Skip to main content
Version: v23.0

Contained Availability Groups on Linux and Containers

Microsoft SQL Server 2022 introduced the contained availability group feature. When configured, a contained availability group has private copies of the master and msdb databases, which contain metadata objects such as logins, permissions, SQL Agent jobs, etc.. As these databases are replicated to all members of the AG, their contents do not need to be manually configured on each replica, which can substantially reduce administrative overhead.

In order to make use of credential objects, certificates, or transparent database encryption in a contained availability group, with SQL Server 2022 on Linux, it is necessary to synchronize machine keys between SQL Server instances. This allows the Service Master Key in the contained master database to be decrypted and used on each replica.

This guide provides further instructions necessary for setting up a new DxEnterprise SQL Server Availability Group cluster featuring contained availability group within a Kubernetes platform.

Preparing a New Machine Key​

Before creating the DxEnterprise SQL Server Availability Group cluster, a common machine key to be used by all instances needs to be created. To generate a new machine key for use with SQL Server on Linux, in base64 format:

openssl rand -out machine-key.bin 44
printf '\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | dd of=machine-key.bin bs=1 seek=0 count=12 conv=notrunc
cat machine-key.bin | base64

Example:

$ openssl rand -out machine-key.bin 44
$ printf '\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' | dd of=machine-key.bin bs=1 seek=0 count=12 conv=notrunc
12+0 records in
12+0 records out
12 bytes copied, 0.0002043 s, 58.7 kB/s
$ cat machine-key.bin | base64
AQAAAAAAAAAAAAAA4vuOBIOXHEoPE7Jg8RTI7yFM8w5MR/73HjOuZKzarjk=

Pre-Loading the Machine Key​

Once a machine key has been prepared, it can be pre-loaded on the SQL Server data volume. Doing this before starting SQL Server for the first time allows SQL Server to perform its initial setup using this machine key.

info

This section is meant to supplement the instructions in the DxOperator Quick Start Guide.

When performing a new deployment with DxOperator using DxEnterpriseSqlAg, a SQL Server machine key can be pre-seeded to the SQL Server persistent data volume(s) using the method explained below. In the example, we will supplement a DxEnterpriseSqlAg deployment named dxesqlag.

  1. Create a secret containing the machine key, following the example in the previous section

    • Replace the example machine key with your own.
    kubectl create secret mssql-machinekey-dxesqlag --from-literal=MACHINE_KEY=AQAAAAAAAAAAAAAA4vuOBIOXHEoPE7Jg8RTI7yFM8w5MR/73HjOuZKzarjk=

  2. Add an initContainer to the DxEnterpriseSqlAg to install the machine key if not present.

    • Ensure that the image matches the container image defined in the mssqlServerContainer section. The default latest image is used in the example.
    apiVersion: dh2i.com/v1
    kind:  DxEnterpriseSqlAg
    metadata:
        name: dxesqlag
    spec:
        ...
        template:
            ...
            spec:
                ...
                initContainers:
                - name: machinekey-setter
                  image: "mcr.microsoft.com/mssql/server:latest"
                  command:
                  - "sh"
                  - "-c"
                  - "if [ ! -f /var/opt/mssql/secrets/machine-key ]; then mkdir -p /var/opt/mssql/secrets; echo $MACHINEKEY | base64 -d >/var/opt/mssql/secrets/machinekey; fi"
                  env:
                  - name: MACHINEKEY
                    valueFrom:
                        secretKeyRef:
                            name: mssql-machinekey-dxesqlag
                            key: MACHINE_KEY
                  volumeMounts:
                  - name: mssql
                    mountPath: "/var/opt/mssql"

    Note that the initContainer will check if a machine key file already exists, and if not, it will create one based on the machine key stored in the mssql-machinekey-dxesqlag secret.

  3. Complete the deployment, per the DxOperator Quick Start Guide

Additional Information​