Conference Questions Pot-Pourri #4: How many databases can you really mirror per instance?

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]

6 thoughts on “Conference Questions Pot-Pourri #4: How many databases can you really mirror per instance?

  1. It depends, I always say whenever I was asked this question every time on the Database Mirroring.
    Personally I have managed upto 8 databases that are managed as 9 to 5 type of applications on a sensible hardware with 16gb RAM.

    By experience I can confirm if you have more than 5 databases to mirror then do not go for automatic failover!

  2. Hi,

    I’m currently trying to mirror roughly 180 databases in one instance with 32GB of ram

    I’m getting timeout issues :( is this expected?

  3. Hi Paul

    When I ran your Query the output shows PageIOlatch_SH and CX_packet are top wait resources but we have 30 databases mirrored where we have network latency for example for copying 1 gb file the ETA would be 8 minutes.

    Above Query does not take any Mirror wait types but when i used select * from sys.dm_exec_requests the majority wait types are “DBMIRROR_EVENTS_QUEUE”DBMIRRORING_CMD.

    And also it shows the same with below Query as well .I thought of sharing for mixed results.

    WITH Waits AS
    (
    SELECT
    wait_type,
    wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
    FROM sys.dm_os_wait_stats
    WHERE wait_type
    NOT IN
    (‘CLR_SEMAPHORE’, ‘LAZYWRITER_SLEEP’, ‘RESOURCE_QUEUE’,
    ‘SLEEP_TASK’, ‘SLEEP_SYSTEMTASK’, ‘SQLTRACE_BUFFER_FLUSH’, ‘WAITFOR’,
    ‘CLR_AUTO_EVENT’, ‘CLR_MANUAL_EVENT’)
    ) — filter out additional irrelevant waits

    SELECT W1.wait_type,
    CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
    CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
    CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
    FROM Waits AS W1
    INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
    GROUP BY W1.rn,
    W1.wait_type,
    W1.wait_time_s,
    W1.pct
    HAVING SUM(W2.pct) – W1.pct < 95; — percentage threshold;

    RP

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.