Skip to main content
Version: v23.0

SQL Server Availability Groups in Docker Using Swarm

Introduction​

Docker Swarm is a container orchestration tool that provides an alternative way to deploy DxEMSSQL containers across multiple host machines. Docker Swarm is required for deployments where:

  1. NAT Matchmaking is unavailable, and

  2. More than one VM or physical machine will be used as a host for the containers.

This guide describes how to create and deploy a DxEnterprise + SQL Server (DxEMSSQL) container cluster in a Docker Swarm environment. You'll also use DxCLI to create and configure the 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​

  • Two or three Linux VMs or physical machines running Docker. For more information about installing Docker, view Docker documentation.

  • An internet connection to download the DxEMSSQL container image from Docker Hub. Alternatively, build and push the image to your own repository using the instructions in the Building DxEMSSQL guide.

  • 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 Swarm​

Docker Swarm uses overlay networks for cross-host container communications. The overlay and swarm must be created before deploying the containers.

  1. Choose a machine to be the swarm manager and initialize Docker Swarm. Save the join token output from this command.

    sudo docker swarm init

  2. On the same machine, create an overlay network.

    sudo docker network create -d overlay --attachable dxemssql-overlay

  3. SSH to the other Docker host(s) and use the output from step 1 to join the hosts into a swarm.

Create the Containers​

  1. Create the first container, making sure to use the overlay network in the run command. Also ensure that you replace dh2i/dxemssql with your registry and/or repository if you've 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 redeployments.

    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 --network dxemssql-overlay dh2i/dxemssql

  2. Repeat the command for the two additional containers on the other Docker host(s). 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 --network dxemssql-overlay 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 --network dxemssql-overlay 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 availability group.

Configure the First Container​

All DxCLI commands can be run from the host using the command format docker exec <container_name> dxcli <command>. This format will be used for DxCLI command examples. For this section, you'll activate the license and create the availability group on the primary container.

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

    tip

    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 cluster passkey using the command dxcli cluster-set-secret-ex.

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

  6. Enable hostname lookup in DxEnteprise's global settings using the command dxcli set-globalsetting.

    sudo docker exec dxemssql-0 dxcli set-globalsetting membername.lookup true

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 on another Docker host.

  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 name of the previous container as the target for the first parameter.

    sudo docker exec dxemssql-1 dxcli join-cluster-ex dxemssql-0 p@ssw0rd

  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 of the previous section.

    sudo docker exec dxemssql-2 dxcli join-cluster-ex dxemssql-0 p@ssw0rd

  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

References​