Edit

Share via

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: docker.io/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>"

    Your password should follow the SQL Server default password policy. By default, the password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, base-10 digits, and symbols. Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.

  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