Database Mirroring (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Meeting the high availability (HA) and disaster recovery (DR) requirements of mission-critical applications is complex and requires substantial planning. Database mirroring is a popular solution that can form part of a solution that is designed to meet these requirements. Introduced in Microsoft SQL Server 2005 and continually improved over releases, database mirroring provides flexibility in architecting solutions. It can be used both for local HA within a data center and for disaster recovery across data centers. Database mirroring works at a database level and it can be combined with failover clustering and log shipping to provide an extended HA/DR solution.

Best Practices

The following resources provide useful pointers for architecting and implementing database mirroring based solutions.

Architectural Overview

  • The article Database Mirroring Overview1 provides an introduction to database mirroring and of the benefits that database mirroring can provide. The article also explains the core terminology and provides links to a variety of resources related to database mirroring.

    Database failover occurs at the user database level, not at the server level. Note that the system databases master, tempdb, and model cannot be mirrored and that only one secondary database is supported with database mirroring in SQL Server 2008. Databases have dependencies on objects that reside in other databases such as the master database. These objects often include logins, jobs, certificates, and server audits but can include other objects. A process (normally involving scripts) needs to be adopted to keep objects that are external to the database synchronized across the servers. This synchronization process usually needs to be a two-way process unless it can be assured that objects will only be created one server.

Configuration and Monitoring

Performance Considerations

Interaction with Applications

Interaction with SQL Server Features

Case Studies and References

SQL Server database mirroring has successfully been deployed to achieve 99.99% and 99.999% availability by several customers. Examples are outlined in the following:

Questions and Considerations

This section provides questions and issues to consider when working with your customer.

  • Understand how HA/DR requirements are prioritized for the application. In particular, consider the following questions:

    • What is the recovery point objective (RPO)? A zero data loss solution implies deploying synchronous database mirroring.

    • What is the recovery time objective (RTO)? A low RTO value requires much more preparation because most of the recovery procedures have to be scripted and rigorously tested. Examples of required scripts include coordinating the failover of all the databases, making them consistent from the application perspective, and if necessary, redirecting traffic to the new application servers.

  • Does the application use multiple databases and require that all of them be co-resident on a server? Distributed transactions are not supported regardless of their co-residency requirement. You must ensure that all databases are consistent from an application perspective prior to making them available to an application. This impacts the RTO and requires more planning and testing.

  • In our experience, database mirroring is easiest to configure if all servers are running the same operating system, as different operating systems may not all support the same set of encryption algorithms. When a mixture of operating systems needs to be supported, consider manual configuration of the end-points as described in the configuration section of this document.

  • What is the log generation rate? This has an influence on the latency of the log transmission, and for synchronous mirroring, might impact the performance of the primary database.

  • Consider the network bandwidth and network latency between servers? Database mirroring performance can be impacted by network caused latencies.

  • Is automatic failover needed? Automatic failover is supported only in database mirroring sessions running with a witness in high-safety mode.

  • Are multiple secondary mirror databases needed? In SQL Server 2008 or SQL Server 2008 R2, consider log shipping along with database mirroring. The recently announced upcoming version of SQL Server, code-named "Denali," addresses the requirement for multiple mirror databases (prior releases support only a single mirror database).

Appendix

Following are the full URLs for the hyperlinked text.

1 Database Mirroring Overviewhttps://msdn.microsoft.com/en-us/library/ms189852.aspx

2 Things to consider when setting up database mirroring in SQL Serverhttp://support.microsoft.com/kb/2001270

3 How to: Configure a Database Mirroring Session (SQL Server Management Studio)msdn.microsoft.com/en-us/library/ms188712.aspx

4 Database Mirroring Endpointhttps://msdn.microsoft.com/en-us/library/ms179511.aspx

5 Database Mirroring Transport Securityhttps://msdn.microsoft.com/en-us/library/ms186360.aspx

6 Monitoring Mirroring Statushttps://msdn.microsoft.com/en-us/library/ms365781.aspx

7 Database Mirroring Best Practices and Performance Considerationshttp://sqlcat.com/whitepapers/archive/2007/11/19/database-mirroring-best-practices-and-performance-considerations.aspx

8 Mirroring a Large Number of Databases in a Single SQL Server Instancehttp://sqlcat.com/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-server-instance.aspx

9 Database Mirroring Log Compression in SQL Server 2008 Improves Throughputhttp://sqlcat.com/technicalnotes/archive/2007/09/17/database-mirroring-log-compression-in-sql-server-2008-improves-throughput.aspx

10 Asynchronous Database Mirroring with Log Compression in SQL Server 2008http://sqlcat.com/technicalnotes/archive/2007/12/17/asynchronous-database-mirroring-with-log-compression-in-sql-server-2008.aspx

11 Implementing Application Failover with Database Mirroringhttp://sqlcat.com/whitepapers/archive/2007/12/16/implementing-application-failover-with-database-mirroring.aspx

12 Using Database Mirroring (JDBC)https://msdn.microsoft.com/en-us/library/aa342332.aspx

13 Automatic Page Repair During a Database Mirroring Sessionhttps://technet.microsoft.com/en-us/library/bb677167.aspx

14 How to Enable RCSI for a Database with Database Mirroringhttp://sqlcat.com/msdnmirror/archive/2010/03/16/how-to-enable-rcsi-for-a-database-with-database-mirroring.aspx

15 Database Mirroring and Log Shipping Working Togetherhttp://sqlcat.com/whitepapers/archive/2008/01/21/database-mirroring-and-log-shipping-working-together.aspx

16 Using database mirroring for cross-database transactions or distributed transactions is not supported in SQL Serverhttps://support.microsoft.com/kb/926150

17 Database Mirroring and Cross-Database Transactionshttps://msdn.microsoft.com/en-us/library/ms366279(v=SQL.105).aspx

18 Replication and Database Mirroringhttps://msdn.microsoft.com/en-us/library/ms151799.aspx

19 FIX: Changes in the publisher database are not replicated to the subscribers in a transactional replication if the publisher database runs exposed in a database mirroring session in SQL Server 2005http://support.microsoft.com/kb/937041

20 Database Mirroring and Failover Clusteringhttps://msdn.microsoft.com/en-us/library/ms191309(v=SQL.105).aspx

21 Database Mirroring and Full-Text Catalogshttps://msdn.microsoft.com/en-us/library/ms191182.aspx

22 Database Mirroring and Database Snapshotshttps://msdn.microsoft.com/en-us/library/ms175511.aspx

23 High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Studyhttp://sqlcat.com/whitepapers/archive/2009/08/04/high-availability-and-disaster-recovery-at-serviceu-a-sql-server-2008-technical-case-study.aspx

24 High Availability and Disaster Recovery for Microsoft’s SAP Data Tier: A SQL Server 2008 Technical Case Studyhttp://sqlcat.com/whitepapers/archive/2010/10/29/high-availability-and-disaster-recovery-for-microsoft-s-sap-data-tier-a-sql-server-2008-technical-case-study.aspx