Re: replication

Roy Harvey (SQL Server MVP) wrote:
The best place to ask this is microsoft.public.sqlserver.replication.

I am no replication expert, but your requirement sounds like it needs
Database Mirroring. I am guessing that a T1 would be a bit slow for
synchronous mode, also known as high-safety mode. "Under high-safety
mode, when a session begins, the mirror server synchronizes the mirror
database with the principal database as quickly as possible. Once the
databases are synchronized a committed transaction is committed on
both partners, at the cost of increased transaction latency."

That would leave asynchronous operation mode, also known as
high-performance mode. "The mirror server attempts to keep up with
the log records sent by the principal server. The mirror database
might lag somewhat behind the principal database, though, typically,
the gap between the databases is small. However, the gap can become
substantial if the principal server is under a heavy work load or the
system of the mirror server is over loaded."

"In high-performance mode, as soon as the principal server sends a log
record to the mirror server, the principal server sends a confirmation
to the client, without waiting for an acknowledgement from the mirror
server. This means that transactions commit without waiting for the
mirror server to write the log to disk. Such asynchronous operation
permits the principal server to run with minimum transaction latency,
at the risk of some potential data loss."

The quotes are straight from BOL.

Roy,

Thanks for the provided information. Is there any information on how much bandwidth is required to perform the synchronous mode successfully (I was considering purchasing second T1 link for redundancy anyway)? Would this be sufficient? What happens in both synchronous and asynchronous cases, when you loose the connectivity between the two databases lets say for 10 minutes?

Could you please provide me with some links on MSSQL 2005 mirroring options/requirements/best practices?

Thanks,
AL
.

Relevant Pages

  • Re: replication
    ... mode, when a session begins, the mirror server synchronizes the mirror ... database with the principal database as quickly as possible. ... substantial if the principal server is under a heavy work load or the ...
    (comp.databases.ms-sqlserver)
  • Re: Licensing for DB mirroring
    ... Actually you do not need a license for the database server running the ... database snapshot on the mirror database you will need a license. ...
    (microsoft.public.sqlserver.setup)
  • Re: Sql serve clustering
    ... You can't not take a single database and mirror it multiple times. ... >> We are planning to have sql server failover clstering. ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL Server 2005, Database Mirroring and SQL Server Agent
    ... I want the job to run every night to update a table in the mirrored database. ... Now if I just create the job on the principle server and that server goes ... the watch server will fail over to the mirror server making that the ...
    (microsoft.public.sqlserver.server)
  • SQL 2005 Mirror Best Practice
    ... I have mirrored a database using SQL 2005. ... to tackle a complete failure of the principal server (i.e. server ... Promote the Mirror to Principle using: ... The mirror should now act as the principle. ...
    (microsoft.public.sqlserver.replication)