Deploy a SQL AG in AKS with DxOperator and Rancher Prime

Introduction

This article details the deployment of a SQL Server Always On Availability Group in Azure Kubernetes Service using DxEnterprise’s DxOperator feature and Rancher Prime. This is the fastest and easiest approach to deploy highly available SQL Server containers. It helps IT pros overcome the inherent complexity of Kubernetes with deployment automation and an easy-to-use management interface, and it unlocks a direct path to the industry’s lowest downtime for SQL Server in Kubernetes.

Prerequisites

• A target Kubernetes cluster with at least two nodes, managed by Rancher or Rancher Prime by SUSE. The Kubernetes cluster must have the ability to provision dynamic persistent volume claims (such as with Longhorn).
• A valid DxEnterprise license with Availability Group management features and tunnels enabled.
• An Internet connection

Installation

Install the DxOperator chart

1. Log into Rancher via the web UI.
2. Select the top-left hamburger menu, then select the managed cluster into which you want to install DxOperator.
3. In the left-hand menu, select App > Charts.
4. In the list of available charts, select DxOperator.
5. Install the chart.
   1. Select Install in the top-right corner.
   2. Select Next in the bottom-right, then Install.
   3. Wait for the installation to complete before proceeding.

Install the DxOperator – DxE + SQL Server AG chart

1. Add Kubernetes secrets for SQL Server and the DxEnterprise cluster passkey.
   1. In the left-hand menu, select Storage > Secrets.
   2. Select Create in the top-right corner.
   3. Select Opaque option.
   4. Name the secret dxe.
   5. Enter a key name as DX_PASSKEY with the value of your DxEnterprise cluster passkey.
   6. Select Add.
   7. Enter a key name as DX_LICENSE with the value of your DxEnterprise license key. A developer key can be obtained at https://dh2i.com/trial/.
   8. Select Create in the bottom-right corner.
   9. Select Create in the top-right corner.
   10. Select Opaque option.
   11. Name the secret mssql.
   12. Enter a key name as MSSQL_SA_PASSWORD with a value of a strong SA password. Please note that your password must meet SQL Server password requirements, or the container deployment will fail. See the SQL Server Password Policy for more details.
   13. Select Create in the bottom-right corner.
2. In the left-hand menu, select Apps > Charts.
3. In the list of available charts, select DxOperator – DxE + SQL Server AG.
4. Install the chart.
   1. Select Install in the top-right corner.
   2. Select Customize Helm options before install checkbox.
   3. Select Next in the bottom-right.
   4. Enter CONTAINED for the Availability Group Options.
   5. Select Create Load Balancers checkbox to allow external access.
   6. Select DxEnterprise edit options.
   7. Select Accept EULA checkbox.
   8. Select dxe for Cluster Secret.
   9. Select SQL Server edit options.
   10. Select Accept EULA checkbox.
   11. Select mssql for SQL Secret.
   12. Select Next in the bottom-right.
   13. Select Install in the bottom-right.

Validation

After the installation is complete, you can connect to your Availability Group and the DxEnterprise cluster using SQL Server Management Studio (SSMS).

1. Get external IP address of the primary pod.
   1. In the left-hand menu, select Services.
   2. Select dxenterprisesqlag-0-lb.
   3. Make note of the Load Balancer IP address.
2. View the Availability Group using SSMS.
   1. Launch SSMS.
   2. Connect to the SQL Server instance using the load balancer IP address of the primary pod and the SQL Server SA password.
   3. In the SSMS Object Explorer, expand Always On High Availability > Availability Groups.
   4. Right-click on AG1 and select Show Dashboard.
   5. Browse the Availability Group details pane.
3. Simulate system failure to observe automatic failover.
   1. In the left-hand menu, select Workloads > Pods.
   2. Select the running dxenterprisesqlag-0 pod.
   3. Select Delete, the Delete again when prompted.
4. Get external IP address of the secondary pod.
   1. In the left-hand menu, select Services.
   2. Select dxenterprisesqlag-1-lb.
   3. Make note of the Load Balancer IP address.
5. View the Availability Group using SSMS.
   1. Connect to the SQL Server instance using the load balancer IP address of the secondary pod and the SQL Server SA password.
   2. In the SSMS Object Explorer, expand Always On High Availability > Availability Groups.
   3. Right-click on AG1 and select Show Dashboard.
   4. Browse the Availability Group details pane.
   5. Notice that the secondary is now the primary replica for the SQL Server Availability Group.

Summary

DxOperator is a powerful extension to Kubernetes that uses custom resource definitions to automate the deployment of DxEnterprise clusters. DxEnterprise then provides all of the instrumentation to create, configure, manage and provide automatic failover for Microsoft SQL Server Availability Group workloads in Kubernetes. With Rancher, deploying a fully operational DxEnterprise + SQL Server Availability Group requires just a few clicks, and can be completed in seconds. Once deployed, DxEnterprise will ensure that automatic failover is carried out if the SQL Server Availability Group container encounters any system failures.

Addition Information

• Quickstart: Deploy an Azure Kubernetes Service (AKS) cluster using Azure CLI – Azure Kubernetes Service | Microsoft Learn
• Installing Rancher on Azure Kubernetes Service | Rancher
• Rancher Prime in Azure: Usage Instructions
• [Demo Video] Deploy SQL Server AG in K8s with DxOperator and Rancher Prime

If you’re interested in learning more about DH2i’s approach to smart high availability technology, get signed up for a one-on-one demo today.

Request a Demo

The author

OJ Ngo

Read more from this author