Authors: Sanjay Mishra, Prem Mehra

Technical Reviewers: Lubor Kollar, Michael Thomassy, Lindsey Allen, Mike Ruthruff, Kaloian Manassiev, Kevin Cox, Thomas Kejser, Mike Weiner, Mark Souza, Joseph Sack, Glenn Berry (SQL Server MVP)

Overview

A frequently asked question regarding database mirroring is: How many databases can be mirrored in a single instance of Microsoft® SQL Server®? This question is often raised by customers who are consolidating many databases into fewer instances and the high availability or disaster recovery service-level agreement (SLA) requires deployment of database mirroring.

Sometimes the question is raised when readers misconstrue the restriction documented in the SQL Server Books Online stating that “On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session.”

It is important to mention two significant points:

• The restriction of 10 databases doesn’t apply to 64-bit systems. In SQL Server Books Online, there are no such documented restrictions on the number of databases in a 64-bit system.
• Various customers have successfully deployed database mirroring with more than 10 databases in a 64-bit environment.

This article explains the number of worker threads required for database mirroring for each database and illustrates the observed performance of an application with many databases. System administrators and database administrators may find this information useful as they examine, test, and deploy systems in the production environment.

Threads Used by Database Mirroring in SQL Server 2008

The number of threads used on a server for database mirroring depends upon:

• The role of the server – principal or mirror
• The number of databases mirrored in an instance
• The number of logical processors on the mirror server

The following table summarizes the number of database mirroring threads used.

In summary, the number of database mirroring threads:

• On a principal is equal to (2 * number of mirrored databases) + 1.
• On a mirror equals ((3 + FLOOR ((number of logical processors +3) / 4)) * number of mirrored databases) + 1.

For example: If your principal and mirror servers have 8 logical processors each, and you are mirroring 20 databases, the total number of database mirroring threads used on the principal will be 41, and the total number of database mirroring threads used on the mirror will be 101.

To view the threads used for database mirroring, query the DMV sys.dm_exec_requests.

SELECT SESSION_ID, STATUS, COMMAND, WAIT_TYPE

FROM SYS.DM_EXEC_REQUESTS WHERE COMMAND = 'DB MIRROR'

Here is sample output from this query on the mirror server with 8 logical processors, and with one mirrored database.

Test Results

To illustrate database mirroring in a consolidated environment, we ran tests with a workload that continuously inserted data into a number of databases. The databases were deployed with synchronous mirroring between two identical servers (for more information, see “Test Hardware and Software” in the Appendix). The application opened 20 connections to each database, and each connection continuously inserted 6,000 rows (one by one) into the database. The insertion rate of the workload scales linearly as we added more databases.

The focus of our tests was to demonstrate the ability to mirror multiple databases in a consolidated database environment. As an aside, we also observed linear scalability of the workload throughput. For more information about database mirroring performance topics, see Database Mirroring Best Practices and Performance Considerations.

The line “Number of Threads (Mirror)” shows the total number of threads used on the mirror server. This includes the number of threads used by database mirroring represented by the line “Number of DBM Threads (Mirror).”

Additional Considerations

There are two additional considerations – one on application throughput, and another on ping traffic.

Impact of Threads on Throughput

As an aside, note that the overall system performance may be impacted by the number of threads available for processing the workload:

• The database mirroring sessions will consume some number of threads, as discussed above. 
• The remaining threads are available to service user requests. 
• If max_worker_threads is not set high enough, user requests might wait on worker threads even though there are adequate CPU and other resources to service those requests.
• Monitor the wait statistics wait for the worker counter to find out whether you are running low on worker threads, and set max_worker_threads appropriately.

Ping Traffic

For the database mirroring session for each database, the servers send and receive a ping from each other. When several databases are mirrored, the servers will send each other many ping messages within a short interval. To keep the database mirroring connection open, a server must receive and send a ping on that connection within the timeout period.

If the database mirroring partner timeout interval has been set too low, false failovers may occur (if a witness is enabled), or the database mirroring session may switch between disconnected and synchronizing states (if there is no witness).

We recommend that you set the timeout interval to a value of 10 seconds (default) or higher, and if you see unexpected timeouts, increase it to a higher value.

Recommendations

• When you set max worker threads (sp_configure option), make sure to accommodate threads needed by database mirroring in addition to the threads needed by your workload.
• When you set max server memory, make sure to account for the memory consumed by the threads, which is allocated from outside of the buffer pool memory. For more information, see 64-bit Solutions for SQL Server.
• Set these options to the same value on both partners (principal as well as mirror), so that the experience is consistent after a role change.
• Ensure that application performance is acceptable with an increased number of databases and the associated workload on the consolidated server.

Conclusion

With proper planning, it is possible to mirror hundreds of databases in an instance. Use the information provided in this article for planning, and perform thorough testing with your workload to ensure successful deployment.

Appendix A: Test Hardware and Software

All the tests were performed in the following hardware and software environment.

Server

Two Dell PE6950 servers (used as principal and mirror), each with:

• 4-socket, dual-core AMD Opteron™ processor 8220 @2.80 GHz
• 32 GB RAM

One Dell R805 server (used as client), with:

• 2-socket, quad-core AMD Opteron™ processor 2354 @2.20 GHz
• 32 GB RAM

Storage

One 3PAR SAN, with:

• 240 disks, each 147 GB, 10K RPM
• 12 ports, 2 directly attached per server
• All LUNs are striped across all disks
• 16 GB data cache, 4 GB control cache; data cache is dynamically allocated based on I/O patterns

Software

• The 64-bit edition of Windows Server® 2008 Enterprise with Service Pack 1
• The 64-bit edition of SQL Server 2008 Enterprise with Service Pack 1

Further Reading

• Prerequisites, Restrictions, and Recommendations for Database Mirroring
• Database Mirroring Sessions
• Things to consider when setting up database mirroring in SQL Server
• 64-bit Solutions for SQL Server