Always on Availability Groups for SQL Server containers on Kubernetes - The DH2i's DxOperator way!!

Update as on 12 Feb 2024: The DH2i Operator is released on 6th Feb 2024, and generally available as per the latest blog from our Partner DH2i, I recommend you read this for further information: DxOperator for SQL Server Availability Group - DH2I.  

 

Recently, I had the privilege of presenting with OJ Ngo from DH2i, our partners who offer the cluster stack for SQL Server on Windows, Linux and containers, at PASS Data Community Summit 2023. In this session, we revealed the private preview of the DxOperator!! - A simple and effective option to deploy SQL Server containers with availability groups configured in high availability mode, with DxEnterprise cluster stack, all deployed on Kubernetes platform.

 

Prerequisites

• A Kubernetes cluster to deploy SQL Server instances. For this demo, I am using Azure Kubernetes Service (AKS) cluster.
• A client machine to run the kubectl commands and manage the object creation and administration on the Kubernetes cluster, today I am going to use a windows machine and here are the instructions to setup kubectl to connect and manage AKS cluster.
• 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.

 

What does this operator do?

This operator allows you to complete all the following tasks in 5 commands and less than 5 minutes!! 

• Deploy three or more SQL Server instances on a Kubernetes cluster with specific mssql-config parameters.
• Configure and install DxEnterprise cluster.
• Create and configure an empty SQL Server Always On availability group (AG) and join all the three or more SQL Server instances to this AG. 

You don’t believe me! Well do it with me to believe it. Do you have 5 minutes now? Yes, then setup the pre-requisites and let's get rolling:

 

Step1:

Create the configmap object on the Kubernetes cluster that has the SQL Server specific mssql-conf settings defined. Here is a sample script:

 

 

 

 

# Create a file called mssqlconfig.yaml, with your specific mssql-conf settings added to it:

apiVersion: v1
kind: ConfigMap
metadata:
    name: mssql-config
data: 
    mssql.conf: |
     [EULA]
     accepteula = Y

     [network]
     tcpport = 1433

     [sqlagent]
     enabled = true

# Now run the command to create the object:
kubectl apply -f "D:\operator\mssqlconfig.yaml"

 

 

 

 

 

 

 

Step 2:

Now, create a secret object to store the sa account password and another secret to store dxe passkey and license key, as shown below:

 

 

 

 

# Create the secret to store the sa password for SQL Server.
kubectl create secret generic mssql --from-literal=MSSQL_SA_PASSWORD= "MyP@SSw0rd1!"

# Create the secret to store the license key for DH2i. You can get the developer license from -> https://dh2i.com/trial/. Replace the XXXX-XXXX-XXXX-XXXX with your License Key.
kubectl create secret generic dxe --from-literal=DX_PASSKEY="MyP@SSw0rd1!" --from-literal=DX_LICENSE=XXXX-XXXX-XXXX-XXXX

 

 

 

 

 

 

 

Step 3:

Install the private preview operator using the following commands:

 

 

 

 

# download the operator yaml from and save as v1beta2.yaml: 

dxoperator.dh2i.com/dxe/files/v1beta2.yaml

# Now install the operator using the command:
kubectl apply -f ”D:\operator\v1beta2.yaml”

 

 

 

 

 

 

 

Step 4:

Finally, provide the SQL Server and AG details like the name, limits and then watch the deployment happen:

 

 

 

 

# create a file called crd_v1.yaml ( you can name it as you like) and copy the content as is, if you want to deploy 3 containers with the instance name contoso-sql-0, contoso-sql-1 and contoso-sql2 and configure AG between the three instances

apiVersion: dh2i.com/v1beta2
kind: DxEnterpriseSqlAg
metadata:
    name: contoso-sql
spec:
    synchronousReplicas: 3
    asynchronousReplicas: 0
# ConfigurationOnlyReplicas are only allowed with availabilityGroupClusterType set to EXTERNAL
    configurationOnlyReplicas: 0
    availabilityGroupName: CONTOSOAG
# For a contained availability group, add the option CONTAINED
    availabilityGroupOptions: null
# Valid options are EXTERNAL (automatic failover) and NONE (no automatic failover)
    availabilityGroupClusterType: EXTERNAL
    createLoadBalancers: true
    template:
       spec:
        dxEnterpriseContainer:
           image: "docker.io/dh2i/dxe:beta"
           imagePullPolicy: Always
           acceptEula: true
           clusterSecret: dxe
           vhostName: VHOST1
           joinExistingCluster: false
  # QoS – guaranteed (uncomment to apply)
           resources:
             limits:
               memory: 1Gi
               cpu: '1'
# Configuration options for the required persistent volume claim forDxEnterprise
           volumeClaimConfiguration:
             storageClassName: null
             resources:
               requests:
                 storage: 1Gi
        mssqlServerContainer:
           image: "mcr.microsoft.com/mssql/server:latest"
           imagePullPolicy: Always
           mssqlSecret: mssql
           acceptEula: true
           mssqlPID: Developer
           mssqlConfigMap: mssql-config
# QoS – guaranteed (uncomment to apply)
           resources:
             limits:
               memory: 2Gi
               cpu: '2'
# Configuration options for the required persistent volume claim for SQL Server
           volumeClaimConfiguration:
             storageClassName: null
             resources:
               requests:
                 storage: 2Gi


# Now deploy the custom resource using the command: 
kubectl apply -f "D:\operator\crd_v1.yaml"

 

 

 

 

 

 

That’s it, now relax you’ve worked hard, wait for a few minutes, see the deployment happen, when it completes you see this:

Step 5:

Connect to your SQL Server primary instance to run the below T-SQL command to add a database “test” to your AG “CONTOSOAG”

 

 

 

 

#T-SQL Command on the primary, create a database and back up the same to add it to the AG.
create database test
go
backup database test to disk = '/var/opt/mssql/data/test.bak'

#Add the newly created database to the availability group "CONTOSOAG" 
ALTER AVAILABILITY GROUP [CONTOSOAG] ADD DATABASE [test];

 

 

 

 

 

 

 

Once this step is completed view the cluster using AG dashboard as shown below:

This is you regular AG deployed on Kubernetes with SQL Server containers based on your requirement!! Let us know what you think and how was your experience?