Edit

Deploy availability groups with DH2i DxEnterprise on Kubernetes

  • Article

Applies to: SQL Server - Linux

This tutorial explains how to configure SQL Server Always On availability groups (AGs) for SQL Server Linux based containers deployed to an Azure Kubernetes Service (AKS) Kubernetes cluster, using DH2i DxEnterprise. You can choose between a sidecar configuration (preferred), or build your own custom container image.

Note

Microsoft supports data movement, AG, and SQL Server components. DH2i is responsible for support of the DxEnterprise product, which includes cluster and quorum management.

Using the steps mentioned in this article, learn how to deploy a StatefulSet and use the DH2i DxEnterprise solution to create and configure an AG. This tutorial consists of the following steps.

  • Create a headless service configuration
  • Create a StatefulSet configuration with SQL Server and DxEnterprise in the same pod as a sidecar container
  • Create and configure a SQL Server AG, adding the secondary replicas
  • Create a database in the AG, and test failover

Prerequisites

This tutorial shows an example of an AG with three replicas. You need:

  • An Azure Kubernetes Service (AKS) or Kubernetes cluster.
  • A valid DxEnterprise license with AG features and tunnels enabled. For more information, see the developer edition for nonproduction usage, or DxEnterprise software for production workloads.

Create the headless service

  1. In a Kubernetes cluster, headless services allow your pods to connect to one another using hostnames.

    To create the headless service, Create a YAML file called headless_services.yaml, with the following sample content.

    #Headless services for local connections/resolution
apiVersion: v1
kind: Service
metadata:
name: dxemssql-0
spec:
clusterIP: None
selector:
    statefulset.kubernetes.io/pod-name: dxemssql-0
ports:
- name: dxl
    protocol: TCP
    port: 7979
- name: dxc-tcp
    protocol: TCP
    port: 7980
- name: dxc-udp
    protocol: UDP
    port: 7981
- name: sql
    protocol: TCP
    port: 1433
- name: listener
    protocol: TCP
    port: 14033
---
apiVersion: v1
kind: Service
metadata:
name: dxemssql-1
spec:
clusterIP: None
selector:
    statefulset.kubernetes.io/pod-name: dxemssql-1
ports:
- name: dxl
    protocol: TCP
    port: 7979
- name: dxc-tcp
    protocol: TCP
    port: 7980
- name: dxc-udp
    protocol: UDP
    port: 7981
- name: sql
    protocol: TCP
    port: 1433
- name: listener
    protocol: TCP
    port: 14033
---
apiVersion: v1
kind: Service
metadata:
name: dxemssql-2
spec:
clusterIP: None
selector:
    statefulset.kubernetes.io/pod-name: dxemssql-2
ports:
- name: dxl
    protocol: TCP
    port: 7979
- name: dxc-tcp
    protocol: TCP
    port: 7980
- name: dxc-udp
    protocol: UDP
    port: 7981
- name: sql
    protocol: TCP
    port: 1433
- name: listener
    protocol: TCP
    port: 14033

  2. Run the following command to apply the configuration.

    kubectl apply -f headless_services.yaml

Create the StatefulSet

  1. Create a StatefulSet YAML file with following sample content, and name it dxemssql.yaml.

    This StatefulSet configuration creates three DxEMSSQL replicas that utilize persistent volume claims to store their data. Each pod in this StatefulSet comprises two containers: a SQL Server container and a DxEnterprise container. These containers are started separately from one another in a "sidecar" configuration, but DxEnterprise manages the AG replica in the SQL Server container.

    #DxEnterprise + MSSQL StatefulSet
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: dxemssql
spec:
serviceName: "dxemssql"
replicas: 3
selector:
    matchLabels:
    app: dxemssql
template:
    metadata:
    labels:
        app: dxemssql
    spec:
    securityContext:
        fsGroup: 10001
    containers:
    - name: sql
        image: mcr.microsoft.com/mssql/server:2022-latest
        env:
        - name: ACCEPT_EULA
        value: "Y"
        - name: MSSQL_ENABLE_HADR
        value: "1"
        - name: MSSQL_SA_PASSWORD
        valueFrom:
            secretKeyRef:
            name: mssql
            key: MSSQL_SA_PASSWORD
        volumeMounts:
        - name: mssql
        mountPath: "/var/opt/mssql"
    - name: dxe
        image: dh2i/dxe
        env:
        - name: MSSQL_SA_PASSWORD
        valueFrom:
            secretKeyRef:
            name: mssql
            key: MSSQL_SA_PASSWORD
        volumeMounts:
        - name: dxe
        mountPath: "/etc/dh2i"
volumeClaimTemplates:
- metadata:
    name: dxe
    spec:
    accessModes:
    - ReadWriteOnce
    resources:
        requests:
        storage: 1Gi
- metadata:
    name: mssql
    spec:
    accessModes:
    - ReadWriteOnce
    resources:
        requests:
        storage: 1Gi

  2. Create a credential for the SQL Server instance.

    kubectl create secret generic mssql --from-literal=MSSQL_SA_PASSWORD="<password>"

  3. Apply the StatefulSet configuration.

    kubectl apply -f dxemssql.yaml

  4. Verify the status of the pods, and proceed to the next step when the pod's status becomes running.

    kubectl get pods
kubectl describe pods

Create availability group and test failover

For details on creating and configuring AG, adding replicas, and testing failover, refer to SQL Server Availability Groups in Kubernetes.

Steps to configure availability group listener (optional)

You can also configure an AG listener, with the following steps.

  1. Ensure you created the AG listener using DxEnterprise as outlined in the optional step near the end of the DH2i documentation.

  2. In Kubernetes, you can optionally create static IP addresses. When you create a static IP address, you ensure that if the listener service is deleted and recreated, the external IP address assigned to your listener service doesn't change. Follow the steps to create a static IP address in Azure Kubernetes Service (AKS).

  3. After you create an IP address, you assign that IP address and create the load balancer service, as shown in the following YAML sample.

    apiVersion: v1
kind: Service
metadata:
  name: agslistener
spec:
  type: LoadBalancer
  loadBalancerIP: 52.140.117.62
  selector:
    app: mssql
  ports:
  - protocol: TCP
    port: 44444
    targetPort: 44444

Steps to configure read/write connection redirection (optional)

After you create the AG, you can enable read/write connection redirection from the secondary to primary by following these steps. For more information, see Secondary to primary replica read/write connection redirection (Always On Availability Groups).

USE [master];
GO

ALTER AVAILABILITY
GROUP [ag_name] MODIFY REPLICA
    ON N'<name of the primary replica>'
WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO

USE [master];
GO

ALTER AVAILABILITY
GROUP [AGS1] MODIFY REPLICA
    ON N'<name of the secondary-0 replica>'
WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO

USE [master];
GO

ALTER AVAILABILITY
GROUP [AGS1] MODIFY REPLICA
    ON N'<name of the secondary-1 replica>'
WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO

USE [master];
GO

ALTER AVAILABILITY
GROUP AGS1 MODIFY REPLICA
    ON N'<name of the primary replica>'
WITH (PRIMARY_ROLE(READ_WRITE_ROUTING_URL = 'TCP://<External IP address of primary -0>:1433'));
GO

USE [master];
GO

ALTER AVAILABILITY
GROUP AGS1 MODIFY REPLICA
    ON N'<name of the secondary-0 replica>'
WITH (PRIMARY_ROLE(READ_WRITE_ROUTING_URL = 'TCP://<External IP address of secondary -0>:1433'));
GO

USE [master];
GO

ALTER AVAILABILITY
GROUP AGS1 MODIFY REPLICA
    ON N'<name of the secondary-1 replica>'
WITH (PRIMARY_ROLE(READ_WRITE_ROUTING_URL = 'TCP://<External IP address of secondary -1>:1433'));
GO

Related content