Based on the requirements for Recovery Time Objective (RTO) and Recovery Point Objective (RPO), as outlined in the following table, you should select a BCDR technology to recover SQL Server instances. To coordinate the recovery of your complete application, combine Site Recovery with the failover functionality of your selected technology.
| Deployment type | BCDR technology | Expected RTO for SQL Server | Expected RPO for SQL Server |
|---|---|---|---|
| SQL Server on an Azure infrastructure as a service (IaaS) virtual machine (VM) or at on-premises. | Always On availability group | The time taken to make the secondary replica as primary. | Because replication to the secondary replica is asynchronous, there’s some data loss. |
| SQL Server on an Azure IaaS VM or at on-premises. | Failover clustering (Always On FCI) | The time taken to fail over between the nodes. | Because Always On FCI uses shared storage, the same view of the storage instance is available on failover. |
| SQL Server on an Azure IaaS VM or at on-premises. | Database mirroring (high-performance mode) | The time taken to force the service, which uses the mirror server as a warm standby server. | Replication is asynchronous. The mirror database might lag somewhat behind the principal database. The lag is typically small. But it can become large if the principal or mirror server’s system is under a heavy load. Log shipping can be a supplement to database mirroring. It’s a favorable alternative to asynchronous database mirroring. |
| SQL as platform as a service (PaaS) on Azure. This deployment type includes single databases and elastic pools. |
Active geo-replication | 30 seconds after failover is triggered. When failover is activated for one of the secondary databases, all other secondaries are automatically linked to the new primary. |
RPO of five seconds. Active geo-replication uses the Always On technology of SQL Server. It asynchronously replicates committed transactions on the primary database to a secondary database by using snapshot isolation. The secondary data is guaranteed to never have partial transactions. |
| SQL as PaaS configured with active geo-replication on Azure. This deployment type includes a managed instances, elastic pools, and single databases. |
Auto-failover groups | RTO of one hour. | RPO of five seconds. Auto-failover groups provide the group semantics on top of active geo-replication. But the same asynchronous replication mechanism is used. |
| SQL Server on an Azure IaaS VM or at on-premises. | Replication with Azure Site Recovery | RTO is typically less than 15 minutes. To learn more, read the RTO SLA provided by Site Recovery. | One hour for application consistency and five minutes for crash consistency. If you are looking for lower RPO, use other BCDR technologies. |
In today’s digital landscape, ensuring business continuity and minimizing downtime are critical components of a robust IT strategy. One essential tool in achieving this is Azure Site Recovery (ASR), which provides disaster recovery capabilities for Azure Virtual Machines (VMs). This blog post will guide you through enabling ASR for a SQL Server running on an Azure VM using the VM’s Disaster Recovery blade.
Prerequisites
Before we start, ensure you have the following:
- An active Azure subscription.
- A running Azure VM with SQL Server installed.
- The VM must be in a supported region for ASR.
Step-by-Step Guide
Step 1: Access the Azure Portal
- Sign in to the Azure portal (https://portal.azure.com/).
- Navigate to the Virtual Machines service.
Step 2: Select the Target VM
- In the Virtual Machines blade, select the VM that runs your SQL Server.
- In the VM’s pane, click on Disaster recovery under the Operations section.
Step 3: Configure ASR
- In the Disaster recovery blade, you’ll be prompted to configure ASR settings for the VM.
Step 4: Configure Target Settings
- Target region: Select the region where you want to replicate your VM.
- Resource group: Select or create a resource group in the target region.
- Virtual Network: Select or create a virtual network in the target region.
Step 5: Configure Replication Settings
- Replication policy: Choose an existing replication policy or create a new one. This policy defines the replication frequency and recovery point retention.
- Cache storage account: Select a storage account to be used as a cache for the replication data.
- Replication storage account: Choose a storage account in the target region where the replicated data will be stored.
Step 6: Enable Replication
- Review your configuration settings and click Start replication.
- Azure will start the initial replication process. This may take some time depending on the size of your VM and the network bandwidth.
Step 7: Monitoring and Testing
- Once replication is enabled, monitor the replication status from the Disaster recovery blade.
- You can perform a test failover to ensure that your SQL Server VM can be successfully failed over to the target region. This is a crucial step to validate your disaster recovery plan.
Step 8: Perform a Test Failover
- In the Disaster recovery blade, click on Test failover.
- Choose a recovery point and target network for the failover.
- Initiate the test failover and ensure the SQL Server VM comes up correctly in the target region.
Now you wondering, why my SQL Instance of Azure VM not replicated to the target region. It will not replicate to the target region. We have to install the related extension in order to create it in the target region. Follow the Step 9 to correctly install it in your DR site SQL VM.
Step 9: Install SQL IaaS Agent extension for target region SQL Server
- Open up the Cloud Shell.
- Run the following Azure PowerShell script in the Cloud Shell Window
# Get the existing Compute VM
$vm = Get-AzVM -Name
New-AzSqlVM -Name $vm.Name -ResourceGroupName $vm.ResourceGroupName -Location $vm.Location -LicenseType
Conclusion
Enabling Azure Site Recovery for your SQL Server running on an Azure VM provides a robust disaster recovery solution, ensuring business continuity in case of regional outages or disasters. By following these steps, you can set up ASR using the VM’s Disaster Recovery blade in the Azure portal, making the process seamless and straightforward.
Regularly testing your disaster recovery plan is crucial to ensure that your SQL Server can be recovered quickly and efficiently when needed. By integrating ASR into your disaster recovery strategy, you are taking a significant step towards safeguarding your critical data and applications.
