Deployment of SQL Server 2019 Always On Availability Groups on Azure AKS

In the article Introduction to Running SQL Server 2019 on Kubernetes recently published on this forum, the core concepts and basic architecture of implementation of SQL Server 2019 Always On Availability Groups on a Kubernetes cluster are described. In this article, an implementation will be stepped through by leveraging Microsoft Azure-hosted managed Kubernetes service referred to as AKS.

AKS considerably simplifies provisioning of Kubernetes clusters by automatically building all underlying infrastructure components, including compute, storage, and networking, as well as a set of highly available master nodes. It also automatically delivers monitoring and maintenance of the essential cluster services. You decide on the number and size of worker nodes that will host the pods you deploy. The pod deployment process can be carried out via the same standard tools available in traditional Kubernetes environments.

To demonstrate the process of implementing SQL Server 2019 Always On Availability Groups on AKS, we will take advantage of Cloud Shell. Its graphical interface is integrated directly into the Azure portal, allowing you to run PowerShell and Azure CLI-based commands without the need to install the corresponding binaries. The shell also includes several other tools, including kubectl and sqlcmd, facilitating interaction with AKS-based deployments of SQL Server. In addition, Cloud Shell relies on the credentials used to access the Azure portal, eliminating the need to authenticate again.

To provision relevant Kubernetes constructs, including controllers, services, and pods, we will leverage sql-server-sampleGitHub repository, including examples of YAML manifest files containing definitions of SQL Server 2019 HA Operator (managing deployment of a StatefulSet and handling health monitoring of its pods), SQL Server custom resource (hosting highly available SQL Server containers), as well as load balancing services (representing endpoints of the primary and secondary replicas of the containerized Always On Availability Groups).

To start, you will deploy an AKS cluster containing three worker nodes. To accomplish this, launch a Web browser of your choice and browse to the Azure portal at https://portal.azure.com. When prompted, authenticate by using an account with the Owner or Contributor role in the Azure subscription you intend to use for this lab. Click + Create a resource link in the upper left corner of the portal interface, on the New blade, click Containers, and then, in the resulting list of Container-related services, click Kubernetes Service. This will display the Create Kubernetes cluster blade. To provision a new cluster, perform the following tasks on each of the tabs appearing on the blade:

  • Basics:
    • Subscription: Type the name of the Azure subscription that you intend to deploy the cluster.
    • Resource group: Click Create new. In the Name text box, type demo-aks-RG and click OK.
    • Kubernetes cluster name: Type the name demo-aks (the name must be unique within the resource group).
    • Region: Type the name of any valid Azure region where you can provision an AKS cluster.
    • Kubernetes version: Select the default version (1.12.8 at the time of writing this content).
    • DNS name prefix: Type any valid, globally unique DNS name.
    • Node size: Accept the default setting (Standard DS2 v2).
    • Node count: Accept the default setting (3).
  • Scale:
    • Virtual nodes: Ensure that the setting is disabled.
  • Authentication:
    • Service principal: Accept (new) default service principal.
    • Enable RBAC: Accept the default setting (Yes).
  • Networking:
    • HTTP application routing: Accept the default setting (No).
    • Network configuration: Accept the default setting (Basic).
  • Monitoring:
    • Enable container monitoring: Select No.
  • Tags: Do not create any tags.

Finally, on the Review + Create tab of the Create Kubernetes cluster blade, click Create. Wait for the provisioning to complete. This should take about 5 minutes.

With the new AKS cluster implemented, we will next proceed with deployment of the SQL Server 2019 Always On Availability Groups components. Since the remaining part of the provisioning process will be carried out by using the kubectl tool, start with opening Cloud Shell (by clicking the Cloud Shell icon at the top of the Azure portal window). If this is the first time you are launching Cloud Shell in the current Azure subscription, you will be asked to create an Azure file share to persist Cloud Shell files. If so, accept the defaults, which will result in creation of a storage account in an automatically generated resource group.

At this point, you can connect to the cluster by running the following from within Cloud Shell:


az aks get-credentials --resource-group demo-aks-RG --name demo-aks

Next, create a namespace that will host all the corresponding pods with the kubectl tool by running the following from within Cloud Shell:


kubectl create namespace ag1

In order to deploy the SQL Server 2019 HA Operator, download its YAML manifest from the GitHub sql-server-samples repository by running the following from within Cloud Shell:


curl -o operator.yaml https://raw.githubusercontent.com/microsoft/sql-server-samples/master/samples/features/high%20availability/Kubernetes/sample-manifest-files/operator.yaml

Now you are ready to deploy the operator. To accomplish this, run the following from within the Cloud Shell:


kubectl apply -f operator.yaml --namespace ag1

To prepare for deployment of SQL Server containers, you need to also create Kubernetes secrets that contain the password for the sa account and the master database key. This can be done by running the following from within Cloud Shell:


kubectl create secret generic sql-secrets --from-literal=sapassword="5Ql53cr3tw.rd0701" --from-literal=masterkeypassword="5Ql53cr3tw.rd0107" --namespace ag1

Just as before, you will leverage the GitHub sql-server-samples repository to download the YAML manifest of the SQL Server custom resource by running the following from within the Cloud Shell:


curl -o sqlserver.yaml https://raw.githubusercontent.com/microsoft/sql-server-samples/master/samples/features/high%20availability/Kubernetes/sample-manifest-files/sqlserver.yaml

Open the newly downloaded file in an editor (feel free to use any Linux editor), replace every occurrence of 2019-CTP2.1-ubuntu with 2019-CTP2.5-ubuntu, save your changes, and close the file. Next, deploy the SQL Server custom resource pods by running the following from within the Cloud Shell:


kubectl apply -f sqlserver.yaml --namespace ag1

To verify the outcome of the deployment, you can run the following from within the Cloud Shell.


kubectl get pods --namespace ag1 

Wait until all pods are listed with the Running status. Next, to enable connectivity to the SQL Server Always On Availability Group cluster you just deployed, you need to expose it via load balancing services. To implement them, you will download an additional file from the GitHub sql-server-samples repository by running the following from within Cloud Shell:


curl -o ag-services.yaml https://raw.githubusercontent.com/microsoft/sql-server-samples/master/samples/features/high%20availability/Kubernetes/sample-manifest-files/ag-services.yaml

Once the download completes, provision the services by running the following from within Cloud Shell:


kubectl apply -f ag-services.yaml --namespace ag1

To identify the public IP address associated with the primary endpoint, run the following from within Cloud Shell:


kubectl get services --namespace ag1

In the output of the above command, note the value in the EXTERNAL-IP column corresponding to the ag1-primary entry (if the value is listed as <pending>, rerun the same command again). This value represents the public IP address associated with the primary endpoint of the SQL Server Always On Availability Group. Once you identified the IP address, you can connect to the corresponding cluster by running the following from within Cloud Shell (where <ip_address> designates the IP address of you identified in the previous step):


sqlcmd -S <ip_address> -U sa -P 5Ql53cr3tw.rd0701

Once you successfully authenticated, you can add a database to the newly created availability group by running the following from the sqlcmd session within Cloud Shell:


CREATE DATABASE [demodb]
GO
USE MASTER
GO
BACKUP DATABASE [demodb] 
TO DISK = N'/var/opt/mssql/data/demodb.bak'
GO
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [demodb]

While you currently do not have the option of creating such database via SQL Server Management Studio, you can use it to view and manage the cluster going forward. To connect, reference the same credentials and the same IP address you used when connecting via sqlcmd. This concludes our demonstration illustrating a sample deployment of SQL Server 2019 Always On Availability Group cluster on Azure AKS.

# # #

See All Articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles