Skip to main content
Version: v22.0

SQL Server Docker Containers: How to Build a SQL Server Availability Group (AG) in Kubernetes Using DH2i DxEnterprise Smart High Availability Software

Abstract​

This whitepaper tackles the problem of SQL Server high availability and SQL Server clustering for SQL Server Docker containers. It describes how easy it is to build a SQL Server AG in Kubernetes using SQL Server Docker containers with the enhanced SQL Server clustering of DH2i's DxEnterprise Smart High Availability software to solve the challenge of provisioning new digital transformation business systems.

Audience​

This white paper is for IT professionals such as DBAs, database architects, engineers, and CIOs who are interested in learning how to build a SQL Server AG in Kubernetes using SQL Server Linux and SQL Server Docker containers in Azure Kubernetes to support the high availability and disaster recovery needs of new business applications.

Business Challenge​

A 2020 McKinsey global study found that as a result of the COVID-19 pandemic, companies have been pushed "over the technology tipping point." The research found that as digital adoption has ramped up in response to the pandemic-related changes in the business environment, enterprises have accelerated the digitization of their customer and supply-chain interactions—as well as their internal operations.

Companies had already started increasing digital transformation (DX) projects—replacing manual IT tasks with software to automate the test, configuration, and runtime processes—and for good reason. It's been recognized for decades that DX can not only dramatically improve IT and business capabilities, but it can also save money. As a result, those organizations that leverage DX can create and maintain a competitive advantage by:

  • Transforming the employee experience (EX) and customer experience (CX)
  • Increasing top-line growth
  • Contributing a substantial boost to the bottom line

But while many companies had already started to recognize the value of a DX approach, the pandemic introduced a new sense of corporate urgency in meeting goals related to digital adoption. Organizations that previously had the luxury of remaining reluctant about e-commerce were suddenly forced to accelerate their DX work. Projects that were planned to take years had to happen in only months or less. The recent Dell Digital Transformation Index 2020 survey showed that eight in ten organizations fast-tracked their DX projects in 2020.

These DX initiatives are in turn contributing to an explosion of software container use. The use of containers in production has increased to 92%, up from 84% last year, and up 300% from in 2016, according to Cloud Native Computing Foundation (CNCF) Cloud Native Survey 2020. Additionally, the survey showed that there's been steady growth in the number of containers that organizations run. Those using more than 5,000 containers hit 23% in 2020, up 109% from 11% in 2016. Those using more than 250 containers hit 61% in 2020, up from 57% in 2019.

These DX initiatives are also driving the adoption of stateful containers. Containers were originally designed to be entirely stateless and ephemeral. A container would spin up, do its job, and disappear, without leaving any record of what happened while it was running. However, as DX projects generally require moving applications to containers, companies have run into a problem: Many applications need to retain state, which creates a need for stateful containers such as databases. The survey results confirm this with 55% of respondents indicating that they use stateful containers in production, 12% are evaluating stateful containers, and 11% plan to use stateful containers in the next 12 months. Only 22% of respondents only use stateless containers.

Another important trend affecting DX projects are the data center deployment strategies for containers. According to the CNCF survey the public cloud remains the most popular data center approach for container deployment. It's used 64% of the time - a slight increase from 62% last year. Private cloud or on-premises usage showed a significant year over year increase. It grew to 52% from 45%. While Hybrid cloud usage showed a slight decrease from 38% to 36%, it remains an important deployment option.

A key challenge for organizations is how to provision new stateful containers, such as SQL Server Docker containers, that meet the tier 1 service-level agreements (SLA) for high availability and disaster recovery requirements of DX business systems. Many of these business systems will potentially involve managing thousands of stateful containers. These containers will need to be deployed fast and efficiently in public cloud, private cloud/on-premises and/or hybrid cloud datacenter environments.

Solution Overview​

In this paper, we expand on information that is available from DH2i, Microsoft and Azure. We provide a SQL Server AG use case that highlights the benefits of deploying a near-zero high availability and disaster recovery (HADR) architecture for SQL Server Docker containers in production. In addition, we explore the intersection of the enhanced SQL Server clustering capabilities of DH2i DxEnterprise (DxE) Smart High Availability software, 2019 SQL Server Docker container, and Azure Kubernetes Services (AKS). The use case that we present is designed to show how DevOps, Database Architects and others can easily create a SQL Server AG that uses the enhanced SQL Server clustering capabilities of DxE with SQL Server Docker containers and AKS. Using the enhanced SQL Server clustering capabilities of DxE with SQL Server Docker containers enables tier 1 high availability and disaster recovery (HADR) with failover automation across hybrid and multiple cloud environments.

Use Case Overview​

Our use case demonstrates the advantages of using the enhanced SQL Server clustering capabilities of DH2i DxEnterprise (DxE) Smart High Availability software, Microsoft 2019 SQL Server Docker containers and Azure Kubernetes Service (AKS) for a SQL Server AG that is hosted in Azure.

The use case will show how a SQL Server AG using the enhanced SQL Server clustering capabilities of DxE with SQL Server database Docker containers in AKS enable a near-zero RTO high availability and disaster recovery (HADR) architecture for SQL Server Docker container-based applications. With this solution, developers, database architects and others can easily take full advantage of SQL Server AG in Kubernetes to ensure SQL Server database downtime is near-zero with automatic failover across Azure availability zones.

In the use case, we create a Linux SQL Server AG using the enhanced SQL Server clustering capabilities of DxE with SQL Server Docker containers in AKS. Taking advantage of built-in Software Defined Perimeter (SDP) capabilities of DxE's enhanced SQL Server clustering, the SQL Server AG will be built without needing to use a private VPN or any other cumbersome network configuration. With DxE, SQL Server Docker containers and AKS we will easily and quickly provision the SQL Server AG with readable secondary replicas and an attached sample database. We will show how to access the primary SQL Server Docker container replica and readable secondary replicas and how easy it is to scale the secondary replica SQL Server Docker containers to support new workload requests. Lastly, we show application resilience by demonstrating fully automatic failover of the primary SQL Server Docker containers to replicas across availability zones.

Use Case Detail: SQL Server AG in AKS​

This guide describes how to create and deploy a DxEnterprise + SQL Server container image to an Azure Kubernetes Service (AKS) cluster for Availability Groups. Using this guide, the user will create a DxEnterprise + SQL Server container image and push the image to a repository, deploy and configure an AKS cluster and 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: Quorum Considerations for SQL Server Availability Groups.

Prerequisites​

  • A machine with the Azure and Kubernetes CLIs installed and logged into an Azure account. For information about installing the Azure CLI and logging in, view Microsoft documentation. To install the Kubernetes CLI, run the command az aks install-cli. Alternatively, the Azure Cloud Shell may be used.
  • Docker installed on a Linux VM and logged in to a Docker account with push and pull privileges. This can be the same VM that has the Azure and Kubernetes CLIs installed. For more information about installing Docker, view Docker documentation. Use docker login to log into a Docker account.
  • A valid repository for the image on a registry service such as Docker Hub. For more information about creating repositories on Docker Hub, view Docker documentation. This guide relies on Docker instructions, but it is perfectly acceptable to use another registry service such as Azure Container Registry.
  • A valid DxEnterprise license with availability group management features and tunnels enabled. A fully featured developer edition is available for free for non-production use at https://dh2i.com/dxenterprise-dxodyssey-developer-edition. To purchase DxEnterprise software for production workloads, visit https://dh2i.com/store/.

Prepare the Cluster​

This section describes how to create the container image, create the AKS cluster, and deploy the StatefulSet in Azure.

Create the Container Image​

This section describes how to create a custom Docker image that combines the SQL Server container image from Docker Hub with a customized DxEnterprise package meant specifically for containers.

  1. Copy the contents of the Dockerfile below to a file your current working directory.

    info

    This image is built from the mcr.microsoft.com/mssql/server:2019-latest image on Docker Hub. This image:

    1. Installs .NET Core 3.1 and net-tools. net-tools is not required for the container to run, but it may help with diagnosing network issues.

    2. Copies the latest DxEnterprise tarball from https://repos.dh2i.com/container/, unpacks it, and creates the necessary user permissions.

    3. Assigns the default user as mssql and sets the entrypoint to a pre-defined script.

    FROM mcr.microsoft.com/mssql/server:2019-latest
    USER root
    #Install dotnet and net-tools
    RUN apt-get update \
       && ACCEPT_EULA=Y apt-get upgrade -y \
       && apt-get install -y wget \
       && wget --no-dns-cache https://packages.microsoft.com/config/ubuntu/20.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb \
       && dpkg -i packages-microsoft-prod.deb \
       && apt-get update \
       && apt-get install -y dotnet-runtime-3.1 net-tools \
       && dpkg --purge packages-microsoft-prod \
       && apt-get purge -y wget \
       && apt-get clean \
       && rm packages-microsoft-prod.deb \
       && rm -rf /var/lib/apt/lists/*
    #Download and unpack DxE, setup permissions
    ADD https://repos.dh2i.com/container/ ./dxe.tgz
    RUN tar zxvf dxe.tgz && rm dxe.tgz \
       && chown -R mssql /var/opt/mssql \
       && chmod -R 777 /opt/dh2i /etc/dh2i
    #Finish setup
    EXPOSE 7979 7985
    ENV DX_HAS_MSSQLSERVER=1 \
        MSSQL_ENABLE_HADR=1
    USER mssql
    ENTRYPOINT ["/opt/dh2i/sbin/dxstart.sh"]

  2. Replace the <repository_name> variable in the command below the name of your registry, repository, and tag (i.e., dh2i/dxemssql:mytest), and run the command.

    docker build -t <repository_name> -f dxemssql.dockerfile ./

Push the Image to a Repository​

  1. Login to your Docker account on the local machine using docker login.

  2. Push the image to Docker Hub using the command below, replacing <repository_name> with the repository name you used in step 2 of the previous section.

    docker push <repository_name>

Create the Kubernetes Cluster​

  1. Create a resource group in Azure for the Kubernetes cluster.

    az group create -n <resource_group> -l <region>

  2. Create an Azure Kubernetes Service (AKS) Cluster in the resource group. For more information about CLI parameters, view Microsoft documentation.

    info

    You can utilize Azure availability zones in select regions by using the --zones parameter. Availability zones distribute your Kubernetes node pool(s) across Azure infrastructure within a given region for higher availability. For more information about Azure availability zones, see Microsoft Documentation

    az aks create -g <resource_group> -n <cluster_name> --disable-rbac --generate-ssh-keys --zones 1 2 3

  3. Obtain the access credentials for the AKS cluster.

    az aks get-credentials -g <resource_group> -n <cluster_name>

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: dxemssql
            image: <Docker_ID>/<repository>
            env:
            - name: ACCEPT_EULA
              value: "Y" 
            - name: MSSQL_ENABLE_HADR
              value: "1"
            - name: SA_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mssql
                  key: SA_PASSWORD
            volumeMounts:
            - name: dxe
              mountPath: "/etc/dh2i"
            - name: mssql
              mountPath: "/var/opt/mssql"
      volumeClaimTemplates:
      - metadata:
          name: dxe
        spec:
          accessModes:
          - ReadWriteOnce
          resources:
            requests:
              storage: 1Gi
      - metadata:
          name: mssql
        spec:
          accessModes:
          - ReadWriteOnce
          resources:
            requests:
              storage: 8Gi

  2. In the YAML, use sed or a text editor to insert your registry and repository into the container configuration. If you're using sed, replace the REGISTRY and REPOSITORY variables with the name of your registry and repository. For example:

    REGISTRY="dh2i"
    REPOSITORY="dxemssql"
    sed -i "s/image:.*/image: $REGISTRY\/$REPOSITORY/" dxemssql.yaml
    Private Registries

    If you're using a private registry, the dxemssql.yaml file needs to be modified to use those credentials. For more information about using private registries with Kubernetes, view Kubernetes documentation.

  3. Create a credential for the SQL instance.

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

  4. Apply the StatefulSet configuration.

    kubectl apply -f dxemssql.yaml

  5. 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 <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 dxemssql-0 -- dxcli activate-server AAAA-BBBB-CCCC-DDDD

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

    kubectl exec dxemssql-0 -- dxcli cluster-add-vhost vhost1 *127.0.0.1 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 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 dxemssql-0 -- dxcli add-ags vhost1 ags1 \
        "dxemssql-0|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit|50221"

  5. Set a One-Time PassKey (OTPK) using the command dxcli set-otpk. The output from this command will be used to join the other pods to the DxEnterprise cluster.

    kubectl exec dxemssql-0 -- dxcli set-otpk

  6. Set a cluster passkey using the command dxcli cluster-set-secret-ex. A passkey is required to remotely manage your DxEnterprise cluster.

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

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 dxemssql-1 -- dxcli activate-server AAAA-BBBB-CCCC-DDDD

  2. Join the second pod to the DxEnterprise cluster using the command dxcli join-cluster-ex. Use the default NAT proxy of match.dh2i.com and the OTPK generated in step 5 of the previous section.

    kubectl exec dxemssql-1 -- dxcli join-cluster-ex \
        match.dh2i.com XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX true

  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 dxemssql-1 -- dxcli add-ags-node vhost1 ags1 \
        "dxemssql-1|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit|50222"

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 dxemssql-2 -- dxcli activate-server AAAA-BBBB-CCCC-DDDD

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

    kubectl exec dxemssql-2 -- dxcli join-cluster-ex \
        match.dh2i.com XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX true

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

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

Add an Availability Group Database​

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

    kubectl exec 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 dxemssql-0 -- dxcli add-ags-databases vhost1 ags1 db1

  3. Verify the details of the availability group using the command dxcli get-ags-detail.

    kubectl exec dxemssql-0 -- dxcli get-ags-detail vhost1 ags1

Create Headless Services​

Headless services give your DxEMSSQL pods an alternative method of connecting each other locally for additional redundancy.

  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

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. Find which pod is the primary.

    kubectl exec dxemssql-0 -- dxcli get-ags-detail vhost1 ags1

  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.

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

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

Make Your Cluster Externally Accessible​

Load balancers can be used to access pods in a Kubernetes cluster. Kubernetes load balancers provide access to cluster resources via an externally-accessible IP address: you define the load balancer configuration in the YAML, and Kubernetes takes care of load balancing incoming connections for you.

  1. Set an AG listener port using the command dxcli add-ags-listener.
kubectl exec dxemssql-0 -- dxcli add-ags-listener vhost1 ags1 14033

  1. Copy the load balancer configuration to your local machine.

    #Example load balancer service
    #Access for SQL server, AG listener, and DxE management
    apiVersion: v1
    kind: Service
    metadata:
      name: dxemssql-cluster-lb
    spec:
      type: LoadBalancer
      selector:
        app: dxemssql
      ports:
      - name: sql
        protocol: TCP
        port: 1433
        targetPort: 1433
      - name: listener
        protocol: TCP
        port: 14033
        targetPort: 14033
      - name: dxe
        protocol: TCP
        port: 7979
        targetPort: 7979

  2. Provision a static IP.

    info

    When creating a Kubernetes load balancer service, the load balancer is assigned an IP address that only persists for the load balancers lifespan. This IP address will change if the service is deleted and recreated. Reference Microsoft documentation for further information about static IPs in Kubernetes. The steps below describe how to create and assign a persistent static IP.

    1. Create a Public IP address resource with a static allocation method and standard SKU using the Azure CLI. The public IP address should be assigned to the additional resource group Azure created during the initial Kubernetes cluster deployment. The name of this auto-generated resource group follows the pattern MC_<resource-group>_<cluster-name>_<region>. To list the name, run the command az group list | grep “MC_”. For example:

      az network public-ip create -g MC_examplegroup_examplecluster_westus2 -n exampleStaticIPName --sku Standard --allocation-method static

    2. Copy the ipAddress value from the JSON output.

    3. Use sed or a text editor to add the static IP to the service YAML file. Replace the <Public_IP> value with the IP address copied in step 2. Note in the command below that there are two spaces after the backslash.

      sed -i '/.*type:.*/a \  loadBalancerIP: <Public_IP>' service.yaml

  3. Apply the configuration.

    kubectl apply -f service.yaml

  4. List the service and note its public IP address.

    kubectl get service

  5. To verify connectivity, you can connect to the SQL instance (port 1433) or AG listener (port 14033) via SSMS/sqlcmd and the load balancer IP address.

    sqlcmd -S <ip_address> -U sa -P <sql_password>

Make Each Pod Externally Accessible​

Load balancers can also be assigned to a single pod. This requires some slight modification of the YAML example from the previous section. Each pod participating in the AG will require a unique service configuration like the one below, and will need a static IP defined.

  1. For each pod in the availability group, replace <lb_ip> and <pod_name> in the example below and save the file.

    #Example load balancer service
    #Access for SQL server, AG listener, and DxE management
    apiVersion: v1
    kind: Service
    metadata:
      #Unique name
      name: <pod_name>-lb
    spec:
      type: LoadBalancer
      loadBalancerIP: <lb_ip>
      selector:
        #Assign load balancer to a specific pod
        statefulset.kubernetes.io/pod-name: <pod_name>
      ports:
      - name: sql
        protocol: TCP
        port: 1433
        targetPort: 1433
      - name: listener
        protocol: TCP
        port: 14033
        targetPort: 14033
      - name: dxe
        protocol: TCP
        port: 7979
        targetPort: 7979
    tip

    Multiple YAML configurations can be saved in one file using a line of three dashes (---) between each configuration.

  2. Apply the service configuration.

    kubectl apply -f pod-service.yaml

Use Tunnels for Faster Connections to the Listener​

Connections to the AG listener via the Kubernetes load balancer may be slow due to how Kubernetes handles traffic to pods that do not have an active listener. To alleviate this issue, a DxEnterprise tunnel can used to redirect listener traffic from the secondaries to the primary to avoid timeouts. Run the dxcli add-tunnel command to add the tunnel to the Vhost.

kubectl exec dxemssql-0 -- dxcli add-tunnel listener true ".ACTIVE" "127.0.0.1:14033" ".INACTIVE,0.0.0.0:14033" vhost1
info

The add-tunnel command given above uses .ACTIVE and .INACTIVE designations to determine where to route traffic. In DxEnterprise, the .ACTIVE node is always the primary replica, and .INACTIVE will be every secondary replica in the Vhost. The command will create tunnel origins (listeners) on the secondaries on port 14033 that route traffic to the primary pod.

Configure Replicas for Read-Only Access​

Availability Group secondaries can be configured to allow read-only access to their databases using T-SQL.

  1. Verify the details of your service configuration before continuing. Obtain the IP addresses for each of the load balancers assigned to your pods.

    kubectl get service

  2. Copy the T-SQL below into a file.

    USE [master] 
    GO 
    --Only allow read-write connections in the primary role
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
        PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE)
    )
    GO
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
        PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE)
    )
    GO 
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
        PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE)
    )
    GO 
    --Allow read-only connections in the secondary role
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
    )
    GO
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
    )
    GO 
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
    )
    GO

  3. Copy the T-SQL to the /etc/dh2i folder on the primary pod.

    kubectl cp read-only-access.sql dxemssql-0:/etc/dh2i
    Which pod is my primary?

    If you're unsure which pod is your primary, run the command dxcli get-ags-detail and search for <replica server name> after <role_desc>.

    kubectl exec dxemssql-0 -- dxcli get-ags-detail vhost1 ags1 | grep PRIMARY

    If dxemssql-0 isn't the primary, you can fail your availability group back to dxemssql-0 using the command dxcli vhost-start-node.

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

  4. Execute the T-SQL, replacing <sql_password> with the SQL Server password.

    kubectl exec dxemssql-0 -- /opt/mssql-tools/bin/sqlcmd \
        -S localhost -U SA -P <sql_password> -i /etc/dh2i/read-only-access.sql

Verify Access​

  1. Copy the YAML configuration below to a file. This will create a mssql-tools pod within the Kubernetes cluster which you will use to access the AG. Alternatively, you can install sqlcmd on a remote machine outside the cluster.

    apiVersion: v1
    kind: Pod
    metadata:
      name: mssql-tools
    spec:
      containers:
      - name: sqlcmd
        image: mcr.microsoft.com/mssql-tools
        command: ["/bin/bash", "-c", "--"]
        args: ["while true; do sleep 30; done;"]
        imagePullPolicy: IfNotPresent
      restartPolicy: Always

  2. Apply the configuration.

    kubectl apply -f mssql-tools.yaml

  3. Create a database table on the primary using the mssql-tools pod. Replace <primary_lb_ip> with the IP address of your primary pod, and <sql_password> with the SQL Server password.

    kubectl exec mssql-tools -- /opt/mssql-tools/bin/sqlcmd \
        -S <primary_lb_ip> -U sa -P <sql_password> -Q "CREATE TABLE db1.dbo.table1 (column_1 int)"
    info

    If a database isn't available in the AG, create one using the instructions from the availability group database section.

  4. Now read the table contents from one of the secondaries, using the <secondary_lb_ip> instead.

    kubectl exec mssql-tools -- /opt/mssql-tools/bin/sqlcmd \
        -S <secondary_lb_ip> -U sa -P <sql_password> -K ReadOnly \
        -d db1 -Q "SELECT column_1 FROM db1.dbo.table1"
    sqlcmd parameters

    sqlcmd requires some additional parameters:

    • -K ReadOnly specifies read-only intent.
    • -d db1 issues a USE database_name statement for the connection.

  5. Finally, attempt to read from the primary using ReadOnly intent. This command should fail.

    kubectl exec mssql-tools -- /opt/mssql-tools/bin/sqlcmd \
        -S <primary_lb_ip> -U sa -P <sql_password> -K ReadOnly \
        -d db1 -Q "SELECT column_1 FROM db1.dbo.table1"

Configure Read-Only Routing with Load Balancing​

Now that your replicas are configured for read-only access, you can configure the availability group so listener connections with read-only intent are routed to the secondary replicas.

  1. Verify your listener port using the command dxcli get-ags-detail.

    kubectl exec dxemssql-0 -- dxcli get-ags-detail vhost1 ags1 | grep listener

  2. Copy the T-SQL below into a file, replacing <lb_ip> with the IP address of the load balancer associated with that pod.

    USE [master] 
    GO 
    --Update the read-only routing URLs
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
        SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://<lb_ip>:1433') 
    )
    GO 
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
        SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://<lb_ip>:1433') 
    )
    GO 
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
        SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://<lb_ip>:1433') 
    )
    GO
    --Update the read-only routing list with AG members in load-balanced configuration
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
        PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-1', 'dxemssql-2')))
    ) 
    GO 
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
        PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-0', 'dxemssql-2')))
    ) 
    GO 
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
        PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-0', 'dxemssql-1')))
    )
    GO
    routing priority

    The normal behavior of SQL Server is to route incoming read-only connections to the first available replica on the list, with no regard to load balancing. Adding an extra set of parentheses around the two secondary replicas configures SQL Server to load balance the two secondaries. This feature is included in the T-SQL example above.

  3. Copy the T-SQL to the /etc/dh2i folder on the primary pod.

    kubectl cp read-routing.sql dxemssql-0:/etc/dh2i

  4. Execute the T-SQL.

    kubectl exec dxemssql-0 -- /opt/mssql-tools/bin/sqlcmd \
        -S localhost -U SA -P <sql_password> -i /etc/dh2i/read-routing.sql

  5. Test a read-intent connection to the AG listener on dxemssql-0. This command should output the name of one of the secondaries.

    kubectl exec mssql-tools -- /opt/mssql-tools/bin/sqlcmd \
        -S <primary_lb_ip>,<ag_listener> -U sa -P <sql_password> -M \
        -K ReadOnly -d db1 -Q "SELECT column_1 FROM db1.dbo.table1; SELECT @@SERVERNAME"

Enable Read-Write Routing to the Primary​

In addition to routing read-only intent connections from the primary to the secondaries, you can also route read-write intent connections from the secondaries to the primary.

  1. Copy the T-SQL below to a file, replacing <lb_ip> with the IP address of the load balancer associated with that pod.

    USE [master] 
    GO 
    --Allow all connections in the secondary role instead of READ_ONLY
    --Read-write intent connections will be routed to the primary
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
        SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
    )
    GO
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
        SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
    )
    GO 
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
        SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
    )
    GO
    --Update the read-write routing URLs
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
        PRIMARY_ROLE (READ_WRITE_ROUTING_URL = 'TCP://<lb_ip>:1433')
    )
    GO
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
        PRIMARY_ROLE (READ_WRITE_ROUTING_URL = 'TCP://<lb_ip>:1433')
    )
    GO
    ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
        PRIMARY_ROLE (READ_WRITE_ROUTING_URL = 'TCP://<lb_ip>:1433')
    )
    GO

  2. Copy the T-SQL to the primary pod.

    kubectl cp write-routing.sql dxemssql-0:/etc/dh2i

  3. Execute the T-SQL.

    kubectl exec dxemssql-0 -- /opt/mssql-tools/bin/sqlcmd \
        -S localhost -U SA -P <sql_password> -i /etc/dh2i/write-routing.sql

  4. Test a read-write connection to the database on dxemssql-1 or dxemssql-2.

    kubectl exec mssql-tools -- /opt/mssql-tools/bin/sqlcmd \
        -S <secondary_lb_ip> -U sa -P <sql_password> -M -d db1 \
        -Q "CREATE TABLE db1.dbo.table2 (column_1 int); SELECT @@SERVERNAME"

Add Replicas to the Cluster​

  1. To scale a StatefulSet up, run the command kubectl scale along with the total quantity of replicas for the StatefulSet.

    kubectl scale --replicas=4 statefulset/dxemssql

  2. Wait for the pod status to change to ready.

    kubectl get pods

  3. Activate the DxEnterprise license for the fourth pod using the command dxcli activate-server.

    kubectl exec dxemssql-3 -- dxcli activate-server AAAA-BBBB-CCCC-DDDD

  4. Join the fourth pod to the DxEnterprise cluster using the command dxcli join-cluster-ex.

    kubectl exec dxemssql-3 -- dxcli join-cluster-ex \
        match.dh2i.com XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX true
    tip

    If the join command fails, try setting an OTPK for the DxEnterprise cluster again using the command dxcli set-otpk and use that OTPK to join the cluster.

    kubectl exec dxemssql-0 -- dxcli set-otpk

  5. Add the fourth pod to the existing availability group using the command dxcli add-ags-node.

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

Update Network Configuration​

After you add the pod to the cluster, you might need to update one or more service configurations.

Add to Headless Service File​

Headless services allow DNS resolution in situations where NAT matchmaking may be unavailable. An initial configuration is provided in the headless service section, which can be expanded upon with the following YAML:

#Headless services for local connections/resolution
apiVersion: v1
kind: Service
metadata:
  name: dxemssql-3
spec:
  clusterIP: None
  selector:
    statefulset.kubernetes.io/pod-name: dxemssql-3
  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
tip

Use 3 dashes (---) to separate service definitions.

Paste that YAML into the headless-services.yaml file. Then run kubectl apply to update the service configuration.

kubectl apply -f headless-services.yaml
Add the Pod to the Load Balancer Configuration​

If each pod in the cluster is externally accessible with an IP address, you'll need to add the new pod to the service definition to give it a public IP too.

info

App-wide load balancers (i.e., selector: app: dxemssql) do not need to be updated to route to the new pod: Kubernetes should route to them automatically. However, any services that use a pod selector (statefulset.kubernetes.io/pod-name) will need the new pod added to the configuration file.

Follow the instructions in the networking guide to make the new pod externally accessible with its own IP address.

Update Read-Only & Read-Write Replicas​

Please review the configuring replicas section before continuing. Also, verify the details of your service configuration.

Obtain the IP addresses for each of the load balancers assigned to your pods.

kubectl get service
Update Read-Only Access​

Adding read-only/read-write access to the replica follows the same steps in the Configure Replicas for Read-Only Access section. The only modification required is to use the T-SQL below instead of the T-SQL supplied in that section.

Copy the T-SQL below into a file. Use the steps from the remainder of the section to apply the configuration.

USE [master] 
GO 
--Only allow read-write connections in the primary role
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-3' WITH (
    PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE)
)
GO
--Allow read-only connections in the secondary role
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-3' WITH (
    SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
)
GO
Update Read-Only Routing​

In addition to adding a read-only routing URL, the read-only routing list must be updated for each AG member to reflect the new membership.

Copy the T-SQL below into a file, replacing <lb_ip> with the IP address of the load balancer associated with that pod. Use the steps from the remainder of the configure read-only routing section to apply the configuration.

USE [master] 
GO 
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-3' WITH (
    SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 'TCP://<lb_ip>:1433') 
)
GO
--Update the read-only routing list with AG members in load-balanced configuration
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-0' WITH (
    PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-1', 'dxemssql-2', 'dxemssql-3')))
) 
GO 
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-1' WITH (
    PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-0', 'dxemssql-2', 'dxemssql-3')))
) 
GO 
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-2' WITH (
    PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-0', 'dxemssql-1', 'dxemssql-3')))
)
GO 
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-3' WITH (
    PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('dxemssql-0', 'dxemssql-1', 'dxemssql-2')))
)
GO
Update Read-Write Routing​

Adding read-write routing to the replica follows the same steps in the read-write routing section. The only modification required is to use the T-SQL below instead of the T-SQL supplied in that section.

Copy the T-SQL below to a file, replacing <lb_ip> with the IP address of the load balancer associated with that pod. Use the steps from the remainder of the section to apply the new read-write routing configuration.

USE [master] 
GO 
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-3' WITH (
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
)
GO
--Update the read-write routing URLs
ALTER AVAILABILITY GROUP AGS1 MODIFY REPLICA ON N'dxemssql-3' WITH (
    PRIMARY_ROLE (READ_WRITE_ROUTING_URL = 'TCP://<lb_ip>:1433')
)
GO

Remove Replicas From the Cluster​

If you plan to keep save your volumes for the AG member, you only need to run the kubectl scale command. Otherwise, if you choose to scale down your replica and delete your volumes for that pod, you should deactivate the DxEnterprise license beforehand.

  1. Run the command dxcli deactivate-server to deactivate the license for that pod.

    kubectl exec dxemssql-0 -- dxcli deactivate-server dxemssql-3

  2. Scale the StatefulSet down using kubectl scale.

    kubectl scale --replicas=3 statefulset/dxemssql

  3. Delete the persistent volume claims for the pod.

    kubectl delete pvc dxe-dxemssql-3
    kubectl delete pvc mssql-dxemssql-3
    danger

    Deleting volumes can result in unintended data loss.

Conclusion​

Many organizations want to transform their business operations by deploying a new class of applications based on stateful containers such as such as SQL Server Docker containers in Kubernetes. Their challenge is how do they ensure these to-be-deployed systems meet their tier 1 service-level agreements (SLA) for high availability and disaster recovery requirements.

In our use case we showed how a database architect can setup a SQL Server AG in Kubernetes using the enhanced SQL Server clustering capabilities of DH2i DxEnterprise (DxE) Smart High Availability software. Using DxE with SQL Server Docker containers in Azure Kubernetes Service (AKS) the database architect can achieve near-zero database-level high availability and business continuity with SQL Server Docker container-based applications.

Additionally, in our use case we showed how with DxE, SQL Server Docker containers and AKS a database architect can easily and quickly provision and scale the SQL Server AG with readable secondary replicas to support new workload requests. Lastly, we showed application resilience by demonstrating fully automatic near-zero failover of the primary database replica across availability zones.