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.
The following resources provide useful pointers for architecting and implementing database mirroring based solutions.
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
The article Things to consider when setting up database mirroring in SQL Server2 can help you prepare to set up database mirroring.
The guide How to: Configure a Database Mirroring Session (SQL Server Management Studio)3 is helpful in configuring database mirroring.
If non-default encryption algorithms need to be configured, it may be necessary to manually configure the database mirroring endpoints rather than using the database mirroring wizard to configure the endpoints. The article Database Mirroring Endpoint4 describes the endpoints used for database mirroring. The article Database Mirroring Transport Security5 describes the available encryption algorithms and provides links to examples of how to configure endpoints manually.
Once configured, database mirroring needs to be monitored on an ongoing basis. The article Monitoring Mirroring Status6 describes the available options for monitoring database mirroring.
For database mirroring performance considerations, failover characteristics, and best practices, see Database Mirroring Best Practices and Performance Considerations.7 The article was written for SQL Server 2005; however, most considerations apply to SQL Server2008 and SQL Server 2008 R2 also.
If you have hundreds of databases in an instance that you want to mirror, see Mirroring a Large Number of Databases in a Single SQL Server Instance8 for information about memory settings, thread settings, and other considerations.
SQL Server 2008 introduced log stream compression with database mirroring, resulting in significant performance gain. For more information, see the following articles:
Interaction with Applications
The article Implementing Application Failover with Database Mirroring11 illustrates how to setup connection string to work with database mirroring. If the application is using JDBC connections to SQL Server, the article Using Database Mirroring (JDBC)12 provides a description of the interaction and useful code samples.
SQL Server 2008 introduced Automated Page Repair for selected I/O errors. For more information, refer to Automatic Page Repair During a Database Mirroring Session.13 It is important to note that automated page repair can help in reducing server downtime, but it does not address the underlying cause of the corruption. Typically, this cause still needs to be investigated and corrected.
Interaction with SQL Server Features
Techniques in Enabling Read Committed Snapshot Isolation (RCSI) are discussed in How to Enable RCSI for a Database with Database Mirroring.14
Log shipping is often used along with database mirroring, either as the primary solution or for additional redundancy. For more information, view Database Mirroring and Log Shipping Working Together.15
Database mirroring does not support distributed transactions or Microsoft distributed transaction coordinator (MSDTC) transactions. For more information, see the following articles:
In SQL Server 2008 R2, database mirroring does not support databases that are using FILESTREAM. A FILESTREAM filegroup cannot be created within a mirrored database, and database mirroring cannot be configured for a database that already contains FILESTREAM filegroups.
The article Replication and Database Mirroring18 describes the interaction between these two features and the associated considerations. Replication stops if database mirroring is paused. This is to prevent the subscriber from getting ahead of the mirror. To allow replication to continue with database mirroring paused in SQL Server 2005, apply the hotfix for SQL Server 2005 Service Pack 2 (SP2) and then use the Trace Flag, as explained in 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 2005.19 The Trace Flag can also be used with SQL Server 2008.
Database Mirroring works between a Failover Cluster and either another Failover Cluster or a non-clustered server as described in the article Database Mirroring and Failover Clustering20
To mirror a database that has a full-text catalog, use backup as usual to create a full database backup of the principal database, and then restore the backup on the mirror server as described in the article Database Mirroring and Full-Text Catalogs21
In SQL Server 2008 R2, the mirrored database cannot be directly accessed, although database snapshots can be created on the mirror for reporting purposes as described in the article Database Mirroring and Database Snapshots22 There are, however, practical constraints in using database snapshots for read-only purposes. In particular, no automated process is available to periodically refresh database snapshots. Also, users need to be disconnected from database snapshots before they can be deleted and recreated.
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:
High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study
23 provides a detailed description of the end-to-end HA and DR solution at ServiceU. ServiceU deploys failover clustering for local HA and database mirroring for DR.
The article High Availability and Disaster Recovery for Microsoft’s SAP Data Tier: A SQL Server 2008 Technical Case Study24 discusses how the Microsoft IT SAP deployment uses synchronous database mirroring within a data center, and log shipping to a remote data center.
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.
- What is the recovery point objective (RPO)? A zero data loss solution implies deploying synchronous database mirroring.
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).
Following are the full URLs for the hyperlinked text.
1 Database Mirroring Overviewhttp://msdn.microsoft.com/en-us/library/ms189852.aspx
3 How to: Configure a Database Mirroring Session (SQL Server Management Studio)msdn.microsoft.com/en-us/library/ms188712.aspx
4 Database Mirroring Endpointhttp://msdn.microsoft.com/en-us/library/ms179511.aspx
5 Database Mirroring Transport Securityhttp://msdn.microsoft.com/en-us/library/ms186360.aspx
6 Monitoring Mirroring Statushttp://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)http://msdn.microsoft.com/en-us/library/aa342332.aspx
13 Automatic Page Repair During a Database Mirroring Sessionhttp://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 Serverhttp://support.microsoft.com/kb/926150
17 Database Mirroring and Cross-Database Transactionshttp://msdn.microsoft.com/en-us/library/ms366279(v=SQL.105).aspx
18 Replication and Database Mirroringhttp://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 Clusteringhttp://msdn.microsoft.com/en-us/library/ms191309(v=SQL.105).aspx
21 Database Mirroring and Full-Text Catalogshttp://msdn.microsoft.com/en-us/library/ms191182.aspx
22 Database Mirroring and Database Snapshotshttp://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