Azure VM SQL Disaster Recovery

1.1. Ensure Network Connectivity

Verify that your primary and secondary Azure VMs can communicate with each other over the network. This typically involves ensuring they are in the same or peered virtual networks and that firewall rules allow SQL Server traffic (default port 1433) and ASR replication traffic.

1.2. Configure Windows Failover Clustering

For Always On Availability Groups, a Windows Server Failover Cluster (WSFC) is required. Follow these steps:

1. Install the Failover Clustering feature on both your primary and secondary SQL Server VMs.
2. Create a cluster object in Active Directory.
3. Configure the cluster using PowerShell or Failover Cluster Manager, ensuring a Quorum configuration is set up.

Refer to Microsoft documentation on Windows Server Failover Clustering for detailed guidance.

1.3. Enable Always On Availability Groups

On both SQL Server instances:

1. Open SQL Server Configuration Manager.
2. Right-click on 'SQL Server Services' and select 'Properties'.
3. Go to the 'Always On High Availability' tab.
4. Check the box for 'Enable Always On Availability Groups'.
5. Restart the SQL Server service.

2.1. Create a Recovery Services Vault

In the Azure portal, create a new 'Recovery Services vault'. This vault will manage replication and failover for your VMs.

2.2. Enable Replication for SQL Server VMs

Within your Recovery Services vault:

1. Under 'Getting Started', select 'Site Recovery'.
2. Choose 'Azure virtual machines' for 'Protect, replicate and recover applications'.
3. Click 'Enable replication'.
4. Select the source region (where your primary VM is located) and target region.
5. Choose the virtual machine scale set or individual VMs you want to protect.
6. Configure replication policies, including Recovery Point Objective (RPO) and retention.

2.3. Monitor Replication Health

Once replication is enabled, monitor the health status of your VMs in the ASR dashboard. Ensure the initial replication completes successfully and that ongoing replication is healthy.

3.1. Create the Availability Group

On your primary SQL Server instance:

1. In SQL Server Management Studio (SSMS), expand 'Always On High Availability'.
2. Right-click 'Availability Groups' and select 'New Availability Group Wizard...'.
3. Follow the wizard, providing a name for your AG, selecting databases, and specifying replicas.
4. For replicas, add your secondary SQL Server instance. Configure availability mode (e.g., Synchronous Commit) and failover mode (e.g., Automatic).
5. Configure listener information, which will be used by applications to connect to the AG.

3.2. Configure Availability Group Listener

The listener provides a single point of connection for your applications. Ensure it is configured with a dedicated IP address that can be accessed from both primary and secondary locations.

4.1. Perform a Manual Failover

Before an actual disaster, it's crucial to test your failover process:

1. In SSMS, right-click on your Availability Group and select 'Failover...'.
2. Follow the wizard to initiate a manual failover to the secondary replica.
3. Verify that applications can still connect to the AG via the listener and that data is consistent.

4.2. Test Failover with Azure Site Recovery

ASR allows you to perform test failovers without impacting your production environment:

1. Navigate to your Recovery Services vault and select 'Replicated items'.
2. Choose your SQL Server VM and click 'Test failover'.
3. Select a recovery point and a target virtual network for the test.
4. Once the test failover is complete, ASR will create isolated VMs in the target network. Connect to these test VMs to validate your data and application connectivity.
5. When testing is complete, remember to 'Clean up test failover'.