Overview

One of the areas improved for SQL 2008 is database mirroring. Two of the improvements listed in Books Online that caught my eye are compression of data sent across the network (at least 12.5 % compression ratio according to books online), and "Write-ahead on the incoming log stream" on the mirror server. I decided to do some simple testing to see if these changes made a noticeable difference.

Setup

For testing purposes, I used basic workstation-class development machines. The machine that served as the principal was running XP-Pro, and the machine serving as the mirror was running Server 2003 standard. For both machines I installed SQL 2005 Developer Edition (SP2) and SQL 2008 Developer Edition (RTM). I installed both instances as named instances, and while testing only started the instance that I was using for the test.

For the test database, I used a copy of one of our production databases (about 4GB in size) and configured the mirroring for high-safety with no automatic failover. To simulate a load, I wrote a simple script that inserted records at a steady pace, and a script that randomly updates records at a steady pace. Mirroring sessions were set up as high safety (synchronous) without automatic failover.

I found that the scripts I had developed for configuring database mirroring in SQL 2005 worked for SQL 2008 without modification. In fact from what I can tell by scanning Books Online, there are no obvious changes to configuring and administering database mirroring in 2008. All of the changes are under the hood.

I tested mirroring from a 2005 principal to a 2005 mirror, a 2005 principal to a 2008 mirror, and from a 2008 principal to a 2008 mirror, and ran each test for about 7 min, using perfmon counters to collect data every 15 seconds.

I found it interesting that SQL allowed a 2005 principal to mirror to a 2008 server. While I was playing around I observed that the mirroring seemed to work fine with this setup, but it did not allow you to take snapshots (from what I can tell, this is because the 2005 database isn't actually upgraded to 2008's file structure until the recovery process in a database restore runs). If you manually fail over to the mirror server, it prints out messages saying it is upgrading the database to 2008. I didn't test it, but I assume this means you can't put that database back on a 2005 SQL server. I don't know how much support you will get mirroring like this for long periods of time, but Books Online briefly mentions a rolling upgrade strategy to upgrade mirrored servers from 2005 to 2008 that involves upgrading the mirror server first, so I think all of the behavior I observed is by design. Just something to keep in mind.

Results

The amount of data writes to disk increased slightly (about 10%) when using SQL server 2008. I assume this is a result of the way 2008 is simultaneously writing log records to disk and processing log records. Unless the disks on your mirror server are stressed to begin with, this is probably insignificant.

CPU usage on the principal server when mirroring from a 2008 principal to a 2008 mirror was significantly higher for a few minutes, then settled down to be roughly the same as 2005 mirroring for the remainder of the test. I'm not sure how to account for this. I was expecting a slight increase in CPU usage due to overhead in compressing the log stream, but that is not what is observed. I would expect the CPU usage to vary a little bit based on the size of the data changes made, data types involved, etc.

The counters perfmon provides for database mirroring network traffic indicate that SQL server 2008 mirroring does indeed result in less network traffic. 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 2005 principal to a 2008 mirror server showed little if any difference than 2005 to 2005 mirroring. Again, I would expect this would vary quite a bit based on the type of data you have in your database.

Conclusion

For the test setup I used, I did indeed see a potentially significant decrease in the amount of network data sent when using SQL 2008 mirroring. With the exception of the disk performance, mirroring from SQL 2005 to SQL 2008 shows little if any performance difference than 2005 to 2005 mirroring. If your network capability in your mirroring environment is currently a bottleneck, then SQL 2008 mirroring may help you out a little bit. This feature alone probably doesn't justify an upgrade, but it's a nice bonus if you are upgrading for other reasons also. I also really like the fact that there are no changes to the mirroring setup between the two versions so all of your mirroring scripts will still work. (I did not play with the GUI that SSMS offers for setting up mirroring, so can't comment on whether or not they are different between 2005 and 2008).

Disclaimer

These results are what I happened to achieve today while running tests. Actual results may vary based on database characteristics, network characteristics, server characteristics and the alignment of Saturn's moons. Do your own testing rather than relying on this article to make important upgrade decisions.