Skip to main content
Version: v23.0

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

Use Case Detail: SQL Server AG in AKS​

This guide provides step-by-step instructions for setting up a highly available and scalable SQL Server solution in Azure. Specifically, the guide will walk you through the process of deploying a DxEnterprise and SQL Server sidecar StatefulSet in an Azure Kubernetes Service (AKS) cluster for Availability Groups. Additionally, this guide will cover the configuration of StatefulSet networking and the addition of SQL Server routing rules, ensuring that your solution is always up and running when you need it. By following the instructions in this guide, you'll be able to deploy a highly available and scalable SQL Server solution on Azure, leveraging the power of Kubernetes and DxEnterprise, and ensuring that your critical applications are always up and running.

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.
  • A valid DxEnterprise license with availability group management features. A fully featured developer edition is available for free for non-production use at https://dh2i.com/trial. To purchase DxEnterprise software for production workloads, please submit a contact request at https://dh2i.com/about-us/contact/.

Prepare the Cluster​

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

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 Headless Services​

Headless services in Kubernetes are a way to provide network identities to a set of stateful pods without using a load balancer. They allow direct communication to specific pods within a service by providing unique DNS entries for each pod, which is useful for applications that require direct access to individual pods.

  1. Download the headless service YAML from Github.

    curl -Lo headless-service.yaml https://github.com/dh2i/samples/blob/main/whitepapers/dxemssql/yaml/headless-services.yaml

  2. Apply the configuration.

    kubectl apply -f headless-services.yaml

Create the StatefulSet​

  1. Download the StatefulSet YAML configuration from Github.

    curl -Lo dxemssql.yaml https://raw.githubusercontent.com/dh2i/samples/main/whitepapers/dxemssql/yaml/dxemssql.yaml
    info

    The following StatefulSet configuration sets up 3 DxEMSSQL replicas that rely on persistent volume claims for data storage. Each of the pods within this StatefulSet includes two separate containers: one for SQL Server and another for DxEnterprise. These two containers start separately from each other in what is known as a "sidecar" configuration. However, even though they are separate, DxEnterprise still manages the availability group replica within 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

  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"

  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 DxEnterprise'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 parameter which must be unique to each command is the node_name.

    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

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.

  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

Make Your Cluster Externally Accessible​

In a Kubernetes cluster, load balancers can help you access the various pods that make up your applications. With Kubernetes load balancers, you can define the configuration you want to use in a YAML file, and then Kubernetes will handle the rest of the work of load balancing incoming connections for you. This is done using an externally-accessible IP address that provides access to the various resources in your cluster.

  1. Set an AG listener port using the command dxcli add-ags-listener.

    kubectl exec -c dxemssql-0 -- dxcli add-ags-listener vhost1 ags1 14033

  2. Download the cluster load balancer YAML configuration from Github.

    curl -Lo cluster-lb.yaml https://raw.githubusercontent.com/dh2i/samples/main/whitepapers/dxemssql/yaml/cluster-lb.yaml

  3. Provision a static IP.

    info

    When you create a Kubernetes load balancer service, the load balancer will be assigned an IP address that is only valid for the duration of the load balancer's lifespan. If the load balancer service is deleted and then recreated, the IP address will also change. Reference Microsoft documentation for further information about static IPs in Kubernetes. The steps below describe how to create and assign a 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 a text editor to add the static IP to the service YAML file. Replace the <static_ip> value for the loadBalancerIP with the IP address from step 3.

  4. Apply the configuration.

    kubectl apply -f cluster-lb.yaml

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

    kubectl get service

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

Assigning a load balancer to a single pod is a useful way to enable external network access via a public IP address. With this approach, you can easily provide external access to a single pod, which can be useful for providing access to a specific service.

This requires a slight modification of the YAML example from the previous section, but the instructions are otherwise the same. Each pod participating in the AG will require a unique service configuration like this one from Github.

tip

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

Use Tunnels for Faster Connections to the Listener​

If you're using a Kubernetes load balancer to connect to an availability group listener, you may experience slow connectivity. This is because Kubernetes uses a round-robin load balancer to distribute network traffic to the pods, and if a pod doesn't have an active TCP listener, it can cause delays. To solve this problem, you can use a DxEnterprise tunnel to redirect listener traffic from the secondary replicas or nodes to the primary replica or node. This will prevent timeouts and improve connectivity within the Kubernetes cluster. Run the dxcli add-tunnel command to add a tunnel to the Vhost.

info

Your license must have tunnels enabled to run this command. See the prerequisites section for more information.

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

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. Download the T-SQL example from Github.

    curl -Lo read-only-access.sql https://raw.githubusercontent.com/dh2i/samples/main/whitepapers/dxemssql/tsql/read-only-access.sql

  3. Copy the T-SQL to the /var/opt/mssql folder on the primary pod.

    kubectl cp read-only-access.sql dxemssql-0:/var/opt/mssql -c sql
    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 -c dxe 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 -c dxe dxemssql-0 -- dxcli vhost-start-node vhost1 dxemssql-0

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

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

Verify Access​

  1. Download the example mssql-tools YAML configuration from Github. 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.

    curl -Lo mssql-tools.yaml https://raw.githubusercontent.com/dh2i/samples/main/whitepapers/dxemssql/yaml/mssql-tools.yaml

  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)"

  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​

With the replicas now configured for read-only access, you can set up the availability group to route listener connections with read-only intent to the secondary replicas.

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

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

  2. Download the T-SQL example from Github, replacing <lb_ip> with the IP address of the load balancer associated with that pod.

    curl -Lo read-routing.sql https://raw.githubusercontent.com/dh2i/samples/main/whitepapers/dxemssql/tsql/read-routing.sql
    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.

  3. Copy the T-SQL to the /var/opt/mssql folder on the primary pod.

    kubectl cp read-routing.sql dxemssql-0:/var/opt/mssql -c sql

  4. Execute the T-SQL.

    kubectl exec -c sql dxemssql-0 -- /opt/mssql-tools/bin/sqlcmd \
        -S localhost -U SA -P <sql_password> -i /var/opt/mssql/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​

Aside from routing read-only intent connections from the primary to the secondaries, you can also configure the availability group to route read-write intent connections from the secondaries to the primary.

  1. Download the T-SQL example from Github, replacing <lb_ip> with the IP address of the load balancer associated with that pod.

    curl -Lo write-routing.sql https://raw.githubusercontent.com/dh2i/samples/main/whitepapers/dxemssql/tsql/write-routing.sql

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

    kubectl cp write-routing.sql dxemssql-0:/var/opt/mssql -c sql

  3. Execute the T-SQL.

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

  4. Test a read-write connection to the SQL listener 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"

Scale the StatefulSet​

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 services section, which can be expanded upon with the YAML example from Github.

curl -Lo new-pod-headless-service.yaml https://github.com/dh2i/samples/blob/main/whitepapers/dxemssql/yaml/new-pod-headless-service.yaml

Run kubectl apply to update the service configuration.

kubectl apply -f new-pod-headless-service.yaml

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 -c dxe dxemssql-3 -- dxcli activate-server AAAA-BBBB-CCCC-DDDD --accept-eula

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

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

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

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

After you add the pod to the cluster, you will need to add a load balancer service for the pod.

Add the Pod to the Load Balancer Configuration​

Follow the instructions in the networking section for making the new pod externally accessible with its own IP address.

Update Read-Only & Read-Write Replicas​

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.

Download the T-SQL example from Github. Use the steps from the remainder of the section to apply the configuration, only exchanging the file names.

curl -Lo update-read-only.sql https://raw.githubusercontent.com/dh2i/samples/main/whitepapers/dxemssql/tsql/update-read-only.sql

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.

Download the T-SQL example from Github, 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 with Load Balancing section to apply the configuration.

curl -Lo update-read-routing.sql https://raw.githubusercontent.com/dh2i/samples/main/whitepapers/dxemssql/tsql/update-read-routing.sql

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.

Download the T-SQL example from Github, 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.

curl -Lo update-write-routing.sql https://raw.githubusercontent.com/dh2i/samples/main/whitepapers/dxemssql/tsql/update-write-routing.sql

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