Building a multi-zone and multi-region SQL Server Failover Cluster Instance in Azure

1. Initialize the extra 20GB disk on SQLNode1.
2. Format it as the F: drive.
3. Assign the same drive letter (F:) on SQLNode2 and SQLNode3 to maintain consistency.

8. Create the DataKeeper job to replicate the F: drive

Now that the F: drive is configured, we create a DataKeeper replication job to synchronize data between the nodes:

1. Synchronous replication between SQLNode1 and SQLNode2 (for low-latency, intra-region failover).
2. Asynchronous replication between SQLNode1 and SQLNode3 (for cross-region disaster recovery).

Steps:

1. Launch DataKeeper and create a new replication job.
2. Configure synchronous replication for the F: drive between SQLNode1 and SQLNode2.
3. Configure asynchronous replication between SQLNode1 and SQLNode3.

The screenshots below walk through the process of creating the DataKeeper job that replicates the F: drive between the three servers.

To add the second target, right-click on the existing Job and choose “Create a Mirror.”

Once replication is active, SQLNode2 and SQLNode3 will have an identical copy of the data stored on SQLNode1’s F: drive.

If you look in Failover Cluster Manager, you will see “DataKeeper Volume F” in Available Storage. Failover clustering will treat this like it is a regular shared disk.

 9. Install SQL Server on SQLNode1 as a new clustered instance

With WSFC configured and storage replication active, we can now install SQL Server FCI.

Steps:

1. On SQLNode1, launch the SQL Server installer.
2. Choose “New SQL Server failover cluster installation.”
3. Complete the installation and restart SQLNode1.

You will notice during the installation, that the “DataKeeper Volume F” is presented as an available storage location.

10. Install SQL Server on SQLNode2 and SQLNode3 (Add Node to Cluster)

To complete the SQL Server FCI, we must add the remaining nodes to the cluster.

Steps:

1. Run SQL Server setup on SQLNode2 and SQLNode3.
2. Choose “Add node to an existing SQL Server failover cluster.”
3. Validate cluster settings and complete the installation.

Once SQL Server is installed on all three cluster nodes, Failover Cluster Manager will look like this.

11. Update SQL Server to use a distributed network name (DNN)

By default, SQL Server FCI requires an Azure load balancer (ALB) to manage client connections. However, Azure now supports distributed network names (DNNs), eliminating the need for an ALB.

Steps:

1. Update SQL Server FCI to use DNN instead of a traditional floating IP.
2. Ensure name resolution works across all nodes.
3. Validate client connectivity to SQL Server using DNN.

Detailed instructions on how to update SQL Server FCI to use DNN can be found in the Microsoft documentation.

Add-ClusterResource -Name sqlserverdnn -ResourceType "Distributed Network Name" -Group "SQL Server (MSSQLSERVER)" 

Get-ClusterResource -Name sqlserverdnn | Set-ClusterParameter -Name DnsName -Value FCIDNN 

Start-ClusterResource -Name sqlserverdnn 

You can now connect to the clustered SQL instance using the DNN “FCIDNN.”

12. Install SQL Server Management Studio (SSMS) on all three nodes

For easier SQL Server administration, install SQL Server Management Studio (SSMS) on all three nodes.

Steps:

1. Download the latest version of SSMS from Microsoft.
2. Install SSMS on SQLNode1, SQLNode2, and SQLNode3.
3. Connect to the SQL Server cluster using DNN.

 13. Test failover and switchover scenarios

Finally, we validate HA and DR functionality by testing failover and switchover scenarios:

1. Perform a planned failover (manual switchover) from SQLNode1 to SQLNode2.
2. Simulate an AZ failure and observe automatic failover.
3. Test cross-region failover from SQLNode1 (East US 2) to SQLNode3 (Central US).

This confirms that SQL Server FCI can seamlessly failover within AZs and across regions, ensuring minimal downtime and data integrity.

Four nines uptime

By following these steps, we have successfully deployed, configured, and tested a multi-AZ, multi-region SQL Server FCI in Azure. This architecture provides 99.99% uptime, seamless failover, and disaster recovery capabilities, making it ideal for business-critical applications. 

Dave Bermingham is senior technical evangelist at SIOS Technology.

—

New Tech Forum provides a venue for technology leaders—including vendors and other outside contributors—to explore and discuss emerging enterprise technology in unprecedented depth and breadth. The selection is subjective, based on our pick of the technologies we believe to be important and of greatest interest to InfoWorld readers. InfoWorld does not accept marketing collateral for publication and reserves the right to edit all contributed content. Send all inquiries to doug_dineley@foundryco.com.