Deploy a SQL Server Always On availability group on a Kubernetes cluster
The example in this article deploys a SQL Server Always On availability group on a Kubernetes cluster with three replicas. The secondary replicas are in synchronous commit mode.
On Kubernetes, the deployment includes a SQL Server operator, the SQL Server containers, and load balancer services. The operator orchestrates the availability group automatically. This article explains how to:
- Deploy the operator, SQL Server containers, and load-balancing services.
- Connect to the availability group with the services.
- Add a database to the availability group.
- A Kubernetes cluster
- Kubernetes version 1.11.0 or higher
- At least three nodes
- Access to the sql-server-samples GitHub repository
You can use any type of Kubernetes cluster. To create a Kubernetes cluster on Azure Kubernetes Service (AKS), see Create an AKS cluster. The following script creates a four-node Kubernetes cluster in Azure.
az aks create --resource-group myResourceGroup --name myAKSCluster --node-count 4 --kubernetes-version 1.11.3 --generate-ssh-keys
Deploy the operator, SQL Server containers, and load-balancing services
Create a namespace.
This example uses a namespace called
ag1. Run the following command to create the namespace.
kubectl create namespace ag1
All objects belonging to this solution are in the
Configure and deploy the SQL Server operator manifest.
operator.yamlfile is the deployment manifest for the Kubernetes operator.
Apply the manifest to the Kubernetes cluster.
kubectl apply -f operator.yaml --namespace ag1
Create a secret for Kubernetes with passwords for the
saaccount, and the SQL Server instance master key.
Create the secret with
The following example creates a secret named
ag1namespace. The secret stores two passwords:
sapasswordstores the password for the SQL Server
masterkeypasswordstores the password used to create the SQL Server master key.
Copy the script to your terminal. Replace each
<>with a complex password, and run the script to create the secret.
The password can't use
kubectl create secret generic sql-secrets --from-literal=sapassword="<>" --from-literal=masterkeypassword="<>" --namespace ag1
Deploy the SQL Server custom resource.
sqlserver.yamlfile describes the SQL Server containers, persistent volume claims, persistent volumes, and load-balancing services that are required for each SQL Server instance.
Apply the manifest to the Kubernetes cluster.
kubectl apply -f sqlserver.yaml --namespace ag1
The following image shows successful application of
kubectl apply for this example.
After you apply the SQL Server manifest, the operator deploys the SQL Server containers.
Kubernetes places the containers in pods. Use
kubectl get pods --namespace ag1 to see the status of the pods. The following image shows the example deployment after the SQL Server pods are deployed.
Monitor the deployment
You can use the Kubernetes dashboard with Azure Kubernetes Service to monitor the deployment.
az aks browse to launch the dashboard.
Connect to the availability group with the services
ag1-primaryprovides an endpoint to connect to the primary replica.
ag1-secondaryprovides an endpoint to connect to any secondary replica.
When you apply the manifest file, Kubernetes creates the load-balancing services for each type of replica. The load-balancing service includes an IP address. Use this IP address to connect to the type of replica you need.
To deploy the services, run the following command.
kubectl apply -f ag-services.yaml --namespace ag1
After you deploy the services, use
kubectl get services --namespace ag1 to identify the IP address for the services.
With the IP address, you can connect to the SQL Server instance that hosts each type of replica.
The following image shows:
The output from
kubectl get servicesfor the namespace
The load-balancing services that are created for each SQL Server container. Use these IP addresses as endpoints to connect directly to the instances of SQL Server in the cluster.
sqlcmdconnection to the primary replica, with the
saaccount via the load-balancer endpoint.
Add a database to the availability group
At this time, SQL Server Management Studios can't add a database to an availability group. Use Transact-SQL.
After Kubernetes creates the SQL Server containers, complete the following steps to add a database to the availability group.
Connect to a SQL Server instance in the cluster.
Create a database.
CREATE DATABASE [demodb]
Take a full backup of the database to start the log chain.
USE MASTER GO BACKUP DATABASE [demodb] TO DISK = N'/var/opt/mssql/data/demodb.bak'
Add the database to the availability group.
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [demodb]
The availability group is created with automatic seeding so that SQL Server automatically creates the secondary replicas.
You can view the state of the availability group from the SQL Server Management Studio Availability Groups dashboard.