SQL Server Always On Availability group on AKS with DH2i’s DxOperator and Rancher by SUSE

Did you know you can do a quickfire deployment of SQL Server Always On Availability Groups on Azure Kubernetes Service using DH2i’s DxOperator and Rancher by SUSE within few mins ? 

You might be already aware of deploying SQL Server Always On Availability Groups for SQL Server containers on Kubernetes using DH2i's DxOperator, Refer to Always on Availability Groups for SQL Server containers on Kubernetes - The DH2i's DxOperator way!! - Microsoft Community Hub for more details. 

Utilizing Rancher with Azure Kubernetes Service (AKS) offers several benefits, particularly in terms of security, management, and monitoring capabilities.

Rancher enhances AKS security by providing tools for hardening, governance, and integrated logging and monitoring. It also includes a built-in service mesh and has recently added CIS Scanning to assess RKE clusters against the CIS Benchmark for Kubernetes.

It simplifies cluster management by offering features such as cluster provisioning, centralized security management, and a global catalog for multi-cluster applications. It allows for infrastructure as code using Terraform, enabling consistent deployment and self-service for development teams. For monitoring SQL Server using Rancher, you can leverage tools like Prometheus and Grafana, which are part of Rancher's application catalog. These tools provide observability and help in monitoring application performance

Recently I had the opportunity of working together on a joint solution with our partners DH2i and SUSE. Here are the steps to perform this simple and effective solution.

Before we get started, there are a few pre-requisites.

• A Kubernetes cluster managed by Rancher/Rancher prime by SUSE to deploy SQL Server instances; In this demo I use Azure Kubernetes Service (AKS) cluster.
• Client machine to run kubectl commands, manage object creation and administration on Kubernetes cluster. I’m using a windows machine and here are the instructions to setup kubectl to connect and manage AKS cluster.
• Valid DxEnterprise License with Availability group management features. ( you can get your license through Trial - DH2I
• On the same client machine, I also have the SSMS (SQL Server Management Studio) or Azure Data Studio (ADS) installed to connect to SQL Server instances and view availability groups.

In order to setup Rancher for AKS, Follow the steps mentioned in Installing Rancher on Azure Kubernetes Service | Rancher.

This demo describes the following steps required to perform this deployment, viz.

1. Installing DxOperator chart
2. Configuring secrets
3. Installing DxOperator – DxE + SQL Server AG deployment
4. Connection & Validation through SSMS

1. Installing the DxOperator chart from Rancher portal

• Open the Rancher WebUI using hostname.
• Click on left hand side menu, Select the managed cluster into which you want to install DxOperator.
• Click on Apps -> Charts.
• Search for DxOperator, click on Install, Click on Next and then Install.

2. Creating the secrets

• From left hand menu, Click on Storage -> Secrets.
• Select Create and then Click on Opaque option.
• Enter secret name dxe
• Enter the key name as DX_PASSKEY wth value of DxEnterprise cluster passkey.
• Select Add, then enter new key name as DX_LICENSE with value of your DxEnterprise license. A Developer key can be obtained at https://dh2i.com/trial/ .
• Select Create in the bottom right corner.
• Select Create at top right corner for new mssql secret.
• Select Opaque, Name the secret as mssql.
• Enter key name as MSSQL_SA_PASSWORD with value of 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.
• Select Create in the bottom-right corner.

3. In the left-hand menu, select Apps > Charts.

• In the list of available charts, select DxOperator – DxE + SQL Server AG.
• Install the chart.
• Select Install in the top-right corner.
• Select Customize Helm options before install checkbox.
• Select Next in the bottom-right.
• Select Create Load Balancers checkbox to allow external access.
• Select DxEnterprise edit options.
• Select Accept EULA checkbox.
• Select dxe for Cluster Secret.
• Select SQL Server edit options.
• Select Accept EULA checkbox.
• Select mssql for SQL Secret.
• Select Next in the bottom-right.
• Select Install in the bottom-right.

4. Connecting and Validating through SSMS

• From Rancher webUI, Go to Services
• Click on dxenterprisesqlag-0-lb, Fetch the external load balancer IP.
• Use that IP address to connect using SSMS along with SQL Server SA password
• From object explorer, Click on Always on High Availability -> Availability Groups, Right click on AG1 and then show dashboard.

Note:
In this below screenshot, you can see that I have created a CONTAINED AG, this is done by mentioning "CONTAINED" in Availability group options (Step #3 in customizing Helm options before install). You may customize as per your SQL AG requirements.

That’s it, You have a SQL Server Always On Availability groups configured on Rancher managed AKS with DxOperator within few mins !! Go ahead and try this !

References:

• Installing Rancher on Azure Kubernetes Service | Rancher
• Quickstart: Create a public DNS zone and record - Azure portal - Azure DNS | Microsoft Learn
• SQL Server HA Clustering & Software-Defined Perimeter | DH2i