Availability Groups on Kubernetes in SQL Server 2019 preview

Starting with SQL Server 2017, Microsoft embarked on a journey to support SQL Server deployments on Linux and containers, in order to provide a choice of deployment platform for our customers and partners. With the preview of SQL Server 2019, we announced new capabilities for deploying SQL Server with High Availability and Disaster Recovery on containers in Kubernetes. The capability is provided through the new SQL Server Operator for Kubernetes, which provides mechanisms for Always On Availability Groups, adding high availability and disaster recovery capabilities for container deployments of SQL Server.

Customers today are gravitating towards adopting SQL Server on containers for a plethora of deployment scenarios, including local application development testing for DevOps pipelines and in some cases production deployment. SQL Server containers, orchestrated through container frameworks like Kubernetes, provide a consistent, isolated and reliable behavior alongside providing the ease of usage, deployment, and management. The ability to deploy a SQL Server container with Availability Groups, provides an Enterprise-class HA and DR for these deployments.

SQL Server 2019 HA Operator

The Kubernetes operator for SQL Server is the key component which provides the functionality to deploy, manage and maintain the SQL Server HA deployment. The operator implements and registers the custom resource definition for SQL Server and the Availability Group resources. When the operator is deployed, it registers itself as a listener for notifications about SQL Server resources being deployed in the Kubernetes cluster. When a user creates or updates a SQL Server resource in the cluster a notification is sent to the operator, which then interprets and creates/updates the corresponding SQL Server object as needed.

The Docker container image implements the code for the operator and the following additional important binaries:

1. mssql-operator – Implements the Kubernetes operator for SQL Server and Availability Groups.
2. mssql-server-k8s-health-agent – Implements the logic to determine the health of a SQL Server Instance.
3. mssql-ha-supervisor – Implements the AG health detection and management logic, including the leader election logic to determine the Primary replica for the availability group. The leader election functionality is based on a fork of the Kubernetes client leader election.
4. mssql-server-k8s-init-sql – Implements the logic for deployment and initialization of a desired state configuration to a SQL Server instance.

Additional details on the other binaries implements in the HA container image can be found in the Always On availability groups for SQL Server containers documentation.

Deployment Architecture

The diagram below shows the high-level architecture of the SQL Server HA on Kubernetes.

The diagram above includes the following components:

• Operator – Deployment and upgrade orchestrator
• Operator Config map – Persists the state of the applied SQL Server custom resources and the state of upgrades
• SQL Server Statefulset – Consists of a container running SQL Server and an ag-agent maintaining the availability group clustering
• Statefulset Secret – Stores information pertaining to the replica the secret is associated with
• Availability group secret – Stores information shared by all replicas in the availability group
• Availability group config map – Stores information used for leader election

Deployment

Steps and samples for deployment a SQL Server HA solution in Azure Kubernetes Services (AKS) are documented under Deploy a SQL Server Always On availability group on a Kubernetes cluster documentation.

Deployment consists of the following steps:

1. Create a Kubernetes Namespace – More information on Kubernetes namespaces can be found under Namespace documentation.
2. Deploy the Kubernetes operator for SQL Server and Availability Groups.
3. Create a Kubernetes secret to store the SAPASSWORD and the MASTERKEYPASSWORD for the SQL Server instance.
4. Deploy the SQL Server custom resource.
5. Deploy Kubernetes proxy service to provide a load-balancing service for AG primary and AG secondary replicas.

Getting started

• To get started with SQL Server 2019 preview, find download instructions on the SQL Server 2019 web page.
• For more information about running SQL Server in containers, view the SQL Server product documentation here.

Sourabh Agarwal

Sr. Program Manager, SQL Server, Azure Data Group

See more articles from this author