Mission critical applications for which downtime is not acceptable, require a database that’s always available. Planned maintenance (software patching, hardware upgrades, disaster recovery rehearsals) and unplanned outages (system-level, infrastructure or process failures) account for SQL Server downtime. To minimize foreseeable downtime, you can unite SQL Servers in an “Always On Availability Group” to form a High Availability Cluster. In a High Availability Cluster, SQL servers can go down automatically or manually, while other SQL Servers immediately take over their job during a failover process.

Azure SQL Database and Azure SQL Managed Instance feature a built-in high availability solution, deeply integrated with the Azure platform. This article concerns the SQL Server on an Azure Virtual Machine, which is often used to accommodate features like file stream and CLR. We describe an enterprise level solution that comes with the price of a double configuration in terms of setup, maintenance, CPU and licensing.

Azure Blob Storage for Quorum witness

SQL Servers in a High Availability Cluster communicate and are aware of each other’s availability. If an SQL Server is not available, a voting mechanism (quorum) kicks in to see which of the other SQL Servers are online. SQL Servers with the least amount of votes shut down. To prevent stalling, in case of even amounts of votes, a Quorum witness is maintained.

The Quorum witness takes form of a 5 MB file called witness.log. In case all SQL Servers have an equally reliable internet connection, Microsoft recommends to store this file in a Storage account. Otherwise, you can provide a file share or a shared disk.

Note that a Quorum witness already adds value starting from two SQL Servers in the cluster. In a cluster formed with two SQL Servers, one of them holds precedence. In a setup without the Quorum witness, if that primary SQL Server goes down the cluster goes down as well. In case of a Quorum witness, the other SQL Server takes over.

Setting up SQL Server High Availability Cluster

Before creating the cluster, make sure:

• Virtual Machines are part of the same subnet;
• Virtual Machines have enhanced networking switched on for Azure;
• Virtual Machines are reachable through ports 1433, 1533, 1633 and 1733 (bi-directional);
• Virtual Machines are in different Zones or have a Availability Set with each machine in a different domain and upgrade zone;
• Server level changes or changes that are not written to the SQL Server transaction log file (like logins, linked servers and SQL Agent jobs) are synchronized manually in the secondary replicas;
• Failover Clustering is installed on all SQL Servers (check Server Manager > Manage > Add Roles and Features for “Failover Clustering”).

Create the cluster:

1. Open the Failover Cluster Manager snap
2. Right click “Failover Cluster Manager” > “Create Cluster…”
3. Select the SQL servers that will form the cluster and name the cluster

Failover Cluster Manager will create a machine and DNS endpoint for the cluster in Active Directory. Once the cluster is created, you should be able to ping the cluster straight away.

Add the Cloud Witness Quorum:

1. Right click the Cluster > More Actions > Configure Cluster Quorum Settings…
2. Select “Select the quorum witness” option
3. Select “Configure a cloud witness”
4. Enter the “Azure storage account name”, “Azure storage account key” and “Azure storage endpoint”
5. Finish the quorum settings for the cluster

A cluster solution is supported by Microsoft only if you run all cluster validation tests, and all tests succeed (with or without warnings).

To run the report above:

1. Select the cluster in the Failover Cluster Manager snap
2. In the sidepanel, below Actions, click “Validate Cluster…”
3. Check the Nodes are “Validated” and the Results are “Success”

The cluster is now configured for Failover with a cloud witness, as you can see at the bottom of the summary pane:

Enable Always On Availability Groups (on every SQL Server):

1. Open Server Configuration Manager
2. Right click the instance > Properties, select the tab “Always On Availability Groups”
3. “Windows Server Failover Cluster” should be pre-populated, if it is not, delete and re-configure the cluster
4. Tick the box “Enable Always On Availability Groups”

After configuring the cluster, the application should be updated to point towards the DNS of the cluster (or load balancer where applicable).

Back-up

High Availability is something different than disaster recovery. You will still need to back-up your data to protect yourself from data loss as changes in a database propagate to other available SQL Servers on short notice. For this, we create a backup file share that is available for all SQL Servers.

1. Create a folder, for instance “AlwaysOn”
2. In Computer Management, expand Shared Folders, right click the Shares and click “New Share…”
3. Select the AlwaysOn Folder
4. Create the share name as “AlwaysOnBackup”
5. Next, Set Customize Permissions for the Service Account for the instance and allow “Full Control”, “Change” and “Read”

Need a hand?

We’re here to help you to setup Always On Availability Groups for your SQL Server and update your procedures to take advantage of increased availability by the cluster.