Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How many mirrored databases can I have on a server?

 How many mirrrored databases can I have on a server?

10 mirrored databases per server is the recommendation for a 32 bit computer WITH 4 processors or less. 64 bit computers have twice as many threads which means, in theory, that you can have twice as many mirrored databases. Also more processors = more threads = more mirrored databases. I interpolated the recommendations out from the base recommendation to other system profiles as follows: 

 

32-bit 64-bit
Number of CPUs Default # of Threads Mirrored Databases Default # of Threads Mirrored Databases
<= 4 processors 256 10 512 20
8 processors 288 11 576 22
16 processors 352 13 704 27
32 processors 480 18 960 37

Others are correct to point out that this is a recommendation, not a hard limit. What your server can actually handle depends on the architecture as well as the amount of other traffic being handled by the server.

Please note that this is merely an interpolation of the "official" recommendation originally recommended for database mirroring limits in SQL Server 2005 Books Online. This is not a statement of how many databases you should expect to be able to mirror on any given server. The actual number of databases you can mirror should be much higher than this and relies on many different factors.

For a more complete understanding of how to calculate the number of databases you can mirror on a given server, I recommend reading and investigating the information documented by the SQL Cat team: Mirroring a Large Number of Databases in a Single SQL Server Instance. As always, be sure to test your planned set up with a realistic workload.

Comments

Posted by Anonymous on 2 April 2009

Here are some notes on “SQL Server 2008 Database Mirroring” I took while attending an advanced class

Posted by G&#233; Brander on 12 June 2009

When you state 'processors' do you mean cores or processors? Do I need a 32 processor with quad cores ==> 128 cores or 8 processor with each 4 cores ==> 32 cores?

Posted by Robert Davis on 14 June 2009

I use the term "processors" to mean logical processors. It is the number of processors as SQL sees them. So if you have 8 quad core processors, then SQL sees 32 processors. If you have 32 quad core processors, then SQL sees 128 processors.

Posted by G&#233;; Brander on 15 June 2009

Ok, clear. Maybe adjust your post to Cores. It makes more sense to people. Then you make a clear distinction between processors and cores.

Posted by Robert Davis on 12 May 2010

Made some updates to the above post to clarify that the information presented is an interpolation of the official recommendation documented in SQL Server 2005 Books Online and not representative of a realistic expectation for mirroring limits of a server.

Posted by mark blakey on 1 October 2010

Do you think there is some advantage to enabling hyperthreading to maximise the number of databases that can be mirrored or is this just a false economy?

Leave a Comment

Please register or log in to leave a comment.