Install and configure SQL Server instances and features

Container orchestration with Kubernetes

SQL Server in Linux containers is almost identical to SQL Server on Windows or Linux. As noted in Chapter 2, the Database Engine is just the same.

This section discusses how to deploy SQL Server in containers on Kubernetes, a container orchestration system initially developed by Google. Processes like fault tolerance, workload schedule, and even networking are all provided by the management layer of Kubernetes.

Once the orchestration is set up, you can configure and manage the databases inside the containers like any other SQL Server instances. One reason Kubernetes (also known as K8s, because there are eight letters between the K and the s) has become a staple in modern datacenters is its flexibility in container orchestration and management. It provides enterprise-level infrastructure functionality to the container development process favored by most DevOps organizations, making it, as Google describes it, the “operating system for the data center.”

Let’s use the analogy of an actual orchestra, comprising a conductor, instrument sections, musicians, their instruments, and their sheet music. While no analogy is perfect, this might help you picture things more easily. At the bottom are your musical instruments. Each instrument needs to be played by a musician, guided by their sheet music. Groups of musicians play together in a section. Finally, the conductor oversees the entire performance. In our analogy, Kubernetes is the conductor, containers are the instruments, clusters are the instrument families (like the string section or the brass section), and musicians are the pods with their sheet music.

Kubernetes relies on a software-defined infrastructure (the sheet music in our analogy). When you deploy your containers, you use a YAML (a recursive acronym standing for YAML Ain’t Markup Language) file that defines:

• The container image you are using

• Any storage you are persisting

• The container CPU and memory configuration of the pod

• The networking configuration

• Other metadata about the deployment

The deployment manifest is converted from YAML to JSON by kubectl and then deployed to the Kubernetes API, where it is parsed and then deployed into a key-value store (called etcd) that stores the cluster metadata. The objects in the manifest are deployed in their respective pods, services, and storage. The cluster controller (part of the control plane) ensures that the manifest is running and is in a healthy application state, and redeploys the manifest in the event of node failure or an unhealthy application state. The cluster will always attempt to maintain the desired state of the configuration, as defined in the deployed manifests.

Kubernetes support for SQL Server

Microsoft introduced support for Kubernetes after the release of SQL Server 2017 (see Figure 4-5). Early releases of Kubernetes lacked support for persisted storage, which is an obvious problem for database containers. The implementation uses a Kubernetes service to act as a persisted front-end name and IP address for the container. In this scenario, if the pod fails, the service stays running, and a new copy of the pod is launched and then pointed at the persisted storage. This is nearly analogous to the architecture of a SQL Server failover cluster instance (FCI).

• Refer to Chapter 2 for a more in-depth discussion on FCIs.

The SQL Server Kubernetes deployment provides for just a single container per SQL Server pod. Services provide load balancing and persistent IP addressing, while persistent volume claims ensure that storage is persisted across container failures or node movement. By defining a persistent volume claim, you align a specific disk volume to your pod deployment to persist data files.

Recent releases of both Kubernetes and Windows Server allow Kubernetes to support both Windows nodes and Windows containers, but SQL Server currently only supports containers on Linux. Kubernetes is also much more broadly used on Linux, so community support will be much more prevalent on that platform.

• To learn more about Kubernetes, read The Kubernetes Book (2022) by Nigel Poulton and Kubernetes: Up and Running (2022) by Brendan Burns et al.

• You can find out more about RHEL in Chapter 5.

Deploy SQL Server in containers

As mentioned, SQL Server runs on Windows, on Linux, and in containers. When originally released with SQL Server 2017, container support was touted for use in development. After all, there was limited support in the container world for persisted storage at the time, and SQL Server lacked support for an enterprise orchestration framework like Kubernetes.

While database containers still make for a fantastic development environment, the support in SQL Server for availability groups and AD authentication means that container deployment is quickly becoming an option for production workloads as well.

• You can read more about availability groups in Chapter 11.

Get started with SQL Server in a Docker container

One of the biggest attractions of running SQL Server in a container is that your choice of OS does not matter. While the container images of SQL Server use Linux as their base, your host machine can run Windows, Linux, or macOS.

While containers can run on almost all host operating systems, SQL Server in containers is only supported for production on Linux hosts running Intel or AMD 64-bit CPU architecture.

First, you will need to install Docker Desktop on your workstation.

• Download Docker Desktop from https://www.docker.com/products/docker-desktop.

After you have Docker installed, you can deploy a SQL Server container with the following steps:

1. Pull a copy of the container image from the Microsoft Container Registry (MCR) to your local environment. To do so, run this command from either a bash shell on Linux or macOS, or an elevated PowerShell prompt on Windows:

   sudo docker pull mcr.microsoft.com/mssql/server:2022-latest

   • You can find out more about the bash shell in Chapter 5 and about PowerShell in Chapter 9.

2. Use the following command to deploy the container. Note that the backslash in this command is a way to split a single bash command across multiple lines:

   sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>'    -p 1433:1433 --name sql2022    -v /users/randolph/mssql:/mssql    -d mcr.microsoft.com/mssql/server:2022-latest

You may be curious what these parameters (also called switches in Linux) mean:

• The docker pull command downloads the container image to your local container repository.

• The docker run command is where the deployment takes place.

• The -e switch allows for an environmental variable to be passed into the deployment. (Chapter 5 covers environment variables.) In this case, you are accepting the End-User License Agreement (EULA) for SQL Server, and providing a strong password for the SA account.

• The -p (or --publish; note the double-dash before the parameter) switch publishes the container’s public and private port for your container. To run multiple SQL Server containers simultaneously, specify a TCP port other than 1433 for the subsequent containers that are deployed.

• The --name switch (note the double-dash before the parameter) specifies the name of your container. This is not required, but if it is not specified, the system will generate a name.

• The -v switch is probably the most important in terms of database use. It allows a persistent volume to be mounted from your local machine to your container. In this case, the local directory /users/randolph/mssql will appear in the container as /mssql. Use this directory to store database backups or data files to be mounted to the container.

• The -d switch refers to the container image you are deploying. In this case you are deploying a SQL Server 2022 container from the MCR.

  • These are only a few of the command-line parameters that you might need. The full list is documented here: https://docs.docker.com/engine/reference/commandline/run/.

After the container is deployed, execute the docker ps command (which lists all the running containers) to confirm that your container is running. (In some environments you may need to run sudo docker ps.) Also, you can connect to your container using SQL Server tools like SSMS or Azure Data Studio, or sqlcmd, by connecting to localhost. This is possible because when you deployed the container, you configured it to run on TCP port 1433, which is the default SQL Server port.

You can also connect into your container with an interactive shell and execute sqlcmd. The first command will launch the bash shell within your container:

sudo docker exec -it sql1 "/bin/bash"

After launching the interactive bash shell within your container, you then call sqlcmd using the full path, since it is not in the path by default:

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourNewStrong!Passw0rd>'

Once your SQL Server container is deployed, you can execute T-SQL just like it was any other SQL Server.

Get started with SQL Server on Kubernetes

Although running SQL Server in a single Docker container is relatively easy, running SQL Server on a Kubernetes infrastructure is more challenging.

Kubernetes as part of Docker Desktop

You can install Kubernetes with Docker Desktop. However, as mentioned, persistent volumes are not supported on Intel-based macOS. If you are using Docker on Windows and you are running Windows 10 or Windows 11 Professional, you can configure Kubernetes after enabling Hyper-V.

• You can find the instructions for deploying Docker with Kubernetes at https://docs.docker.com/desktop/kubernetes.

Kubernetes using minikube

Another commonly used option for development and testing of Kubernetes is minikube, which runs across Windows, Linux, and macOS. minikube is an open-source project that allows for a deployment to your local workstation.

• Configuring minikube is part of the main Kubernetes documentation, available at https://kubernetes.io/docs/tutorials/hello-minikube.

Kubernetes using the Azure Kubernetes Service

If you need to simulate a production environment, we recommend deploying using Azure Kubernetes Service (AKS). (See Figure 4-7.) AKS is a managed service that allows you to quickly deploy a Kubernetes cluster of 1 node or up to 100 nodes.

• Configuring AKS is part of the main Azure documentation, available at https://learn.microsoft.com/azure/aks/learn/quick-kubernetes-deploy-cli.

AKS offers the benefit of hosting a highly available control plane for the cluster in Azure, as well as deploying the latest release of Kubernetes without installing software, worrying about dependencies, or finding hardware to build on. The other benefit of AKS is that the service itself is free. You are charged only for the underlying VM compute costs. Storage in AKS is provided by using either Azure Managed Disks or the Azure File service that acts as a file share.

Deploy SQL Server on Kubernetes

Once you have a Kubernetes cluster or simulated cluster like minikube, you can start deploying SQL Server. First, you will need to create a secret in Kubernetes to store your SA password:

kubectl create secret generic mssql --from-literal=MSSQL_SA_PASSWORD="<password>"

If kubectl (the Kubernetes command-line tool) is not installed on the machine where you are managing your cluster, you will need to install it to manage your deployment.

• Instructions for installing kubectl are available at https://kubernetes.io/docs/tasks/tools/install-kubectl.

Next, you will create a persistent volume claim (PVC). As mentioned, containers were originally designed to be ephemeral and not persist data across restarts or failures. A PVC will ask the cluster to provide a mapping to a persistent volume (PV). A PV can be statically or dynamically provisioned.

• A statically provisioned PV is defined by the cluster administrator. A PVC will be matched to that PV based on size and access mode.

• A dynamically provisioned PV is provisioned from a cluster-defined storage class. A PV asks the storage class to provision the volume from the underlying storage subsystem of the cluster. This can be a cloud provider’s persistent volume such as Azure Managed Disks, or even an on-premises SAN.

If you are using Azure Kubernetes Services, save the following code to a file called pvc.yaml:

kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
     name: azure-disk
provisioner: kubernetes.io/azure-disk
parameters:
  storageaccounttype: Standard_LRS
  kind: Managed
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: mssql-data
  annotations:
    volume.beta.kubernetes.io/storage-class: azure-disk
spec:
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 8Gi

This code defines the Azure storage class, and then an 8-GB volume. This code example uses Azure Storage, which is how you would implement on AKS. You will use slightly different code if you are using storage local to your cluster, like you do when using minikube or Docker:

kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: mssql-data-claim
spec:
  accessModes:
  - ReadWriteOnce
  resources:
  requests:
   storage: 8Gi

Just like in the Azure example, save this file to pvc.yaml and then deploy using this kubectl apply command:

kubectl apply -f C:\scripts\pvc.yaml

The next step is to deploy the SQL Server service and the pod itself. In the following code, you specify the load balancer service as well as the container running SQL Server. Kubernetes can use extensive metadata to describe and categorize your environment, as you will note from the metadata and label fields in the following YAML. Much like in the Docker script earlier, you define a port, passing in the SA password you defined in the secret and accepting the EULA. Finally, in the last section, you define the load balancer, which gives you a persistent IP address for your SQL instance.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql-deployment
spec:
  replicas: 1
  template:
    metadata:
      labels:
        app: mssql
    spec:
      terminationGracePeriodSeconds: 10
      containers:
      - name: mssql
        image: mcr.microsoft.com/mssql/server:2022-latest
        ports:
        - containerPort: 1433
        env:
        - name: MSSQL_PID
          value: "Developer"
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mssql
              key: MSSQL_SA_PASSWORD
        volumeMounts:
        - name: mssqldb
          mountPath: /var/opt/mssql
      volumes:
      - name: mssqldb
        persistentVolumeClaim:
          claimName: mssql-data
---
apiVersion: v1
kind: Service
metadata:
  name: mssql-deployment
spec:
  selector:
    app: mssql
  ports:
    - protocol: TCP
      port: 1433
      targetPort: 1433
  type: LoadBalancer

You can save this YAML as sql.yaml. Then, using the same kubectl apply -f command, you can deploy it from where you manage Kubernetes.

Congratulations, you now have SQL Server running on Kubernetes. You can run the kubectl get pods and kubectl get services commands as shown in Figure 4-8 to see your deployment.

If you review the output of the kubectl get services command, you will see the external IP address of your SQL Server service. You can now use any SQL Server client tool to connect to that address with the SA password you created in the secret.

Review cluster health

Kubernetes provides a built-in dashboard for monitoring the overall health of your cluster and its resource use. If you are using AKS, you can view this by running the az aks browse command with the resource group and cluster names. Depending on the configuration and version of your cluster, you may need to create a cluster role binding to view the dashboard, as shown in Figure 4-9.

• If you are not using AKS, you can find instructions on installing and configuring a dashboard for your cluster at https://kubernetes.io/docs/tasks/access-application-cluster/web-ui-dashboard.

Kubernetes deployments move all your infrastructure into scripts. For some automation-focused administrators, this may be the holy grail that they have been waiting for. But it is important to manage these scripts just as you would your application code. They should be version-controlled in a source control system like Azure DevOps or GitHub. If you are hosting your own repositories, you should ensure they are backed up and highly available.