Skip to main content
Version: v23.0

SQL Server Availability Groups in Docker

Introduction​

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

info

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

Prerequisites​

  • Docker installed on 3 Linux machines (physical or virtual) with at least 8GB of RAM. For more information about installing Docker, view Docker documentation.

  • 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, visit the DH2i Store.

Create the Containers​

First you'll create the containers using the docker run command. 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 Docker 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​

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

  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​