Tech Paper - Best Practices for Disaster Recovery

Version 2

    Deploying the ArcFM Solution on SQL Server White Paper


    Disaster recovery (DR) provides alternate services in the event of a catastrophic physical disruption of the infrastructure at the primary site. For protection from natural disasters such as earthquakes, the DR site is frequently located far from the primary site. SQL Server provides various technologies for transferring data changes from the primary database to a disaster recovery site.

    One of the main decisions that you need to make is whether or not you are willing to tolerate potential data loss in the event of complete destruction of the primary site. Loss-less DR methods require the use of synchronous replication to the DR site. This means that all writes to the storage systems must complete at both sites before the transaction is committed, which can increase the time to perform the transaction significantly, depending on the line speed.

    The two primary methods of synchronous replication are SAN mirroring and database mirroring in "high-safety" mode. SAN mirroring requires additional features provided by the SAN vendor. Database mirroring is a feature included with SQL Server.


    SAN Mirroring

    SAN mirroring synchronizes the contents of one SAN with another and requires very high bandwidth between the two SANs. A benefit of SAN mirroring is that it mirrors all of the storage, not just the database. SAN mirroring is provided by the storage vendor (it is not part of the Windows Server operating system).

    Note: Asynchronous SAN mirroring is not recommended because it is not possible to determine if the mirror is synchronized after a disaster, and you cannot easily discover any differences.


    Asynchronous Replication Methods

    If a small potential for loss of recently committed transactions is acceptable, synchronization performance can be improved by using asynchronous protocols between primary and secondary database servers. Using asynchronous protocols means that there is no dependency between the database servers when committing a transaction. A transaction may be committed on one server, but not on the other until much later.

    The primary methods of asynchronous replication are asynchronous database mirroring, log shipping, or transactional replication (server-to-server transactional replication or peer-to-peer replication). With these methods, there is a window of a few seconds to a few minutes where the transactions committed on the primary site are not yet committed on the DR site.

    Note that when using asynchronous database mirroring or log shipping, the log from the primary site cannot be applied to the DR site once the DR site has been used to store new transactions in its new role as the primary database. Transactions that were not shipped to the DR site before switching to the secondary database server are lost.

    For more information, see Log Shipping Overview, Database Mirroring Overview, and Transactional Replication Overview.