How to Create SQL Server 2019 Failover Clustered Instances in Azure

Most of what you see in this post is similar to our prior post in the series – Azure shared disks – Failover Clustered Instance – SQL Server 2016. The key difference when setting up Failover Clustered Instances with SQL Server 2019 in Azure is we’re not going to utilize an Azure Load Balancer to provide access to the failover clustered instance. We’re going to focus on the Distributed Network Name access point.

Distributed Network Name access point

In SQL Server 2019 CU2 (or later) failover clustered instances, the listener was enhanced to work with the Windows Server Failover Cluster DNN access points. Prior to supporting DNN access points, a virtual IP access point was used but wasn’t supported in Azure without creating a load balancer. DNN access points reduce the complexity of FCIs in Azure by allowing the SQL client to connect without the need for a load balancer.

Create the cluster and failover clustered instance

Creating the cluster, using Azure Shared Disks, was covered in our previous post here. The difference is that we’re installing a SQL Server 2019 FCI and then applying CU 7 to ensure distributed network names are supported. Go ahead and create your Windows failover cluster and then create the SQL Server 2019 FCI. You won’t be able to connect to the FCI instance just yet because we’re not setting up an Azure load balancer. Failover the clustered role to all nodes within the cluster just to ensure there’s no issues.

Create the DNN

After the FCI has been created, we’ll need to rename the virtual network name resource. However, this isn’t required if you’re planning to use a different name for the DNN and access the instance using a different DNS name than you specified when setting up the FCI. I renamed mine to SEROSQLFC_VNN (shown below). To rename, right click on the Server Name resource in Failover Cluster Manager, and choose properties. I appended the existing name with “_VNN”. Don’t delete this resource as it is a required component of the FCI infastructure.

Next, create the DNN. I’ll be using SEROSQLFC to access the instance and used the powershell script below to create it.

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

Get-ClusterResource -Name SEROSQLFC_DNN | Set-ClusterParameter -Name DnsName -Value SEROSQLFC

Once created, you should see a new resource within the cluster role.

Bring the DNN online. The instance must be restarted before accessing. I typically failover to the other node, attempt to access the instance from a remote server using SSMS, and then failback to ensure the instance can be accessed when hosted on either node.

IP conflicts and how to avoid them

When using a distributed network name access point there’s no longer a load balancer reserving virtual IP address in Azure and there’s a risk that another resource on the virtual network will be assigned the same IP address as the virtual IP address used by the FCI. This can lead to an IP conflict. The powershell script below was used to create an APIPA address to avoid this issue. Another option is to create a dedicated network adapter in Azure and reserve the IP address used by the virtual IP address resource. Both of these options can be seen here.

Retrieve the virtual IP resource name by opening Failover Cluster Manager and right clicking on the IP address resource (example below). Choose properties and note the resource name.

Get-ClusterResource "SQL IP Address 1 (SEROSQLFC)" | Set-ClusterParameter –Multiple @{"Address”=”169.254.1.1”;”SubnetMask”=”255.255.0.0”;"OverrideAddressMatch"=1;”EnableDhcp”=0}

Take the IP Address resource offline and back online for the changes to take effect. This will also take SQL Server offline so these resources will need to be restarted as well.

Additional resources for Creating Failover Clustered Instances

Distributed network names and shared disks simplify the usage of FCIs in Azure. However, there a few issues to watch out for. This post is not meant to provide an in depth review but a quick overview of what is possible using these new features available in Azure, Windows OS (since 2016), and SQL Server 2019 (cu2 or later). The resources below provide additional detail on the use of DNNs.

FCI DNN & SQL Server features

Rename a SQL Server Failover Cluster Instance

Create DNN for FCI

Conclusion

Let us know your thoughts regarding this option for high availability and if it’s something you may be interested in leveraging in the future.

Thanks for reading!