When I first heard about database mirroring in 2004, I was excited. Here was a great new feature that would solve some of the issues with log shipping, and have a much lower cost than clustering. It doesn't completely replace those options, and be sure that you research your high availability options and try to match them to your needs before picking a technology. However database mirroring seemed like a great idea to me.

Then SQL Server 2005 was released in Nov 2005.....but no Database Mirroring. Sure you could enable a trace flag and turn it on, but it wasn't supported in RTM. The reason given is that there was more testing needed. Somewhat annoying, and definitely a marketing driven release, but still the technology was there and there were customers were using it in test programs.

Since then, it seems that I've not heard about a lot of issues with database mirroring. Maybe not a lot of people are using it, but it seems like it's been a good technology for keeping a standby database ready in case of a failure. I do regularly see questions about some things, but not a lot of them compared with log shipping or clustering. I think that it's a fairly solid technology, as long as you get it set up correctly in the first place.

However one place I do see questions is with regards to limits. When I went to Tech Ed in 2006, I am sure that I attended a few sessions and people were claiming that 50 databases was the most you'd want to mirror on an instance because of load. After that point, in testing, the instance performance had degraded quite a bit. So when I got a question recently in the forums on the number of databases per instance, I was ready to post the 50 number and was looking for a reference when I ran across this great blog post from Geek Speek on Database MIrroring Limitations and Gotchas.

The limit given there is 10 databases per instance.

That's a long way from 50, and I can only guess that real world testing over the last 4 years has shown that 10 is a better limit, especially on 32 bit machines. Now when I heard the 50 number there were all the usual caveats that it depends on transaction load, hardware, etc, which is all true, but can you not really get above 10 on a 32 bit machine?

Apparently it has something to do with threads. If you go over to the Geek Speek link, you'll see a nice table of the default threads that exist with various CPU levels and platforms (32/64). Since a mirror uses 5 threads per instance, or maybe per database. I haven't seen a good explanation of this in white papers or documentation, but to be fair, I haven't been digging into a problem and there's a lot of information to wade through without a good reference listed from Microsoft. I've listed a few resources at the end for you to look through.

What I'd like to see from Microsoft is not a number of databases, but rather a number of transactions (or transaction byte count) along with bandwidth requirements to allow you to better size your systems. A database isn't a good generic item, and while there may be overhead, I'd bet the size of the transaction along with the number of them has more to do with mirroring limitations than anything else.

Resources

• Database Mirroring Best Practices and Performance Considerations - http://technet.microsoft.com/en-us/library/cc917681.aspx#EDAA
• Database Mirroring in SQL Server 2005 - http://technet.microsoft.com/en-us/library/cc917680.aspx