High Availability for Containerized SQL Server in K8s

January 24, 2022January 20, 2022 Don Boxley high availability, kubernetes, Microsoft SQL Server, stateful applications

by Don Boxley

Containers have numerous benefits for IT operations. But when it comes to using containerized stateful workloads, such as Microsoft SQL Server, a key obstacle remains: Implementing high availability (HA).

Kubernetes is well known for its ability to automate computer application management, deployment and scaling. Yet by itself, the native HA of K8s is not fast enough to support SQL Server workloads. Historically, containers have not supported highly available availability groups (AGs), the standard technology many IT pros rely on for HA.

But a new class of smart availability software—which is the convergence of HA and software-defined perimeter (SDP) solutions—provides robust HA for containerized SQL Server AGs. This software can seamlessly create secure hybrid AG clusters across networks and platforms, including Azure Kubernetes Service (AKS), without using VPNs. It also protects against node failures within containers and apps with close to zero recovery time objective (RTO) automatic failovers. And IT teams can also use the technology to avoid common challenges with HA/disaster recovery (DR) configuration and networking.

The History of SQL Server Containers

Before digging deeper into how this new technology can ensure peak availability for containerized SQL Server in K8s, consider this short but very important history. These containers were introduced with SQL Server on Linux in 2017 and were updated in 2019 with additional features and options like PolyBase, replication and machine learning. That same year, new solutions were built on top of these containers, including big data clusters.

In 2021, a product that is completely based on SQL Server containers was released: Azure Arc for SQL Server. Another new tool, called adutil, was recently released to help IT teams easily configure Active Directory authentication for SQL Server containers. In short, over the last four years, Microsoft improved on and invested in SQL Server containers in Linux and these containers became an even more important piece in the product roadmap.

Sponsorships Available

Container Benefits

Why do organizations use SQL Server containers and what are the benefits? There are five major reasons.

Portability. One of the first benefits is the portability that comes with containers. SQL Server leverages this benefit as well, which means that all SQL Server features are combined into a single image that can run on any Linux host including Ubuntu, Red Hat Enterprise Linux (RHEL) as well as on SUSE Linux. This agnostic approach allows IT to run SQL Server containers on all supported operating systems.

No installation. A second benefit is that there is no installation required for SQL Server containers. When the image is locally downloaded, IT can simply deploy the containers and have a full-fledged SQL Server running in their environment, whether with or without K8s, in less than five seconds with a single command.

Security. A third benefit is security. SQL Server containers are as secure as SQL Server on bare metal or SQL Server on virtual machines (VMs). Features like transparent data encryption and OpenSSL encryption are available and are completely supported in SQL Server containers. An additional security feature is that, by default, SQL 2019 containers themselves start as a non-root user, which is a recommended best practice when running containers.

Customizable. SQL Server containers are not one-size-fits-all; they are customizable and configurable. The rules for customization are spelled out in the support policy document.

Stateful. One important point; when we refer to containers, it generally refers to a stateless application. SQL Server can also be deployed in containers as a stateful application. This means all the data stored in your databases persists even when you delete or destroy the containers.

These five features are the reasons many enterprises are using SQL Server containers—for example, in hybrid environments on Windows and Linux deployed in K8s environments—all using the various options that are available.

Overcoming the HA Obstacle with Software

A key obstacle to wider adoption lies in implementing HA. Fortunately, new smart availability software can help companies achieve HA and business continuity with SQL Server containers.

Tip: Use K8s to provide pod-level HA for SQL Server containers and smart availability software to provide AG HA at the database level.

This can be provided as a cluster service that’s installed on each node or container that does the core management, network management, fault detection and failover automation for the SQL Server availability group. This simplifies setting up a cluster and provisioning your HA availability group and can be achieved without the use of cumbersome and restrictive legacy clustering and VPN solutions.

Smart availability software is also flexible in that the clustering itself supports both Windows and Linux, and can run both inside and outside of containers, enabling mix-and-match.

Tip: Use smart availability software to run a Windows VM, some Linux nodes and containers in Azure all in the same HA availability group cluster.

In addition to this flexibility, SA software can also help with migration from one platform to another.

Tip: Leverage the software’s built-in SDP features, like secure tunneling, to unlock hybrid cloud capabilities for running various nodes that may reside in different isolated networks.

This is also important for the container use case, handling the networking required for the cluster communication and AG replication between various nodes, whether those are bare metal machines, VMs or containers.

Since SA software can be run inside or outside K8s, it’s easy to set up clusters that reside in different locations with secure tunneling and end-to-end automatic failover between clusters. As mentioned earlier, other approaches—such as Kubernetes alone—don’t necessarily meet the production HA requirements that many organizations have for SQL Server.

Smart availability software enables AGs to be highly available between containers. Availability groups are themselves a technology that many (if not most) SQL Server setups are already familiar with, flattening the learning curve. Deployment is also simplified, as IT can create a container image that contains both SQL Server and the HA software and then use that image to deploy containers. Once these containers are created, it’s easy to create an HA availability group between those containers.

With the use of smart availability software, enterprises can solve the problem of implementing SQL Server AGs for containers with automatic failover. Organizations can leverage the software to create hybrid Kubernetes AG clusters across networks; protect against node, container and app failures and avoid headaches with HA/DR configuration.