SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Database Mirroring - Hitting the Limits

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.


The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by Ken Simmons on 22 July 2009

The number of threads really depends on the function and edition of the server. EE can use an extra thread per every 4 CPUs. Check out the section "Threads created for a database mirroring session". msdn.microsoft.com/.../ms189901.aspx

Posted by Anonymous on 22 July 2009

Thank you for submitting this cool story - Trackback from SnipStorm

Posted by Steve Jones on 23 July 2009

Thanks for the link and glad you liked it. I thought this was really interesting as well, and good information to keep in the back of your mind.

Posted by Anonymous on 31 July 2009

Pingback from  Weekly Links Recap for July 31 | Brent Ozar - SQL Server DBA

Posted by pikes on 31 July 2009

I'm planning on testing mirroring in our environment in the next few months, and we have over 10 databases. We are going to be running it on x64. I'll let you know how it works out!

Posted by Anonymous on 5 March 2010

Cheap soma watson. Cheap soma. Cheap soma indice.

Leave a Comment

Please register or log in to leave a comment.