Persistent Server Name Metadata When Deploying SQL Server in Kubernetes

In this post, we will explore how a Pod name is generated, Pod Name lifecycle, how it’s used inside a Pod to set the system hostname, and how the system hostname is used by SQL Server to set its server name metadata.

Pod Naming in Deployments

When deploying SQL Server in Kubernetes using a Deployment, the Pod created by the Deployment Controller will have a name with a structure of <DeploymentName>-<PodTemplateHash>-<PodID> for example, mssql-deployment-8cbdc8ddd-9n7jh.

Let’s break that example Pod name down a bit more:

mssql-deployment – this is the name of the Deployment specified at metatdata.name. This is stable for the lifecycle of the deployment
8cbdc8ddd – this is a hash of the Pod Template Spec in the Deployment object template.spec. Changing the Pod Template Spec changes this value and also triggers a rollout of the new Pod configuration.
9n7jh – this is a random string assigned to help identify the Pod uniquely. This changes with the lifecycle of the Pod itself.

In a default Deployment configuration, the Pod’s name is used to system hostname inside the Pod. In a Deployment, when a Pod is deleted for whatever reason, Pod/Node failure, Pod administratively deleted, or an update to the Pod Template Spec triggering a rollout, the new Pod created will have a new Pod Name and a matching hostname inside the Pod. It is a new Pod after all. 🙂 This can lead to an interesting scenario inside SQL Server since the Pod name can change. Let’s dig deeper…

Server name metadata inside SQL Server running in a Pod

To ensure SQL Server’s data has a lifecycle independent of the Pod’s lifecycle, in a basic configuration, a PersistentVolume is used for the instance directory /var/opt/mssql. The first time SQL Server starts up, it copies a set of system databases into the directory /var/opt/mssql. During the initial startup, the current hostname of the Pod is used to set SQL Server system metadata for the server name. Specifically @@SERVERNAME, SERVERPROPERTY('ServerName') and the Name column from sys.servers.

In Listing 1, is an example Deployment for SQL Server. In this configuration, the hostname inside the Pod will match the current Pod Name. But what happens when the Pod name changes when a Pod is deleted, and new Pod is created with a new name? Let’s walk through that together in the next section.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql-deployment
spec:  
  replicas: 1
  strategy:
    type: Recreate
  selector:
    matchLabels:
        app: mssql
  template:
    metadata:
      labels:
        app: mssql
    spec:
      securityContext:
        fsGroup: 10001
      containers:
      - name: mssql
        image: 'mcr.microsoft.com/mssql/server:2019-CU8-ubuntu-18.04'
        ports:
        - containerPort: 1433
        env:
        - name: ACCEPT_EULA
          value: "Y"
        - name: SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mssql
              key: SA_PASSWORD 
        volumeMounts:
        - name: mssqldb
          mountPath: /var/opt/mssql
      volumes:
      - name: mssqldb
        persistentVolumeClaim:
          claimName: pvc-nfs-instance

Listing 1 – Example SQL Server Manifest using a Deployment Controller

Examining Server Name Metadata When Deploying SQL Server in a Deployment

Initial Deployment

When the Deployment is created, a Pod is created. In the output below, you can see the name of the Pod is mssql-deployment-bb44b7bf7-nzkmt, and the hostname set inside the Pod is the same, mssql-deployment-bb44b7bf7-nzkmt

kubectl get pods 
NAME                               READY   STATUS    RESTARTS   AGE
mssql-deployment-bb44b7bf7-nzkmt   1/1     Running   0          7s
kubectl exec -it mssql-deployment-bb44b7bf7-nzkmt -- /bin/hostname
mssql-deployment-bb44b7bf7-nzkmt

Check Server Name Metadata

Since this is the initial deployment of this SQL Server instance, system databases are copied into /var/opt/mssql, and the server name metadata is set. Let’s query SQL Server for @@SERVERNAME, SERVERPROPERTY('ServerName') and the Name column from sys.servers. In the output below you can see all three values match.

sqlcmd -S $SERVICEIP,$PORT -U sa -Q "SELECT @@SERVERNAME AS SERVERNAME, SERVERPROPERTY('ServerName') AS SERVERPROPERTY, name FROM sys.servers" -P $PASSWORD -W
SERVERNAME                          SERVERPROPERTY                   name
----------                          --------------                   ----
mssql-deployment-bb44b7bf7-nzkmt    mssql-deployment-bb44b7bf7-nzkmt mssql-deployment-bb44b7bf7-nzkmt

Delete the Currently Running Pod

Next, let’s delete a Pod and what happens to the Pod’s name, the Pod’s hostname, and the SQL Server server name metadata.

kubectl delete pod mssql-deployment-bb44b7bf7-nzkmt
pod "mssql-deployment-bb44b7bf7-nzkmt" deleted

I’ve deleted the Pod, and since this is controller by a Deployment controller, it immediately creates a new Pod in its place. This Pod gets a new name. The existing databases and configuration are persisted in the attached PersistentVolume at /var/opt/mssql. These databases are all brought online. In this output below, you can see the new Pod name and hostname are both mssql-deployment-bb44b7bf7-6gm6v.

kubectl get pods 
NAME                               READY   STATUS    RESTARTS   AGE
mssql-deployment-bb44b7bf7-6gm6v   1/1     Running   0          20s
kubectl exec -it mssql-deployment-bb44b7bf7-6gm6v -- hostname
mssql-deployment-bb44b7bf7-6gm6v

What’s in a name?

Now let’s query the server name metadata again. In the output below, you can see there are some inconsistencies. We saw above that Pod has a new name and hostname (mssql-deployment-bb44b7bf7-6gm6v), but this change isn’t updating all the server name metadata inside our Instance. The only place it is updated is SERVERPROPERTY('ServerName') the other values still have the initial Pod Name mssql-deployment-bb44b7bf7-nzkmt.

sqlcmd -S $SERVICEIP,$PORT -U sa -Q "SELECT @@SERVERNAME AS SERVERNAME, SERVERPROPERTY('ServerName') AS SERVERPROPERTY, name FROM sys.servers" -P $PASSWORD -W
SERVERNAME                          SERVERPROPERTY                   name
----------                          --------------                   ----
mssql-deployment-bb44b7bf7-nzkmt mssql-deployment-bb44b7bf7-6gm6v mssql-deployment-bb44b7bf7-nzkmt

Setting a Pod’s Hostname

So what do we do about this? Having instability in the server name metadata can break Replication, mess up our server monitoring systems, and even break code. To get the Pod’s hostname to a persistent value, you need to set the template.pod.spec.hostname field in the Deployment. This sets the system hostname inside the Pod to this value.

In the code below you, can see I’ve set the template.pod.spec.hostname to sql01. On the initial deployment of a SQL Instance, this is the value that is stored in the Instance server name metadata.

If you already have a SQL Server up and running in Kubernetes and did not set the template.pod.spec.hostname value, the server name metadata will need to be updated using standard SQL Server methods with sp_dropserver and sp_addserver.

But for demonstration purposes, I’m going to start over as if this is an initial deployment. And deploy the manifest in Listing 2 into my cluster.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql-deployment
spec:  
  replicas: 1
  strategy:
    type: Recreate
  selector:
    matchLabels:
        app: mssql
  template:
    metadata:
      labels:
        app: mssql
    spec:
      securityContext:
        fsGroup: 10001
      hostname:
        sql01
      containers:
      - name: mssql
        image: 'mcr.microsoft.com/mssql/server:2019-CU8-ubuntu-18.04'
        ports:
        - containerPort: 1433
        env:
        - name: ACCEPT_EULA
          value: "Y"
        - name: SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mssql
              key: SA_PASSWORD 
        volumeMounts:
        - name: mssqldb
          mountPath: /var/opt/mssql
      volumes:
      - name: mssqldb
        persistentVolumeClaim:
          claimName: pvc-nfs-instance

Listing 2 – Example SQL Server Manifest using a Deployment Controller, setting the Pod’s hostname

In the output, below the Pod Name is mssql-deployment-8cbdc8ddd-nv8j4, but inside the Pod, the hostname is sql01, and now all three values for our server name metadata match. If this Pod is deleted, the Pod gets a new name, the hostname inside the Pod will still be sql01, and the Pod server name metadata will still be set to sql01.

kubectl get pods 
NAME                               READY   STATUS    RESTARTS   AGE
mssql-deployment-8cbdc8ddd-nv8j4   1/1     Running   0          43s
kubectl exec -it mssql-deployment-8cbdc8ddd-nv8j4  -- hostname
sql01
sqlcmd -S $SERVICEIP,$PORT -U sa -Q "SELECT @@SERVERNAME AS SERVERNAME, SERVERPROPERTY('ServerName') AS SERVERPROPERTY, name FROM sys.servers" -P $PASSWORD -W
SERVERNAME  SERVERPROPERTY name
----------  -------------- ----
sql01       sql01           sql01

Setting the hostname in the Pod Template Spec gives you the ability to persist the hostname and thus the server name metadata inside SQL Server. This is crucial for services and code that depend on a static hostname. A StatefulSet is a Controller in Kubernetes that does give you persistent, stable naming independent of the lifecycle of a Pod. I will explore those in an upcoming blog post.

The post Persistent Server Name Metadata When Deploying SQL Server in Kubernetes appeared first on Centino Systems Blog.