SQL Server Always On Across Two Data Centers with Dual Witnesses and DNS CNAME – Part 1
Amir Kordestani
May 30, 2025 4:02:59 PM
In our existing SQL Server Always On configuration, we had a two-node setup, with each node hosted in a separate data center, and a single witness located in the secondary data center (DR). Due to the design requirements of our application, the availability group was configured for manual failover.
However, this architecture introduced a critical limitation: when the DR DC went offline, we lost quorum, and the Always On configuration became unusable. As a result, we were forced to disable and delete the availability group, and then recreate it from scratch, including re-adding all databases—an error-prone and time-consuming process.
To overcome this limitation and ensure high availability across both data centers, we implemented a more resilient architecture: two file-share witnesses—one in each data center—combined with a DNS CNAME record to abstract the witness name. This solution provides redundancy for the quorum configuration and allows the availability group to survive the failure of either data center without needing to rebuild the setup.
In these two blogs, I will walk you through how we built a more resilient Always On architecture. In Part 1 (SQL Server Always On Across Two Data centers with Dual Witnesses and DNS CNAME), I’ll explain the setup of dual file share witnesses across two data centers, along with the use of a DNS CNAME to manage quorum effectively. In Part 2 (SQL Server Always On Across Two Datacenters: Manual and Automatic Failover Testing), I’ll demonstrate how we tested failover in both manual and automatic modes—simulating complete failure of either the primary or secondary data center—and show how this architecture ensures availability without needing to rebuild the configuration.
I created the witness1 VM in the primary DC and witness2 in the DR DC, and assigned each VM an IP address within the IP address range designated for the Primary DC and the DR DC.
I created a shared file with the path of "C:\clusterfileshared" on each node in the cluster and each witness.
For example, you can see how I created the shared folder on witness1.
Create the "clusterfileshared" folder in the C Drive on Windows.
Write, click on the folder "clusterfileshared" and click properties
Go to the sharing tab and click advanced sharing
Click on the 'Permissions' button.
Grant Full Control permissions, then click Apply and OK
If you recheck the properties of this folder, you should see the following.
To provide flexibility and fault tolerance for the file share witness in our Always On configuration, we use a CNAME (Canonical Name) record in DNS. In Windows environments, a CNAME allows you to create an alias that points to another hostname, making it easier to switch the witness between data centers without changing SQL Server configurations.
To create a CName in the DNS Server (for my test, it is Windows Server 2016), go to the DNS server and open the Server Manager >> Tools >> DNS
Create DNS records for WitnessSite1 and WitnessSite2 in the DNS forward zone.
Create the CNAME record in the DNS forward lookup zone by right-clicking on an empty area and selecting 'New Alias (CNAME)'.
Double-click on the DNS server name.
Select the primary witness, which here is WitnessSite1, and click Apply
Now, the alias has been created.
Below you can see my final DNS configuration.
To test if the alias is working correctly, go to SQLNODE1 or SQLNODE2 and nslookup the alias. Using nslookup is a suitable method for testing DNS resolution, including alias resolution.
Another test is to attempt to access the shared folder using its alias name on SQLNODE1 or SQLNODE2.
5.1. Remove the Existing File Share Witness
Now, remove the current file share witness before adding a new one.
NOTE: We can temporarily remove the old witness without affecting the availability group's online operation.
5.1.1- Using Failover Cluster Manager on SQLNODE1 remove the old witness temporarily:
Open Failover Cluster Manager >> Right-click on your cluster name. >>select More Actions >> Click Configure Cluster Quorum Settings. Then select More Actions >> Click Configure Cluster Quorum Settings.
Select the quorum witness
Select Do not configure a quorum witness (temporarily remove the old witness).
Click Next and Finish.
5.1.2- Checking Availability Group status after removing old witness:
This is the status of the availability group on the SQLNODE1 (SQLNODE1 status after temporarily removing the old witness).
This is the status of the availability group on SQLNODE2.
This is the Availability group status in the Failover cluster after temporarily removing the old witness.
This is the Availability group status in the SSMS after temporarily removing the old witness. Both primary and secondary Replicas are up and running
Therefore, we can temporarily remove the old witness without affecting the availability group's online operation.
6.1. Using Failover Cluster Manager on SQLNODE1 to configure \\WitnessSite1\clusterfileshared as quorum witness for availability group:
Open Failover Cluster Manager >> Right-click on your cluster name. >>select More Actions >> Click Configure Cluster Quorum Settings. Then select More Actions >> Click Configure Cluster Quorum Settings.
Select the quorum witness
In the File Share Path, enter \\witness\clusterfileshared. This path utilizes the CNAME 'witness', which resolves to the IP address WitnessSite1.
Click Next and Finish.
Now, the witness in the Failover Cluster Manager for our cluster has been configured as \\witness\clusterfileshared.
To verify the outcome of this change, check the contents of the following folders on SQLNODE1 or SQLNODE2:
The above image shows that files related to the new File Share Witness have been added to \\WitnessSite1\clusterfileshared, while no files have been added to \\WitnessSite2\clusterfileshared.
Note: To complete the configuration, perform steps 5 and 6 on the secondary replica.
With this setup—two file share witnesses distributed across both data centers and a DNS CNAME for flexibility—we’ve created a more resilient SQL Server Always On environment that can better withstand datacenter-level failures. This approach eliminates the need to rebuild the availability group when one site goes down, saving time and reducing risk during outages.
In Part 2 (SQL Server Always On Across Two Datacenters: Manual and Automatic Failover Testing) of this blog series, I’ll demonstrate how we tested this configuration by simulating real failover scenarios, including the loss of the primary and secondary datacenters. I’ll cover both manual and automatic failover modes and show how the system responds under pressure to ensure continuity and high availability.
Fill out the form below to unlock access to more Eclipsys blogs – It’s that easy!