Rediģēt

Kopīgot, izmantojot


Deploy availability groups with DH2i DxEnterprise on Kubernetes

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