Skip to main content
Version: v23.0

SQL Server Availability Groups in Docker Using Host Networking

Introduction​

Docker host networking allows you to deploy your containers on the host's network interface instead of using a Docker network adapter. Host networking is required for deployments where:

  1. NAT Matchmaking is unavailable,

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

  3. The containers do not belong to a Docker Swarm with an overlay network.

This guide describes how to create an availability group using Docker host networking for the containers. Using this guide, you will create and configure a container on the first Docker host, then join the two other containers to the cluster to form an availability group.

info

Multiple DxEnterprise containers can run on the same machine using host networking if DxEnterprise's and SQL Server's listening ports are changed per container. Steps for accomplishing this are given below.

Docker Swarm is an alternative to host networking that allows Docker containers to connect to each other with an overlay network instead of using the Docker host's network. Swarm significantly simplifies cluster deployments when using a single host for multiple containers. For more information about using Docker Swarm with DxEnterprise, see the Docker Swarm guide.

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, or the image built and pushed to a public or private 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 for free for non-production use. To purchase DxEnterprise software for production workloads, visit the DH2i Store.

Create and Configure the Primary Container on the First Host​

In this section you will create a container on the first VM and configure it. This container will function as your AG's primary replica.

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.

  1. Create the first container using the docker run command. 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 --network=host dh2i/dxemssql

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

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

    If this command fails with "Connection refused", 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.

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

  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.

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

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

  6. Set a cluster passkey using the command dxcli cluster-set-secret-ex. The passkey you use in this command will be used by the other containers to join the cluster.

     sudo docker exec dxemssql-0 dxcli cluster-set-secret-ex PassW0rd

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

  8. Obtain the IP address of the host machine. In default configurations, this is the eth0 interface address listed in the ip -f inet addr command.

Create and Configure the Secondaries​

In this section, you'll create the two secondary containers and join them into the availability group. The configuration in this section will depend on whether or not you're using the same Docker host for both containers.

Join the Second Container to the Cluster​

  1. Create the container on the second host using docker run. 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=host dh2i/dxemssql

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

  3. Join the second container to the DxEnterprise cluster using the command dxcli join-cluster-ex. Replace the <PRIMARY_IP> variable in the command example below with the IP address from step 7 of the previous section.

    sudo docker exec dxemssql-1 dxcli join-cluster-ex <PRIMARY_IP> PassW0rd
    tip

    If the join command fails, ensure that the network ports used by DxEnterprise are open.

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

The third container can be on a different Docker host, or the same host as the other secondary. The steps required for each are provided below.

  1. Create the container on the host using docker run. Different steps are given below depending on how many Docker hosts you have. 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-2:/etc/dh2i -v mssql-2:/var/opt/mssql --name dxemssql-2 -h dxemssql-2 --network=host dh2i/dxemssql

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

  3. Join the third container to the DxEnterprise cluster using the command dxcli join-cluster-ex. Replace the <PRIMARY_IP> variable in the command example below with the IP address from step 7 of the previous section.

    sudo docker exec dxemssql-2 dxcli join-cluster-ex <PRIMARY_IP> PassW0rd

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