Database Mirroring (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions
Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions. |
Database mirroring maintains two copies of a single database that must reside on different server instances of Microsoft SQL Server Database Engine. Typically, these server instances reside on computers in different locations. Database mirroring is not widely used for data warehousing (DW) given its overhead. Instead, most clients will resort to SAN-based replication, where copying of the database files is handled on a scheduled basis "behind the scenes," rather than as a managed operation under SQL Server.
Another popular method of mirroring is to split DW loads into two streams and load both databases concurrently. After loads are finished, a manual reconciliation process must be designed to ensure that both databases are synchronized.
Best Practices
The following resources provide useful pointers for architecting and implementing database mirroring based solutions.
For database mirroring performance considerations, failover characteristics, and best practices, see Database Mirroring Best Practices and Performance Considerations1 The article was written for SQL Server 2005; however, most considerations apply to SQL Server2008 and SQL Server 2008 R2 also.
The article Implementing Application Failover with Database Mirroring2 illustrates how to set up connection string to work with database mirroring.
SQL Server 2008 introduced log stream compression with database mirroring, resulting in significant performance gain. For more information, see the following articles:
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 Instance5 for information about memory settings, thread settings, and other considerations.
Techniques in Enabling Read Committed Snapshot Isolation (RCSI) are discussed in How to Enable RCSI for a Database with Database Mirroring.6
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.7
Database mirroring does not support distributed transactions or Microsoft distributed transaction coordinator (MSDTC) transactions. For more information, see the following articles:
The protection is at the user database level. Must create scripts and processes for copying logins, jobs, and so on, from principal to mirror.
Only one secondary. Augment with log shipping if multiple secondaries are needed.
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 10 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.
Bwin uses synchronous database mirroring across data centers, and augments the solution with log shipping: Failure Is Not an Option: Zero Data Loss and High Availability.11
The article High Availability and Disaster Recovery for Microsoft’s SAP Data Tier: A SQL Server 2008 Technical Case Study12 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.
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.
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.
Do you need multiple secondaries in SQL Server 2008? Consider log shipping along with database mirroring.
Appendix
Following are the full URLs for the hyperlinked text.
1 Database Mirroring Best Practices and Performance Considerations http://sqlcat.com/whitepapers/archive/2007/11/19/database-mirroring-best-practices-and-performance-considerations.aspx
2 Implementing Application Failover with Database Mirroring http://sqlcat.com/whitepapers/archive/2007/12/16/implementing-application-failover-with-database-mirroring.aspx
3 Database Mirroring Log Compression in SQL Server 2008 Improves Throughput http://sqlcat.com/technicalnotes/archive/2007/09/17/database-mirroring-log-compression-in-sql-server-2008-improves-throughput.aspx
4 Asynchronous Database Mirroring with Log Compression in SQL Server 2008 http://sqlcat.com/technicalnotes/archive/2007/12/17/asynchronous-database-mirroring-with-log-compression-in-sql-server-2008.aspx
5 Mirroring a Large Number of Databases in a Single SQL Server Instance http://sqlcat.com/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-server-instance.aspx
6 How to Enable RCSI for a Database with Database Mirroring http://sqlcat.com/msdnmirror/archive/2010/03/16/how-to-enable-rcsi-for-a-database-with-database-mirroring.aspx