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
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
Run the following command to apply the configuration.
kubectl apply -f headless_services.yaml
Create the StatefulSet
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
Create a credential for the SQL Server instance.
kubectl create secret generic mssql --from-literal=MSSQL_SA_PASSWORD="<password>"
Apply the StatefulSet configuration.
kubectl apply -f dxemssql.yaml
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.
Ensure you created the AG listener using DxEnterprise as outlined in the optional step near the end of the DH2i documentation.
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).
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
- Deploy availability groups on Kubernetes with DH2i DxOperator on Azure Kubernetes Service
- Deploy SQL Server containers on Azure Kubernetes Service
- Deploy SQL Server Linux containers on Kubernetes with StatefulSets
- Tutorial: Configure Active Directory authentication with SQL Server on Linux containers