This is a question I was asked multiple times over the last week: the Microsoft guidelines for database mirroring say not to mirror more than 10 databases per instance – why is that and is it true?
The answer is my favorite 'it depends!'. The number 10 is a rough guess at the sweet-spot for the majority of customers based on hitting a thread limit on 32-bit machines. The factors that need to be considered are:
- How much memory do the principal and mirror instances have? (hopefully the same)
- How much processing-power do the principal and mirror instances have? (hopefully the same)
- How much bandwidth does the IO subsystem have on the mirror instance? (hopefully the same as on the principal)
- How much transaction log does the workload on each database generate?
- How much network bandwidth is available between the principal and the mirror instances?
The last two factors are the most critical. If the network bandwidth available between the two instances is not enough to handle the combined transaction log generation rate per second from all databases being mirrored then performance will drop on the principal databases. SQL Server 2008 does alleviate some of this with log stream compression – see here for details. The next most critical thing to consider is the memory and thread requirements for mirroring – each mirrored database takes one thread plus some memory, so on low-powered servers, lots of mirrored databases may be too much load on the server when combined with the regular workload.
Here are some examples that I've seen:
- A customer with 25 databases, all of which have very small amounts of activity, and not all at the same time, has them all mirrored with no problem.
- A customer with only 3 heavily-loaded databases, but without a great network connection, that can barely mirror one of the databases without the lack of network bandwidth causing workload degradation.
The key to success here is to do the log generation calculation and then if it seems that the available network bandwidth will support the number of databases you want to mirror, test it first before relying on it in production.
I guess the bottom-line here is that any broad guidance is only that – your mileage may (and probably will) vary. Always do your own calculations and testing.
[Edit 10/15/2009: Checkout the new KB article I helped write that discusses this in detail: http://support.microsoft.com/kb/2001270]