So it has been some time since I worked on my “Deploying a highly available App Service Solution” blog series. The last blog in this series was also a standalone blog: Using Azure Active Directory Domain Services for Azure Stack VM’s Authentication and Identity, but since we need some sort of Active Directory environment in this solution I included it within this series. This next blog will focus on how we deployed an AlwaysOn SQL Solution for the App Service databases. So officially I guess this would be Part IV but who’s counting?

Just to be transparent I am not a SQL expert and had to rely on a lot of different resources to deploy this SQL Always On configuration. That being said I may get a term incorrect here and there. However, I will try to provide a solution that can be replicated by anyone that will work.

Note: The Microsoft Docs team just published a guide to deploying SQL Always On running on Azure Stack as well. This blog will follow more of how I did it based off of a document for building Always On SQL Solutions on Azure.

https://docs.microsoft.com/en-us/azure/azure-stack/user/azure-stack-tutorial-sql

This blog also is more of my notes that I took while deploying this solution. There will be very little step by step for this specific blog. I will try and provide links to along the way to resources that will guide you step by step.

Review of network and infrastructure

I want to start with a little review of our current infrastructure up to this point. We have created a Resource Group called AzS.DFW.Infrastructure on our Default Provider Subscription.

Note: Out of all the current PaaS Resource providers, App Service is the only one that is allowed to be deployed within this subscription. MySQL and SQL RP’s need to be deployed within a tenant subscription.

Also, we will be using SQL Enterprise for our SQL Servers. This is allowed as long as the only databases on this instance are the App Service Databases.

We are planning on deploying App Service in the same Resource Group and the same virtual network. We have deployed Azure AD DS and are using AD DS for authentication and Identity within our Azure Stack as well. We have a Site 2 Site VPN from our Azure Stack vNet to a vNet in our Azure Subscription.

We have a single vNet for this solution with multiple subnets. Our DNS servers for this vNet we have pointed to our DNS servers for our Azure ADDS solution. Our address space for this vNet is 10.1.0.0/16. We created 10 Subnets including our future App Service Subnets.

Our Subnets

All our management based VM’s will be in the mgmtSubnet, our SQL cluster in the sqlSubnet, etc.

Deployment of SQL

A majority of the work I did I followed from the following Microsoft Doc’s website: https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr

Availability Set

The very first thing I did was create an availability set for our SQL VM’s in our AzS.DFW.Infrastructure Resource Group. More information about Availability Sets on Azure Stack can be found here. https://docs.microsoft.com/en-us/azure/azure-stack/azure-stack-key-features#high-availability-for-azure-stack. Currently our AS is set to a single fault domain and a single update domain.

Virtual Machine Configurations

I then deployed two VM’s using the SQL Server 2017 Enterprise Windows Server 2016 image from the market place. At the time that I deployed my solution the SQL Always-On ARM Template wasn’t available for Azure Stack and I didn’t have the time to edit it to work with Azure Stack.     The following is the configurations I did for the VM’s created.

Once the VM’s where deployed the below configurations are needed:

• Joined them to our Azure ADDS Domain.
• Add a SQL Service account to the local administrator group.
• Configure the firewall on both servers to allow 1433, 5022, 59999, and 58999.

Configure Firewall on VM’s.

We will need SQL port 1433 open. We also need 2 Azure load balancer probe ports open. We will be using 59999 and 58999. We also need the Database mirroring endpoint open. This port is 5022.

Disk Configurations

I also added 3 new data disk to each of the SQL VM’s. Which I will later use for the TempDB, Data, and Logs. I also made sure that only the TempDB and Data disk was set for Read-only for Host Caching and the Logs disk was set to none.

SQL Server Service Accounts

We then set the SQL Server service account on each server to use our domain SQL service account.

Configure system account permissions

If not created, create an account for “NT Authority\System” on each SQL Server Instance.

USE [master]
GO
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

Then grant the following permissions

GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO

Add Failover Clustering Features

Last but not least we installed the failover clustering features to both VM’s. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-prereq#add-failover-clustering-features-to-both-sql-server-vms

Configuration of Cluster

Create Cluster

Once we have all the prerequisites completed we can now move forward with configuring our cluster. There are some configurations and steps that are needed in order to properly deploy a cluster in Azure or Azure Stack. When we deploy this cluster we are going to use the defaults and only add the first node to the cluster. On the validation warning you will need to select “No. I do not require support from Microsoft for this cluster…..” Do not add storage and just use defaults.

The configuration data below is what we will be using for our cluster. The initial cluster IP address will be different and need to be changed per the Microsoft Docs.

Edit Cluster Core Resource

You should notice within Failover Cluster manager that the Cluster Core Resources is offline and in a failed state. This is due to the fact that the IP address for the Cluster Core Resources is the same IP as the local server. This will need to be changed to the actual static IP address that will be used. For our cluster we are going to be using 10.1.3.11 for our Cluster IP address. Once changed you and then bring this resource online. You can follow this document if you need step by step instructions. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-tutorial#create-the-cluster

Add remaining SQL Nodes

At this time we will add the 2^nd SQL node to the cluster. During this process do not add existing storage. Since this is an AlwaysOn configuration there isn’t shared storage.

Quorum/Witness

We are going to then add a quorum using a Cloud Witness. We will use Azure Storage for our Cloud Witness but it can also be done on Azure Stack as well. If you want to use Azure Stack for the cloud Witness the steps are similar except you will need to use a different endpoint. Here are some steps you can follow in order to use a Cloud Witness in Azure Cloud. https://docs.microsoft.com/en-us/windows-server/failover-clustering/deploy-cloud-witness

For both Azure and Azure Stack at a high level you will have to create a new Storage Account first. Then you will need to grab the Storage Key for that Storage account. You will also need your service endpoint. For Azure you can leave the default. For Azure Stack that service endpoint it will be your external endpoint. For example dfw.azurestack.domain.com.

Enable Availability Groups

We now need to enable the AlwaysOn Availability Groups feature. You can do this by following these step by step instructions. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-tutorial#enable-availability-groups

Create Test Database

We will create a Test Database on the first SQL Server in order to get configure our AlwaysOn Availability groups. This can be done by following the following examples. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-tutorial#create-a-database-on-the-first-sql-server

Create Availability Group

We created the availability group named DFWAPPSQL-AG. One note we need to highlight, do not create the Listen during this stage. We will do that later on. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-tutorial#create-the-availability-group

Once you have created the availability group and verified that it was successful we can then move on. We should now have replicas of the test database on both SQL instances. We are now ready to create our Azure Stack load balancer and then create our listener as well.

Create the Azure Stack Load Balancer

Running VM’s on Azure stack and SQL Server Availability groups on Azure will require a load balance. The load balance will consist of the IP address for listeners and the actual failover cluster. The following section of the Microsoft Document will guide us step by step creating the load balancer, creating the two health probes, and the load balancer rules. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-tutorial#create-an-azure-load-balancer

Here are the configurations we used:

Here is the configuration for the Windows File Cluster Endpoint frontend IP Configurations.

Configure Listener

Now we can configure our Availability Group listener. This will be done via the Failover Cluster Manager. The following Microsoft Document can help you step by step. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-tutorial#configure-listener

You will also need to set the listener port within the Availability Group.

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-tutorial#set-listener-port

Then you will need to test the connection to the listener.

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-tutorial#test-connection-to-listener

What Next?

At this point you should be able to connect to your SQL Servers using the Listener Name from SQL Server Management Studio. This name should also be available from every server that is in that existing virtual network as well.

References

The follow are links that I used along the way to help me deploy this SQL Server Solution. So I would like to give credit for their knowledge. The resource I used the most of course was the Microsoft Document on Microsoft Docs.

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr

I also went between the following other blogs and Microsoft Docs sites as well.

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performance

https://davidfleming.org/2017/03/09/deploy-sql-server-2016-availability-groups/

Also, Microsoft Docs team just released a new tutorial on how to create highly available SQL Databases on Azure Stack.

https://docs.microsoft.com/en-us/azure/azure-stack/user/azure-stack-tutorial-sql

Final Thoughts

I will be honest, this probably isn’t my best blog. Like I said, most of it is just notes that I took along the way as I deployed a SQL AlwaysOn Cluster on my Azure Stack. For someone who doesn’t spend a lot of time with SQL this was a little challenging at first. Also, at the time in Azure there was an ARM Template that created these AlwaysOn clusters for you. This just recently became available on Azure Stack. My next blog in this series will be about how I deployed a SOFS cluster on top of S2D in order to have a highly available file share for App Service.

Deploying a Highly Available App Service Solution Series:

Azure Stack: Deploying A Highly Available App Service Solution Series
Azure Stack: Highly Available App Service Solution – Part II – Did I just start eating an elephant?
Using Azure Active Directory Domain Services for Azure Stack VM’s Authentication and Identity