Database Mirroring FAQ

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.

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.

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. 1. The principal takes the current end of log and sends that to the mirror.
2. 2. After sending the current end of log, the principal determines how much more log to send.
3. 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. 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. 5. The principal then goes back to step 1 and repeats the process.


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.

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.