ActiveDR Disaster Recovery Strategies for SQL Server

Storage and database administrators can use ActiveDR to protect SQL Server data between a primary site array and a secondary site array.

Disaster Recovery Strategies for User Databases

A SQL Server instance contains several system databases and user databases. Each instance stores both the system database and user databases in files on a file system. These files include:

Database files (MDF and NDF): Every database has one MDF data file and, optionally, zero or more NDF files, with each file having an .mdf or .ndf extension. MDF files contain all data related to the database, including tables, views, stored procedures, and other database metadata.
Transaction log files (LDF): Every database has one or more LDF transaction log files, which have an .ldf extension. The transaction log contains records of all changes made to a SQL Server database. These changes include updates, inserts, deletes, the start and end of each transaction, and other system transactions.

Note: Only the volumes that contain user database MDF, LDF, and NDF files should be replicated to a remote site with ActiveDR. TempDB files should not be replicated, though the SQL Server instance at the remote site does need to be configured with its own TempDB volume. System databases should be segregated into their own set of volumes apart from user databases. ActiveDR does not support the replication of system databases.

Once the primary site's SQL Server instance volumes are created and populated with user database files, the volumes are then placed in a FlashArray pod.

If a SQL Server instance contains multiple user databases, each set of user database files should be placed on different volumes. Using different volumes for different user database files helps storage and database administrators to:

Manage recovery strategies on a per-database level. For example, a user database might require a shorter recovery time objective that benefits from replication to a remote site using ActiveDR, while another user database might require a less stringent recovery time objective that can use traditional backup and recovery methods or snapshots.
Manage storage at a more granular level. Database administrators can monitor volume usage and expand volumes for different databases as needed. This avoids storage space conflicts where large databases impact the storage space needed for smaller databases.

FIGURE 4 | Volume and pod layout for databases on a single SQL Server instance

Replicating a SQL Server VMware vSphere Virtual Machine

FlashArray provides integration with VMware vSphere that lets storage and database administrators replicate complete virtual machines to a remote location. In this scenario, FlashArray volumes are configured as VMware datastores (vVols are not supported) that are attached to VMware ESXi hosts. These volumes contain files for virtual machines and any attached virtual disks.

FIGURE 5 | A virtualized SQL Server stack that uses Everpure FlashArray.

Database and storage administrators can configure SQL Server instances in virtual machines similar to physical SQL Server instances by creating separate virtual disks on separate FlashArray volumes that contain the virtual machine, SQL Server data files and log files. ActiveDR can then replicate the virtual machine, data file, and log file volumes to a remote site, where the volumes can be attached to a remote VMware ESXi host. This scenario is beyond the scope of this white paper, but more information is available in the ActiveDR with VMware User Guide.

ActiveDR and Always On Availability Groups

SQL Server Always On Availability Groups provide replication at the database layer.

The entirety of an Availability Group (with replicas participating in the Availability Group) can be added to an ActiveDR pod for disaster recovery failover/replication to another set of nodes in a remote location. Replicating a subset of an Availability Group is not supported with ActiveDR. Under certain circumstances in a failover scenario, only the primary replica will be available, and secondary replicas must be reseeded. This reseeding is dependent on the state of data movement occurring between the Availability Group replicas at the time of failover.

ActiveDR and SQL Server Always On Failover Cluster Instances

Microsoft provides SQL Server Always On Failover Cluster Instances that use Windows Server Failover Clustering to provide high availability at the SQL Server instance–level. Database and storage administrators can also use ActiveDR to replicate SQL Server data and log files at the storage level to a secondary site, typically on a different subnet. In this scenario, two or more SQL Server nodes utilize a shared Cluster Shared Volume, where the SQL Server database and log files are stored. The Cluster Shared Volumes are then placed into a FlashArray pod, which is then replicated to a remote FlashArray using ActiveDR. The replicated volumes at the remote site can then be attached to a secondary SQL Server Failover Cluster Instance should a failure at the primary site occur.

For more information about using SQL Server Always On Failover Cluster Instances between primary and secondary sites, see SQL Server Multi-Subnet Clustering (SQL Server).

FIGURE 6 | ActiveDR replication between two SQL Server Failover Cluster Instances.

Volumes that contain database files and log files should be configured in an ActiveDR pod.

Database administrators can configure SQL Server Failover Cluster Instances to automatically failover should a problem occur on one of the SQL Server Failover Cluster Instance nodes, or a failover can be manually initiated. In either an automatic or a manual failover, the node that takes over from the primary failed node continues to use the Cluster Shared Volume on the FlashArray at the primary site.

In a disaster recovery event where the primary site is offline, such as during a natural disaster, administrators can bring the replicated Cluster Shared Volumes online at the remote site. These volumes are already attached to the preconfigured remote SQL Server Failover Cluster Instance, allowing application requests to continue being processed seamlessly.

Once the primary site is brought back online, storage administrators can reverse the replication from the secondary site back to the primary site, and then failback to the primary site at the time of their choosing.