This frequently asked questions (FAQ) document provides answers to questions commonly asked regarding the database mirroring functionality in SQL Server 2005.
|A.||Microsoft fully supports database mirroring with SQL Server 2005 SP1 onwards. For the RTM release (prior to SP1), Microsoft support services will not support databases or applications that use database mirroring; the database mirroring feature should not be used in production environments. Prior to SP1, database mirroring is disabled by default, but can be enabled for evaluation purposes by using trace flag 1400 as a startup parameter. (For more information about trace flags, see Trace Flags (Transact-SQL) in the SQL Server Books Online.)|
|A.||Yes, you can mirror a database from one virtual server to another virtual server in a different cluster. You can even mirror from one virtual server to another virtual server in the same cluster. However, Microsoft does not recommend mirroring to another virtual server in the same cluster.|
When you use both mirroring and clustering, understand that mirroring and clustering work independently of each other. Mirroring knows nothing about clusters, and clusters know nothing about mirroring. If a node fails, mirroring will probably fail over before the clusters do. Therefore, when the other node in the cluster comes online, this node will be in the mirror role.
If you are currently running well with clusters in production, using mirroring in addition to clusters should be fine.
|A.||In the Microsoft SQL Server 2005, you cannot guarantee that mirroring will fail over after a cluster failover. (Cluster failover is where the cluster fails over first. When the last node in the cluster fails, database mirroring fails over.)|
|A.||Yes, the witness can be on a cluster. However, having the witness on a cluster is not required. Your experience with clustering should determine whether to use clustering.|
Note that the witness is not the most important member of the database mirroring session. The witness just answers the question: “Who do you see?” When the partner servers cannot see each other, the partner servers contact the witness to see whether the witness can contact the other partner and verify that a failure has occurred.
|A.||Yes, but Microsoft does not recommended mirroring between multiple servers in a cluster.|
|A.||When you use database mirroring in high-availability mode, the process of determining a failover is based on the network connection. If there is a problem with the network, mirroring will fail over or deny access to the database because of the quorum requirement. Although mirroring has acted correctly, not having access may surprise customers, because with an ordinary stand-alone computer, the database would still be served. To understand how the system works without the option of automatically failing over, try using database mirroring without a witness for a time.|
|A.||There are no specific restrictions on the network for mirroring, but the network connection between the servers is critical. The network should generally be dedicated, and be of high quality and high bandwidth. As a rough guideline, the network bandwidth should be three times the maximum log generation rate.|
|A.||Use multiple network adapters in the server. For the network adapter that you want to dedicate to mirroring, associate a specific IP address to that adapter. Then, associate a particular name to that IP address. Use that particular name when you set up database mirroring.|
For all systems, including stand-alone systems, if a client issues a COMMIT statement, there can be one of three different outcomes:
For database mirroring, the only other implication is that the database is brought online quickly on another server. If the commit log record is not on the mirror server before failover occurs, the client was never informed that a COMMIT statement had occurred, and so whether the log record hit the disk on the original principal is not important.
In the past, backup and restore to another server or failover for log shipping was generally a difficult process. Some customers could not correctly move a database to another server, or the move would take a long time to do. Therefore, moving a database to another server was rarely performed. When moving a database to another server, the following features did not work “smoothly:”
Now with database mirroring, moving a database to another server is much easier.
|A.||No, the witness just answers the question: “Who do you see?”|
The witness is not a single point of failure.
If one partner server thinks that there has been a problem with the other partner, the partner servers contact the witness and ask the witness what it sees.
If the safety is set to FULL, but the mirror is not SYNCHRONIZED, transactions do not wait on the COMMIT statement. The mirror is either SUSPENDED, down, or catching up (SYNCHRONIZING). When the mirroring is SYNCHRONIZING, mirroring is chasing a moving target because the principal is generating more log records. To catch up, the following actions occur:
Every time that the principal enters step 4, the delay is increased until the delay reaches a maximum of 100 ms. As the delay added in step 4 increases, the principal is throttled more and more until the mirror catches up in step 2.
|A.||No. However, if you spent some time working with an external tool that sent SUSPEND and RESUME statements to the mirroring session at the right times, a delayed mirror might be possible. The recommendation for a delayed secondary is to use log shipping.|
|A.||The location of the witness (indeed, the location and configuration of all the servers) depends on what problems are anticipated and what errors must be reduced. Ideally, all three servers should be in well-maintained locations with high-quality services and personnel.|
If there two and only two data centers, where should the witness go? With the mirror or principal? The answer depends on what the weakest link in your infrastructure. If the power is likely to fail in the principal site, the witness should probably accompany the mirror to avoid losing two different servers, and therefore quorum, if the power system fails. Generally, the witness would accompany the principal.
|A.||The witness can be any edition of Microsoft SQL Server 2005.|
If a partner uses the SQL Server 2005 Standard Edition, the safety must be FULL, and REDO is single-threaded.
In SQL Server 2005 Enterprise Edition, the feature is fully implemented, and lets you turn the safety OFF and have REDO multithreaded.
The database cannot be changed to use the bulk-logged or simple recovery model. You can import the data as before; however, the data will be fully logged and sent to the mirror.
|A.||The send queue is the part of the transaction log that has been hardened on the principal, but not sent to the mirror. The redo queue is the part of the transaction log that has been hardened on the mirror, but not redone.|