Skip to main content
Version: v23.0

Deploy SQL Server Availability Groups in Kubernetes

Introduction​

This guide describes how to create a SQL Server availability group in a Kubernetes or OpenShift cluster using DxEnterprise. Using this guide, you will deploy a StatefulSet and use DxEnterprise to create and configure the availability group.

info

This guide covers the deployment of three AG replicas. When deploying availability groups, please consider the Microsoft SQL Server quorum requirements for automatic failover described in this KB:

Prerequisites​

  • A deployed Kubernetes or OpenShift cluster. References for creating a cluster on various cloud platforms or locally can be found in this KB article.

    • The Kubernetes or OpenShift CLI available and connected to your cluster.
    tip

    Rancher by SUSE uses the standard Kubernetes toolchain (such as the kubectl command). Follow the Kubernetes instructions if you are using Rancher.

  • A valid DxEnterprise license with availability group management features enabled. A fully featured Developer Edition is available free for non-production use. To purchase DxEnterprise software for production workloads, visit the DH2i Store.

Create Headless Services​

Headless services allow your pods to connect to one another using hostnames.

  1. Copy the YAML below to a file.

    #Headless services for local connections/resolution
    apiVersion: v1
    kind: Service
    metadata:
      name: dxemssql-0
    spec:
      clusterIP: None
      selector:
        statefulset.kubernetes.io/pod-name: dxemssql-0
      ports:
      - name: dxl
        protocol: TCP
        port: 7979
      - name: dxc-tcp
        protocol: TCP
        port: 7980
      - name: dxc-udp
        protocol: UDP
        port: 7981
      - name: sql
        protocol: TCP
        port: 1433
      - name: listener
        protocol: TCP
        port: 14033
    ---
    apiVersion: v1
    kind: Service
    metadata:
      name: dxemssql-1
    spec:
      clusterIP: None
      selector:
        statefulset.kubernetes.io/pod-name: dxemssql-1
      ports:
      - name: dxl
        protocol: TCP
        port: 7979
      - name: dxc-tcp
        protocol: TCP
        port: 7980
      - name: dxc-udp
        protocol: UDP
        port: 7981
      - name: sql
        protocol: TCP
        port: 1433
      - name: listener
        protocol: TCP
        port: 14033
    ---
    apiVersion: v1
    kind: Service
    metadata:
      name: dxemssql-2
    spec:
      clusterIP: None
      selector:
        statefulset.kubernetes.io/pod-name: dxemssql-2
      ports:
      - name: dxl
        protocol: TCP
        port: 7979
      - name: dxc-tcp
        protocol: TCP
        port: 7980
      - name: dxc-udp
        protocol: UDP
        port: 7981
      - name: sql
        protocol: TCP
        port: 1433
      - name: listener
        protocol: TCP
        port: 14033

  2. Apply the configuration.

    kubectl apply -f headless-services.yaml

Create the StatefulSet​

  1. Write the StatefulSet YAML configuration below to a file.

    #DxEnterprise + MSSQL StatefulSet 
    apiVersion: apps/v1
    kind: StatefulSet
    metadata:
      name: dxemssql
    spec:
      serviceName: "dxemssql"
      replicas: 3
      selector:
        matchLabels:
          app: dxemssql
      template:
        metadata:
          labels:
            app: dxemssql
        spec:
          securityContext:
            fsGroup: 10001
          containers:
          - name: sql
            image: mcr.microsoft.com/mssql/server:2022-latest
            env:
            - name: ACCEPT_EULA
              value: "Y"
            - name: MSSQL_ENABLE_HADR
              value: "1"
            - name: MSSQL_SA_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mssql
                  key: MSSQL_SA_PASSWORD
            volumeMounts:
            - name: mssql
              mountPath: "/var/opt/mssql"
          - name: dxe
            image: docker.io/dh2i/dxe
            env:
            - name: MSSQL_SA_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mssql
                  key: MSSQL_SA_PASSWORD
            volumeMounts:
            - name: dxe
              mountPath: "/etc/dh2i"
      volumeClaimTemplates:
      - metadata:
          name: dxe
        spec:
          accessModes:
          - ReadWriteOnce
          resources:
            requests:
              storage: 1Gi
      - metadata:
          name: mssql
        spec:
          accessModes:
          - ReadWriteOnce
          resources:
            requests:
              storage: 1Gi
    info

    This StatefulSet configuration creates 3 DxEMSSQL replicas that utilize persistent volume claims to store their data. Each pod in this StatefulSet is comprised of two containers: a SQL Server container and a DxEnterprise container. These containers are started separately from one another in a "sidecar" configuration, but DxEnterprise manages the availability group replica in the SQL container.

  2. Create a credential for the SQL instance.

    kubectl create secret generic mssql --from-literal=MSSQL_SA_PASSWORD="<password>"

  3. Apply the StatefulSet configuration.

    kubectl apply -f dxemssql.yaml

  4. Check the status of the pods using one or both of the commands below. Proceed to the next section after the first pod’s status updates to running.

    kubectl get pods
    kubectl describe pods

Create the Availability Group​

This section describes how to start the availability group on the primary, then join the two secondary Kubernetes pods to the DxEnterprise cluster and the availability group.

Configure the Primary Pod and Add the Availability Group​

All DxCLI commands can be run from outside the container using the command format kubectl exec -c <container_name> <pod_name> -- dxcli <command>. This format will be used for all of the subsequent DxCLI command examples.

  1. Activate the DxEnterprise license using the command dxcli activate-sever.

    kubectl exec -c dxe dxemssql-0 -- dxcli activate-server AAAA-BBBB-CCCC-DDDD --accept-eula

  2. Add a Vhost to the cluster using the command dxcli cluster-add-vhost.

    kubectl exec -c dxe dxemssql-0 -- dxcli cluster-add-vhost vhost1 "" dxemssql-0
    tip

    If you are using PowerShell, place the empty double-quotes within single-quotes:

    kubectl exec -c dxe dxemssql-0 -- dxcli cluster-add-vhost vhost1 '""' dxemssql-0

  3. Encrypt the SQL Server sysadmin password for DxEnterprise using the command dxcli encrypt-text. The encrypted password will be used to create the availability group in the next step.

    kubectl exec -c dxe dxemssql-0 -- dxcli encrypt-text p@ssw0rd

  4. Add an availability group to the Vhost using the command dxcli add-ags. The SQL Server sysadmin password must be encrypted using the command dxcli encrypt-text from the previous step, replacing the encrypted password in the example below.

    kubectl exec -c dxe dxemssql-0 -- dxcli add-ags vhost1 ags1 "dxemssql-0|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit|0"

  5. Set a cluster passkey using the command dxcli cluster-set-secret-ex.

    kubectl exec -c dxe dxemssql-0 -- dxcli cluster-set-secret-ex p@ssw0rd

  6. Enable hostname lookup in DxEnteprise's global settings using the command dxcli set-globalsetting.

    kubectl exec -c dxe dxemssql-0 -- dxcli set-globalsetting membername.lookup true

Join the Second Pod to the DxEnterprise Cluster​

  1. Activate the DxEnterprise license for the second pod using the command dxcli activate-server.

    kubectl exec -c dxe dxemssql-1 -- dxcli activate-server AAAA-BBBB-CCCC-DDDD --accept-eula

  2. Join the second pod to the DxEnterprise cluster using the command dxcli join-cluster-ex.

    kubectl exec -c dxe dxemssql-1 -- dxcli join-cluster-ex dxemssql-0 p@ssw0rd

  3. Add the second pod to the existing availability group using the command dxcli add-ags-node. The SQL Server sysadmin password must be encrypted using the command dxcli encrypt-text.

    info

    The commands dxcli add-ags and dxcli add-ags-node take identical parameter sets. The only parameters which must be unique to each command are the node_name and the tunnel_port.

    The functionality difference between the commands is that add-ags creates the availability group under a Vhost, while add-ags-node adds a pod to an existing Vhost and availability group.

    kubectl exec -c dxe dxemssql-1 -- dxcli add-ags-node vhost1 ags1 "dxemssql-1|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit"

Join the Third Pod to the DxEnterprise Cluster​

  1. Activate the DxEnterprise license for the third pod using the command dxcli activate-server.

    kubectl exec -c dxe dxemssql-2 -- dxcli activate-server AAAA-BBBB-CCCC-DDDD --accept-eula

  2. Join the third pod to the DxEnterprise cluster using the command dxcli join-cluster-ex.

    kubectl exec -c dxe dxemssql-2 -- dxcli join-cluster-ex dxemssql-0 p@ssw0rd

  3. Add the third pod to the existing availability group using the command dxcli add-ags-node.

    kubectl exec -c dxe dxemssql-2 -- dxcli add-ags-node vhost1 ags1 "dxemssql-2|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit"

Add an Availability Group Database​

  1. If a database is not already available, create it using sqlcmd on the primary.

    kubectl exec -c sql dxemssql-0 -- /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P p@ssw0rd -Q "create database db1"

  2. Add databases to the availability group from the primary using the command dxcli add-ags-databases.

    kubectl exec -c dxe dxemssql-0 -- dxcli add-ags-databases vhost1 ags1 db1

  3. Verify the details of the availability group using the command dxcli get-ags-detail. The XML output can be piped (redirected) to the command dxcli format-xml to make it easier to read.

    kubectl exec -c dxe dxemssql-0 -- dxcli get-ags-detail vhost1 ags1 | kubectl exec -c dxe dxemssql-0 -- dxcli format-xml

Optional: Test Failover​

To verify that your AG is working correctly, you can simulate a failover on the primary.

  1. Verify the state of the AG before failover. To do this, run the command dxcli get-ags-detail. Look for the status of the database for each AG member in the output details and find whichever pod is the primary.

    kubectl exec -c dxe dxemssql-0 -- dxcli get-ags-detail vhost1 ags1 | kubectl exec -c dxe dxemssql-0 -- dxcli format-xml

  2. Delete the primary pod.

    kubectl delete pod <PRIMARY_POD>

  3. Wait a few seconds, then run dxcli get-ags-detail again to check the status of the AG. Your database should be active and synchronized on one of your other pods. After a few minutes, the deleted pod should automatically rejoin the cluster and synchronize with the availability group.

    tip

    You can also test that your AG is still externally accessible via SSMS or sqlcmd using the instructions in the External Networking Guide for Kubernetes.

  4. After the pod rejoins the cluster and synchronizes, you can set its role back to primary by running the command dxcli vhost-start-node

    kubectl exec -c dxe dxemssql-0 -- dxcli vhost-start-node vhost1 dxemssql-0

Next Steps​

The AG deployment is complete! You can add to your deployment by creating load balancers and customizing AG settings to suit your needs. More information can be found in the following guides:

Additional Information​