Skip to main content
Version: v23.0

Supplemental Guide for Availability Groups in Kubernetes

Introduction​

This quick start guide describes how to create a SQL Server Availability Group running in Kubernetes on Azure using the Kubernetes CLI and DxEnterprise’s DxCLI commands.

Before continuing

This guide assumes that you have already completed previous sections of the SQL Server tutorial on Microsoft Docs. If you have not, please reference Microsoft's AKS SQL Server tutorial before continuing. Alternatively, use the full SQL Server Availability Groups for Kubernetes guide from DH2i.

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​

  • Completion of the previous sections of the SQL Server tutorial doc on Microsoft Docs. Reference the steps in that doc for instructions on deploying the Azure Kubernetes Service, creating the DxEnterprise + MSSQL container image, and deploying the containers to the AKS cluster.

  • 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 and tunnels enabled. A fully featured Developer Edition is available for free for non-production use. To purchase DxEnterprise software for production workloads, visit the DH2i Store.

Configure the Primary and Create the Availability Group​

  1. Get the credentials for the Kubernetes cluster.

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

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

    Example

    kubectl exec mssql-pri-0 -- dxcli activate-server AAAA-BBBB-CCCC-DDDD --accept-eula

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

    Example

    kubectl exec mssql-pri-0 -- dxcli cluster-add-vhost vhost1 *127.0.0.1 mssql-pri-0

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

    Example

    kubectl exec mssql-pri-0 -- dxcli encrypt-text p@ssw0rd

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

    Example

    kubectl exec mssql-pri-0 -- dxcli add-ags vhost1 ags1 "mssql-pri-0|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit|40001"

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

    Example

    kubectl exec mssql-pri-0 -- dxcli set-otpk

Join the Second Pod to the DxEnterprise Cluster​

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

    Example

    kubectl exec mssql-sec-0 -- dxcli activate-server AAAA-BBBB-CCCC-DDDD --accept-eula

  2. Join the second node to the DxEnterprise cluster using the command dxcli join-cluster-ex. Use the default NAT proxy of match.dh2i.com.

    Example

    kubectl exec mssql-sec-0 -- dxcli join-cluster-ex match.dh2i.com 331bc8bf-7096-99bc-05e5-0dd097393600 true

  3. Add the second node 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 node to an existing Vhost and availability group.

    Example

    kubectl exec mssql-sec-0 -- dxcli add-ags-node vhost1 ags1 "mssql-sec-0|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit|40002"

Join the Third Pod to the DxEnterprise Cluster​

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

    Example

    kubectl exec mssql-sec-1 -- dxcli activate-server AAAA-BBBB-CCCC-DDDD --accept-eula

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

    Example

    kubectl exec mssql-sec-1 -- dxcli join-cluster-ex match.dh2i.com 331bc8bf-7096-99bc-05e5-0dd097393600 true

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

    tip

    The third node is not required to be synchronous_commit.

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

Add Availability Group Database(s) and a Listener​

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

    kubectl exec mssql-pri-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.

    Example

    kubectl exec mssql-pri-0 -- dxcli add-ags-databases vhost1 ags1 db1

  3. Optional: Set an AG listener port using the command dxcli add-ags-listener.

    Example

    kubectl exec mssql-pri-0 -- dxcli add-ags-listener vhost1 ags1 44444

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

    Example

    kubectl exec mssql-pri-0 -- dxcli get-ags-detail vhost1 ags1

This guide is complete! You can return to the Microsoft tutorial here.

Additional Information​