Skip to main content
Version: v26.0-RC

SQL Server Availability Groups in Containers

Introduction​

This guide describes how to create and deploy a DxEnterprise + SQL Server (DxEMSSQL) container cluster and use DxCLI to create an availability group.

info

This guide covers the deployment of three Availability Group (AG) replicas. When deploying availability groups, please be aware of Quorum Considerations for SQL Server Availability Groups.

Docker and Podman are largely interchangeable for the container commands shown in this guide. Docker commands assume the default docker.io registry. When using Podman, container image names must be fully qualified with the registry name. For example:

podman run docker.io/dh2i/dxe

Prerequisites​

  • A supported container runtime installed on three Linux machines (physical or virtual) with at least 8 GB of RAM each.
    Supported runtimes: Docker or Podman.
    Refer to the official Docker or Podman documentation for installation instructions.

  • A DxEMSSQL image available locally or on a repository. To create the image, follow the relevant quick start guide. Alternatively, DH2i provides the dh2i/dxemssql image on Docker Hub for testing and non-production use only.

  • A valid DxEnterprise license with availability group management features and tunnels enabled. A fully featured Developer Edition is available free for non-production use. To purchase DxEnterprise software for production workloads, please contact us.

Create the Containers​

First you'll create the containers using your container runtime. DH2i's NAT Matchmaking allows containers on disparate networks to connect to each other.

  1. Create the first container. Ensure that you replace dh2i/dxemssql with your registry and/or repository if you built and pushed your own image.

    caution

    it is strongly recommended to create volumes for the /etc/dh2i and /var/opt/mssql folders to persist data between container re-deployments.

    sudo docker run -d -e ACCEPT_EULA=Y -e SA_PASSWORD=p@ssw0rd \
      -v dxe-0:/etc/dh2i \
      -v mssql-0:/var/opt/mssql \
      --name dxemssql-0 \
      -h dxemssql-0 \
      dh2i/dxemssql

  2. Repeat the command for the two additional containers on different hosts. Increment the values for --name, -h, and both volumes (-v):

    sudo docker run -d -e ACCEPT_EULA=Y -e SA_PASSWORD=p@ssw0rd \
      -v dxe-1:/etc/dh2i \
      -v mssql-1:/var/opt/mssql \
      --name dxemssql-1 \
      -h dxemssql-1 \
      dh2i/dxemssql

    sudo docker run -d -e ACCEPT_EULA=Y -e SA_PASSWORD=p@ssw0rd \
      -v dxe-2:/etc/dh2i \
      -v mssql-2:/var/opt/mssql \
      --name dxemssql-2 \
      -h dxemssql-2 \
      dh2i/dxemssql

Create the Availability Group​

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

Configure the First Container​

DxCLI commands can be run by using the container runtime's exec command to invoke DxCLI within the container. This method will be used in the examples below.

  1. Activate the DxEnterprise license on the first container using the command dxcli activate-server.

    info

    If this command fails, retry a few more times. Depending on system resources, it may take 30 or more seconds for DxEnterprise and SQL Server to complete their startup.

    sudo docker exec 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.

    sudo docker exec 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.

    sudo docker 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.

    sudo docker exec dxemssql-0 dxcli add-ags vhost1 ags1 "dxemssql-0|mssqlserver|sa|V1DUmkSp6vNmyLDbzT1+xg==|5022|synchronous_commit|0"

  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 containers to the DxEnterprise cluster.

    sudo docker exec dxemssql-0 dxcli set-otpk

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

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

Join the Second Container to the DxEnterprise Cluster​

Now that the availability group is created on the primary, you can add a second container to the cluster.

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

    sudo docker exec dxemssql-1 dxcli activate-server AAAA-BBBB-CCCC-DDDD --accept-eula

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

    sudo docker exec dxemssql-1 dxcli join-cluster-ex match.dh2i.com 331bc8bf-7096-99bc-05e5-0dd097393600 true

  3. Add the second container 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.

    sudo docker exec dxemssql-1 dxcli add-ags-node vhost1 ags1 "dxemssql-1|mssqlserver|sa|V1DUmkSp6vNmyLDbzT1+xg==|5022|synchronous_commit|0"

Join the Third Container to the DxEnterprise Cluster​

Finally, join the third container into the cluster.

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

    sudo docker exec dxemssql-2 dxcli activate-server AAAA-BBBB-CCCC-DDDD --accept-eula

  2. Join the third container to the DxEnterprise cluster using the command dxcli join-cluster-ex with the same parameters from step 2.

    sudo docker exec dxemssql-2 dxcli join-cluster-ex match.dh2i.com 331bc8bf-7096-99bc-05e5-0dd097393600 true

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

    sudo docker exec dxemssql-2 dxcli add-ags-node vhost1 ags1 "dxemssql-2|mssqlserver|sa|V1DUmkSp6vNmyLDbzT1+xg==|5022|synchronous_commit|0"

Add the Availability Group Database(s)​

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

    sudo docker 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.

    sudo docker 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.

    sudo docker exec dxemssql-0 dxcli get-ags-detail vhost1 ags1

Additional Information​