SQL Server 2005 Database Mirroring

  • Article

Customers often raise the following concerns and questions regarding the Database Mirroring feature of SQL Server 2005. In here, Don Vilen a Program Manager for SQL Server Database Mirroring and I have responded to some of these concerns and questions:

1) performance - synchronous mirroring has well documented performance statistics for instance index rebuild takes longer than before...

Synchronous mirroring guarantees zero data loss; the method it uses is to keep two copies of the database identical in structure, copy the log from one to the other, and replay the log operations on the second copy. So yes, everything that goes into the log must be sent to the mirror. Most log records can be sent to the mirror in a partially asynchronous manner, but when a COMMIT record is put into the log, all pending log records must be sent to the mirror and the COMMIT record must be written on the mirror before we can acknowledge the COMMIT to the client who committed his transaction.

Regarding index rebuild: if rebuilding is necessary on a large table, consider table/index partitioning where you can rebuild indexes on only the partitions that need it, thereby reducing the impact. It is worth mentioning that partitions are only available in the Enterprise Edition.

2) mirroring occurs at the database level – what about queries that require data from multiple databases?

Yes, Database Mirroring occurs at the database level. Some customers mirror multiple databases, but they must then manage the failover manually so that all databases involved failover at the same time. Failover Clustering may be a good solution for this as it works at the instance level rather than the database level.

3) mirroring relies on good network infrastructure and low latency- which is not always provided - low latency can significantly drag down the performance of the principal

Yes, you need good infrastructure (unfortunately we are not able to overcome either the laws of physics or the laws of information theory). An alternative is to use asynchronous mirroring as it handles network delays somewhat ‘better’, though it does not provide a guarantee of zero data loss or automatic failover. For asynchronous operations, you may want to consider SQL Replication.

4) if mirror server is down, logs will grow on principal which could cause disk space issues on the server.

Yes, as with replication we need the information to remain in the log until we can send it over to the mirror. If this takes too long, some intervention will have to be made, perhaps having to stop the mirroring session. We will need the log records to catch the mirror up to the principal.

5) imagine a setup in which the principal server is in one room and the witness and mirror in the other. What happens if the communication between the rooms fails?

For the Principal to remain the Principal it must have 2 of the 3 ‘votes’ in the mirroring session. Otherwise it might be carrying on as the Principal while the Mirror has also become the Principal. In this scenario, work is being done at both sides of the mirror which would be an undesirable situation. Put the witness nearer the preferred principal, so that if the communication fails between principal and mirror, that the witness will still see the preferred principal and therefore ‘vote’ for the principal to remain the principal, rather than have it see the preferred mirror and not see the preferred principal so it would vote for the mirror to become the principal with the mirror now having 2 of the 3 votes so it will become the principal. This is exactly what the witness is for and how a distributed system like this must prevent the ‘split-brain’ problem where both sides of the mirror assume the other side is dead and so they should take over and continue.

6) failover clustering or database mirroring?

In database mirroring, as the name suggests, the protection is offered at the database level, whereas, failover clustering provides protection at the SQL Server instance level. Another major difference is that in the case of database mirroring both the mirror and the principal get their own SQL Server instance with distinct names. However, in the case of clustering all nodes are given one virtual server name and IP address. It is also important to mention that unlike clustering, database mirroring does not require proprietary hardware and does not have a potential failure point with shared storage.

Where to go if you need more information? Ron Talmage has produced a comprehensive whitepaper on Database Mirroring in SQL Server 2005.

Comments

  • Anonymous
    April 28, 2008
    Hi, Pedram. Are there well documented performance statistics (or rules of thumb) regarding increase/decrease in insert/update/delete performance where the synchronous mirroring is used instead of asynchronous? If so, where? Thanks.

  • Anonymous
    April 28, 2008
    Have a look at this: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx Pedram

  • Anonymous
    June 09, 2010
    Split Brain issue usually happen when you are using Distributed transaction with DB mirroing. Cheersjavascript:WebForm_DoPostBackWithOptions(new%20WebForm_PostBackOptions("ctl00$content$ctl00$fragment_3366$ctl01$ctl00$ctl00$ctl05$bpCommentForm$ctl05$btnSubmit",%20"",%20true,%20"BlogPostCommentForm-ctl00_content_ctl00_fragment_3366_ctl01_ctl00",%20"",%20false,%20true)) Praveen Barath