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.

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.

  • 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