Database Mirroring FAQ

Article
12/18/2008

This frequently asked questions (FAQ) document provides answers to questions commonly asked regarding the database mirroring functionality in SQL Server 2005.

Availability of Database Mirroring

Q. What support is available for database mirroring?

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.)

Clustering

Q. Can I use mirroring and clusters?

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.

Q. How do I have mirroring fail over after the clusters fail over?

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.)

Q. Can the witness be on a cluster?

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.

Q. Can I mirror between multiple virtual servers in a cluster?

A.	Yes, but Microsoft does not recommended mirroring between multiple servers in a cluster.

Networking

Q. How reliable does my network have to be?

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.

Q. What is the slowest network that I can use with mirroring?

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.

Q. How do I specify a specific network adapter/network just for mirroring?

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.

Q. What if I issue a COMMIT but the database fails over before I receive a response? I receive a connection closed error.

For all systems, including stand-alone systems, if a client issues a COMMIT statement, there can be one of three different outcomes:

Acknowledgement—the transaction is guaranteed to be committed.
Rollback—the transaction is guaranteed not to be committed.
Connection Closed—there is no way to tell if the transaction was committed or not. The client must go back and query the database after reconnecting to the server.

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.

General

Q. Why don’t some technologies work seamlessly after a failover?

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:”

Replication
Distributed Transaction Coordinator (DTC)
Change notification
Logins
Jobs
Information stored in other databases, such as the msdb or master database:

TRUSTWORTHY database property
Jobs
Logins
Unique stored procedures in system databases

Using three-part names to access databases on the same server
Multiple databases
Log shipping
Performance

Now with database mirroring, moving a database to another server is much easier.

Q. Does the witness instruct or monitor the partner servers?

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.

Q. How does the mirror catch up after the mirror has been down for some time?

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:

1. The principal takes the current end of log and sends that to the mirror.
2. After sending the current end of log, the principal determines how much more log to send.
3. If the remaining amount of log to be sent is less than 1 megabyte (MB), the status is set to SYNCHRONIZED and all COMMIT statements will wait to hear back from the mirror before returning. The system is caught up.
4. If the remaining amount of log to be sent is more than 1 MB, the principal adds a delay of several milliseconds (ms) to all transactions when those transactions commit.
5. The principal then goes back to step 1 and repeats the process.

Q. Can I achieve a “delayed” mirror?

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.

Q. Where should the witness server go if the partners are in different locations?

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.

Q. What are the different licenses available for mirroring?

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.

Q. What about bulk-logged imports?

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.

You have the following solutions:

Turn off mirroring, change the recovery model to bulk logged, import the data, change the recovery model back to full, do a log backup, copy the backup over and restore the backup, and turn on mirroring again.
Take the performance hit.
Use a different technology (for example, SQL Server 2005 Replication). Any solution that works at the physical level—log shipping, mirroring, stretch clustering—will have to move the physical contents of the files to the other side.

Q. Where are the “send queue” and “redo queue” located?

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.

For More Information

Database Mirroring in SQL Server 2005

Top of page