Database mirroring is a feature in SQL Server that provides high availability and data protection by maintaining a redundant copy of a database on a separate server. In SQL Server 2008, there were several improvements made to the database mirroring feature, including compression of data sent across the network and “Write-ahead on the incoming log stream” on the mirror server.
To understand the impact of these changes, I conducted some simple testing using SQL Server 2005 and SQL Server 2008 Developer Editions. I set up a mirroring configuration with a high-safety mode and no automatic failover. The test database was a copy of a production database, and I used scripts to simulate a load by inserting and updating records at a steady pace.
During the testing, I observed that the scripts I had developed for configuring database mirroring in SQL Server 2005 worked without modification in SQL Server 2008. This means that the process of setting up and administering database mirroring remained the same in both versions, with all the changes happening under the hood.
I tested mirroring from a SQL Server 2005 principal to a SQL Server 2005 mirror, from a SQL Server 2005 principal to a SQL Server 2008 mirror, and from a SQL Server 2008 principal to a SQL Server 2008 mirror. I collected performance data using perfmon counters every 15 seconds for a duration of 7 minutes.
One interesting finding was that SQL Server allowed a SQL Server 2005 principal to mirror to a SQL Server 2008 server. However, it did not allow taking snapshots because the 2005 database is not upgraded to the 2008 file structure until the recovery process in a database restore runs. This means that the mirrored database cannot be put back on a SQL Server 2005 server.
In terms of performance, I observed a slight increase in data writes to disk when using SQL Server 2008, which can be attributed to the simultaneous writing and processing of log records. However, this increase is likely insignificant unless the mirror server’s disks are already stressed. CPU usage on the principal server was significantly higher for a few minutes when mirroring from a SQL Server 2008 principal to a SQL Server 2008 mirror, but it settled down to be roughly the same as mirroring in SQL Server 2005 for the remainder of the test.
The perfmon counters for database mirroring network traffic indicated that SQL Server 2008 mirroring resulted in less network traffic compared to SQL Server 2005 mirroring. The total bytes sent per second decreased by about 32%, log bytes sent per second decreased by about 15%, and total sends per second decreased by about 34%. Mirroring from a SQL Server 2005 principal to a SQL Server 2008 mirror showed little to no difference compared to mirroring between SQL Server 2005 instances.
Based on my test setup, I observed a potentially significant decrease in the amount of network data sent when using SQL Server 2008 mirroring. However, apart from the disk performance, mirroring from SQL Server 2005 to SQL Server 2008 showed little to no performance difference compared to mirroring between SQL Server 2005 instances. If your network capability is currently a bottleneck in your mirroring environment, SQL Server 2008 mirroring may provide some improvement.
In conclusion, while the improvements in SQL Server 2008 database mirroring may not justify an upgrade on their own, they are a nice bonus if you are already upgrading for other reasons. It is worth noting that the mirroring setup process remains the same between SQL Server 2005 and SQL Server 2008, allowing your existing mirroring scripts to continue working seamlessly.
Disclaimer: These results are based on my own testing and may vary depending on various factors such as database characteristics, network characteristics, server characteristics, and other unforeseen factors. It is always recommended to conduct your own testing and evaluation before making important upgrade decisions.