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

Use DxOperator to easily deploy a SQL Server AG in Kubernetes.

  1. Log into Rancher via the web UI.View the Rancher Prime Dashboard.
  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

Deploy a highly available SQL Server AG with DxOperator and DxEnterprise.

  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.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.

New primary replica on 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

DxOperator – SQL Server Operator for Kubernetes

What is Kubernetes Operator?

Kubernetes operator is a custom controller that extends Kubernetes functionalities. Operator is responsible for managing pods, services, containers, volumes, and other system resources. Operator automates tasks and manages complex applications in Kubernetes.

Learn about the architecture of a standard Kubernetes Operator.

Kubernetes operator uses custom resources (CR) to manage applications and their components. The Kubernetes operator translates the high-level directives provided by the users within a CR into the low-level actions, based on best practices embedded within the operator’s logic.

Introducing DxOperator

DxOperator is a Kubernetes operator that uses custom resource definitions to automate the deployment of DxEnterprise clusters. DxEnterprise then provides all the instrumentation to create, configure, manage, and provide automatic failover for Microsoft SQL Server availability group workloads in Kubernetes.

Sidecar container deployment of SQL Server AG in Kubernetes.

What can you do with DxOperator?

DxOperator gives you full control of how your SQL instances and availability groups are configured and function to support your production workloads. In collaboration with the Microsoft SQL Server team, we have committed the following list of features in this DxOperator version. To see how you can deploy Always on Availability Groups for SQL Server containers on Kubernetes – The DxOperator way, head over to @Amit’s blog.

Custom Pod name

Pod name is a part of the Kubernetes metadata used to identify the deployed pod(s).

Custom pod name

Assigning pods to nodes

DxOperator supports node selection for pods as defined by Kubernetes guidance.

Assigning pods to nodes

Assigning pods to nodes using node affinity

To schedule a pod based on the node affinity, apply the Kubernetes guidance in the CR.

Assigning pods to nodes using node affinity

SQL availability group name and cluster type

Define appropriate name for the SQL availability group that best fits your application or environment. Cluster type can also be specified. Valid values for cluster type are EXTERNAL (automatic failover) and NONE (no automatic failover).

SQL availability group name and cluster type

SQL availability group replicas

Specify the desired synchronous, asynchronous, and/or configuration_only replicas for the deployment. Please note that configuration_only replica is only supported when the cluster type is EXTERNAL.

SQL availability group replicas

SQL availability group options

For SQL Server 2022, DxOperator provides additional options (with) to apply when creating SQL availability groups, including CONTAINED. You can refer to Microsoft documentation for the available options for your version of SQL Server deployment.

SQL availability group options

SQL availability group listener port

Specify the listener port for the SQL availability group.

SQL availability group listener port

Pod load balancer

As part of the deployment of the pod(s), an external load balancer may need to be created to allow external access to the pod(s) running inside Kubernetes. Specify if a load balancer is to be created.

Pod load balancer

Custom Pod annotations

Annotations are key-value pairs associated with the deployed resources, used to provide further information about the resources.

Custom Pod annotations

DxEnterprise container specification

Specify desired container image, image pull policy, EULA acceptance, Kubernetes secret, and whether to join an existing DxEnterprise cluster. If joining an existing cluster is desired, the DX_OTPK value must be present in the Kubernetes secret.

DxEnterprise container specification

SQL Server container specification

Specify desired container image, image pull policy, EULA acceptance, product ID/edition, Kubernetes secret, and config map name.

SQL Server container specification

User-defined volume class

Depending on workload, it may be necessary to assign dedicated storage for each of the containers. DxOperator supports user-defined volume class for each of the desired containers.

User-defined volume class

Quality of Service (QoS)

When deploying production grade applications in Kubernetes, it is important to set the desired QoS for each of the containers to ensure that the container performs within desired metrics. DxOperator supports QoS through the following parameters.

Quality of Service (QoS)

Additional side-car containers

DxOperator allows any additional container(s) to be deployed along with DxEnterprise and SQL Server as side-car container(s). An example of such a side-car container is mssql-tools.

Additional side-car containers

ConfigMap

The ConfigMap object supports SQL Server specific mssql-conf settings to be defined. Any data defined here is copied into the mssql instance’s mssql.conf file.

ConfigMap

Final word

DxOperator is a powerful toolset. With DxOperator, you can easily and quickly deploy any SQL availability group instances in Kubernetes, with any desired specifications and parameters of your choosing. To get started with DxOperator, head over to https://dh2i.com/dxoperator/, sign up for a DxEnterprise developer license, download the quick start guide, and take DxOperator for a spin.