Introduction to Running SQL Server 2019 on Kubernetes

In recent years, Microsoft has fully embraced the open source paradigm. One of the more prominent examples of this trend was providing the ability to host SQL Server 2017 instances on Linux, including running Docker-based SQL Server images (available directly from Microsoft Container Registry) on Kubernetes clusters. The release of SQL Server 2019 further extends the multi-platform capabilities by allowing for Kubernetes-based deployments of SQL Server Always On Availability Groups, delivering increased level of resiliency. In this article, the basic characteristics of these deployments are introduced.

In order to better understand the technology that makes such deployments possible, it might be helpful to review some of the fundamental concepts of Kubernetes. Just as any other container orchestration platform, its primary purpose is to facilitate running containerized workloads within a cluster consisting of multiple nodes. Kubernetes builds a layer of abstraction around individual containers by forming pods, which constitute a logical resource hosting an individual instance of containerized application (it is common for a single pod to map to a container, but that is not a requirement – a single pod can consist of multiple containers). Pods bring all of the benefits associated with containerization, including portability and highly optimized resource utilization. Pods can also be used to implement custom Kubernetes resources, which extend the functionality implemented by the orchestrator.

Kubernetes resources hosted by cluster nodes (such as pods) can be logically isolated by using namespaces. This isolation permits using the same set of cluster nodes by separate workloads, while, at the same time, restricting their mutual visibility (although it is important to keep in mind that namespace-based isolation is not considered sufficient from the security standpoint, which instead requires the use of separate clusters).

Pods are by design ephemeral. In order to provide resiliency for containerized workload, Kubernetes employs controllers. The primary purpose of controllers is to handle lifecycle of pod deployments. While it is possible to schedule execution of individual pods (without the use of deployments), such approach is not recommended since it effectively precludes resiliency provisions offered by deployment controllers.

Controllers schedule execution of pods on cluster nodes. The underlying scheduling mechanism facilitates deployment of both stateless and stateful workloads. The former is traditionally accomplished by using the Deployment controller, which creates a ReplicaSet consisting of one or more identical pods. A common approach to implementing the latter relies on the StatefulSet controller, responsible for persisting the state of individual pods beyond their lifetime. However, it is also possible to configure state persistence when using Deployment controllers.

State persistence can be set up in several different ways, but, in general, it relies on storage resources (referred to as volumes) accessible by underlying cluster nodes. Generic volumes typically serve as persistent store for containerized workload and can take the form of local disks attached directly to individual cluster nodes (as is the case with the StatefulSet controller) or shared storage mounted across multiple nodes (which is supported by the ReplicaSet controllers). Pods rely on persistent volume claims in order to maintain access to the underlying storage. Kubernetes offers additionally more specialized volume types, such as secret (intended for sensitive data, which is not supposed to be written to a disk but instead maintained in tmpfs) and configMap (used to inject key-value pairs into pods that belong to the same namespace).

This brief introduction into Kubernetes-based container orchestration should help with understanding its support for SQL Server and SQL Server Always On Availability Groups. The original mechanism introduced in SQL Server 2017, which offered continuity of operations of a database engine instance in a Kubernetes cluster, relied on the characteristics of the replica set and health monitoring of the SQL Server process within individual containers. Such monitoring can detect a failure of a process, pod, or entire node and, in response, activating another instance within the same replica set and reestablishing persistent volume claim from the new pod.

With the new approach based on SQL Server Always On Availability Group functionality, Kubernetes forms a StatefulSet consisting of multiple SQL Server pods, with automated failover between them leveraging a custom resource referred to as SQL Server 2019 HA Operator, implemented as a single instance ReplicaSet. Its role is to manage deployment of the StatefulSet and to handle health monitoring of its pods. This delivers not only increased resiliency with accelerated recovery, but also can be used to offload backups and to scale out read operations (just as with traditional Always On Availability Group deployments).

The Kubernetes-based implementation of SQL Server Always On Availability Group consists of the following components:

  • a ReplicaSet, containing the HA operator and its configMap, which persists such settings as the container image and image provisioning details.
  • a StatefulSet containing pods running SQL Server Always On Availability Group cluster node. The pods are sharing ConfigMap and Secret persistent store containing availability group-level settings but each of them has its own dedicated Secret store, protecting such sensitive information such as passwords and master keys.
  • a load balancing service providing access to two endpoints representing, respectively, the primary and secondary replicas.

This concludes the introduction to deployment SQL Server Always On Availability Groups on Kubernetes clusters. This topic will be explored in more details in upcoming articles published on this site.

# # #

See All Articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles