Here are some essential things to consider when trying to tune a DB mirroring setup. They revolve round the following concepts:

• The effect of Virtual Log Files (VLFs) on recovery and therefore failover times
• The interplay (or lack thereof) between mirroring and some other SQL Server engine features
• Network speed and issues

1. Be on the latest available Service Pack and Cumulative Update for SQL 2005 or 2008. There are many issues related to mirroring which are fixed in these updates. Some of the key ones which I have seen are given below.

• http://support.microsoft.com/kb/947462/ (FIX: Error message when you start a database mirroring session in SQL Server 2005: "Communications to the remote server instance ‘TCP://<ComputerName>:<PortNumber>’ failed before database mirroring was fully started")
• http://support.microsoft.com/kb/979042 (FIX: The principal database is not recovered if the database has a large number of virtual log files in SQL Server 2005 or in SQL Server 2008)
• http://support.microsoft.com/kb/978791 (FIX: Data loss may occur if the database mirroring thread stops responding for a long time during an automatic failover process in SQL Server 2005 or in SQL Server 2008)
• http://support.microsoft.com/kb/982933 (Error message when you shrink data files on principal in a Database Mirroring for two SQL Server 2005 servers
• http://support.microsoft.com/kb/983500 (FIX: The role switch is delayed when a mirroring automatic failover occurs in SQL Server 2005)

• The remedial steps for this set of problems revolves round fixing the Virtual Log File Fragmentation issues, such as what is described by Kimberly Tripp. The relevant blog posts are:

• http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
• http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx
• http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

• You should also be aware of the article in http://support.microsoft.com/kb/937531 (The shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005)
• If you are using MSDTC transactions with a database which is mirrored, please consider fact that it is not officially supported, as the below articles.

• http://support.microsoft.com/kb/926150 (Using database mirroring for cross-database transactions or distributed transactions is not supported in SQL Server)
• http://support.microsoft.com/kb/977350 (FIX: Assertion failures occur in SQL Server 2005 when a Distributed Transaction Coordinator (DTC) transaction is rolled back while that transaction is being commited by the DTC)

• For the networking side, the key things to consider are given below.

• Review the effect of network latency on mirroring throughput. This is described in the white paper at http://technet.microsoft.com/en-us/library/cc917680.aspx
• If you are facing issues with mirroring being disconnected very frequently, especially under load, check if your NIC card device driver needs updating.
• In addition, review and if required, disable, the TCP Chimney feature in Windows 2003 SP2. More details can be found at http://blogs.msdn.com/b/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx

I will keep adding to this post as I receive additional information. In the meantime, I hope you stay informed and therefore hopefully avoid known issues with mirroring.

If you like this post, please take a few seconds to leave a comment and do rate the post!