Kudos for SQL Server 2005 Database Mirroring

If you are considering using database mirroring in SQL Server 2005 instead of or in addition to fail-over clustering, I strongly recommend that you follow Microsoft’s advice and make sure that you have identical drive letters and paths to the data files on both sides of the mirror (Principal and Mirror).

Otherwise, if you decide to create a new data file on the Principal database on a drive letter that does not exist on the mirror, the Mirror database will be unable to create the new file on it’s side.  This will cause the mirroring to go into Suspended status, which will cause the transaction log to queue up on the Principal. Then your transaction log will start to grow and grow, and grow, even though you are doing log backups with truncate.

Of course if this hypothetical situation were to happen to you, the solution is to make sure you create an identical path on the mirror for the new data file, then Resume mirroring. Then you will probably have to wait a little while for the queued transaction log to be sent to the Mirror database and then be replayed there to get back to a Synchronized state.

Even after being abused like this, SQL Server 2005 SP1 database mirroring will just keep on ticking, working its tail off to get the Mirror caught up. Good job Microsoft!

Technorati Tag: SQL Server

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s