Skip to main content
Version: Archive

SQL Server Availability Groups for Docker Quick Start Guide

Introduction​

This quick start guide describes how to create and deploy a DxEnterprise + SQL Server container cluster in a Docker environment for Availability Groups. Using this guide, you will create a DxEnterprise + SQL Server container image and use DxEnterprise 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​

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

  • 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 Image and Containers​

These first two sections cover creating the Docker image using the provided dockerfile, then creating the containers on a local VM.

Create the SQL Server and DxEnterprise Combined Container Image​

First you will create a custom Docker image that combines the SQL Server container image from Docker Hub with a customized DxEnterprise installation meant specifically for containers.

  1. Copy the contents of the Dockerfile below to a file your current working directory.

    info

    This image is built from the mcr.microsoft.com/mssql/server:2019-latest image on Docker Hub. What it does:

    1. It installs .NET Core 3.1 and net-tools. net-tools is not required for the container to run, but it may help with diagnosing network issues.
    2. It copies the latest DxEnterprise tarball from https://repos.dh2i.com/container/, unpacks it, and creates the necessary user permissions.
    3. It assigns the default user as mssql and sets the entrypoint.
    FROM mcr.microsoft.com/mssql/server:2019-latest
    USER root
    #Install dotnet and net-tools
    RUN apt-get update \
       && ACCEPT_EULA=Y apt-get upgrade -y \
       && apt-get install -y wget \
       && wget --no-dns-cache https://packages.microsoft.com/config/ubuntu/20.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb \
       && dpkg -i packages-microsoft-prod.deb \
       && apt-get update \
       && apt-get install -y dotnet-runtime-3.1 net-tools \
       && dpkg --purge packages-microsoft-prod \
       && apt-get purge -y wget \
       && apt-get clean \
       && rm packages-microsoft-prod.deb \
       && rm -rf /var/lib/apt/lists/*
    #Download and unpack DxE, setup permissions
    ADD https://repos.dh2i.com/container/ ./dxe.tgz
    RUN tar zxvf dxe.tgz && rm dxe.tgz \
       && chown -R mssql /var/opt/mssql \
       && chmod -R 777 /opt/dh2i /etc/dh2i
    #Finish setup
    EXPOSE 7979 7985
    ENV DX_HAS_MSSQLSERVER=1
    USER mssql
    ENTRYPOINT ["/opt/dh2i/sbin/dxstart.sh"]

  2. Build the image using the Dockerfile.

    docker build -t dh2i/dxemssql -f dxemssql.dockerfile ./
    info

    Docker will accept many variations for tagging. This guide uses the pattern dh2i/dxemssql, but any tag (e.g., test:1, dh2i:test, mycorp/myrepo:mytag and others) may be used.

    The dh2i/dxemssql image provided on Docker Hub is provided as-is for testing/non-production use only.

Create the Containers​

  1. Create the first container.

    caution

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

    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, incrementing the value for --name, -h, and both volumes (-v):

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

    Example

    docker exec dxemssql-0 dxcli activate-server AAAA-BBBB-CCCC-DDDD

  2. Add a Vhost to the cluster using the command dxcli cluster-add-vhost.

    Example

    docker exec dxemssql-0 dxcli cluster-add-vhost vhost1 *127.0.0.1 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.

    Example

    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.

    Example

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

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

    Example

     docker exec dxemssql-0 dxcli set-otpk

Join the Second Container to the DxEnterprise Cluster​

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

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

    Example

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

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

    Example

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

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

    Example

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

Join the Third Container to the DxEnterprise Cluster​

Finally, join the last secondary into the cluster.

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

    Example

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

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

    Example

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

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

    docker exec dxemssql-2 dxcli add-ags-node vhost1 ags1 "dxemssql-secondary-1|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit|50223"

Add the Availability Group Database(s)​

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

    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.

    Example

    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.

    Example

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

Additional Information​