SQL Server (2017) Always On with Cloud Witness - Azure Resource Manager Template

The short version of this blog post is that I have published a new SQL Server Always On Template. Check it out.

Microsoft SQL Server has an important high availability/disaster recovery feature called Always On. It allows you to deploy a database with multiple replicas that are kept synchronized and should the primary replica fail, one of the secondary replicas will take over. There are a few different Azure Resource Manager templates out there for deploying this in the Azure cloud. Here are just a few of them:

• SQL Server 2014 Always On including Domain Controllers
• SQL Server 2014 Always On for Existing VNET and Domain
• SQL Server 2016 Always On for Existing VNET and Domain

Additionally, there is also a template available through the Azure Commercial cloud portal.

The components involved in those deployments are continuously updated. Specifically, SQL Server 2017 has been released and PowerShell DSC modules such as xSqlServerDsc have been updated many times, e.g. xSqlServerDsc has recently been renamed to SqlServerDsc. There are also some features in SQL Always On, that could potentially help simplify the deployments. One such feature is the Cloud Witness, which uses Azure Blob storage instead of a file share on a VM for cluster quorum.

I have put together a SQL Server 2017 Always On template that includes the following features:

• SQL Server 2017
• Always On Availability Groups
• Cloud Witness for cluster quorum, eliminates a VM.
• A single, updated PowerShell DSC module that uses the latest versions of SqlServerDsc, etc.
• Managed Disks
• Azure SQL IaaS Agent to enable automatic patching, disk configuration, etc.

The template deploys into an existing subnet and joins an existing domain. You can use my core-network template to set up the basic networking and domain controllers. The deployment with Domain Controllers would look something like:

After deployment, you will have a SQL Server cluster endpoint (default name: SQLClusterAG) you can connect to from the virtual network on port 1433. There are no databases in the initial deployment. To add a database to the availability group you should follow the "usual" workflow:

1. Create the database on the primary replica.
2. Take a full backup of the database. You cannot add a database that has not been backed up.
3. Add the database to the availability group.

This is a works-in-progress and I will make and effort to update it to include more features and configuration options, but it is a starting point. Let me know if you have questions/comments.

• Anonymous
  January 03, 2018
  This is a great post Michael. Something that I will keep handy in case I need to work on SQL Always On.