Skip to main content
Version: v26.0-RC

SQL Server Availability Groups Using Container Host Networking

Introduction​

Container host networking allows you to deploy containers directly on the host's network interface instead of using a container-managed 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 are not deployed using an overlay network (for example, Docker Swarm).

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

info

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

Docker Swarm is an alternative to host networking that provides an overlay network for container-to-container communication, instead of using the host network. 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 with a supported container runtime installed. Supported runtimes: Docker or Podman. Refer to the official Docker or Podman documentation for installation instructions.

  • An internet connection to download the DxEMSSQL container image from a container registry, or an image built and pushed to a public or private repository using the instructions in the Building DxEMSSQL guide.

    • When using Podman, fully qualify Docker Hub images with the registry name (for example, docker.io/dh2i/dxemssql).

  • 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, please contact us.

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.

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. 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 \
      --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 DxEnterprise'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 the containers are deployed on the same host or on different hosts.

Join the Second Container to the Cluster​

  1. Create the container on the second host. 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 deployed on a different host or on the same host as the other secondary. The steps required for each scenario are provided below.

  1. Create the container on the host. 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 <PRIMARY_IP> 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​