Say we have a database that we want to migrate a copy of into Kubernetes for test/dev purposes, and we don’t want to backup/restore.

How can it be done?

Well, with cross platform availability groups! We can deploy a pod to our Kubernetes cluster, create the availability group, and then auto-seed our database!

We’ll run through how to do this but first, I just want to point out that everything here firmly belongs in this category…

Ok, now that’s out the way…let’s see how this can be done.

We’ll need a “normal” instance of SQL Server running on Windows and a Kubernetes cluster.

Here I have one instance of SQL Server running SQL 2022 CU15 (HA enabled): –

And a Kubernetes cluster running v1.29.4: –

OK, now we need to deploy a SQL instance to the Kubernetes cluster. Going to use a statefulset for this: –

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mssql-statefulset
spec:
  serviceName: "mssql"
  replicas: 1
  selector:
    matchLabels:
      name: mssql-pod
  template:
    metadata:
      labels:
        name: mssql-pod
    spec:
      securityContext:
        fsGroup: 10001
      hostAliases:
      - ip: "10.225.115.136"
        hostnames:
        - "z-ap-sql-01"
      containers:
        - name: mssql-container
          image: mcr.microsoft.com/mssql/server:2022-CU15-ubuntu-20.04
          ports:
            - containerPort: 1433
              name: mssql-port
          env:
            - name: MSSQL_PID
              value: "Developer"
            - name: ACCEPT_EULA
              value: "Y"
            - name: MSSQL_AGENT_ENABLED
              value: "1"
            - name: MSSQL_ENABLE_HADR
              value: "1"
            - name: MSSQL_DATA_DIR
              value: /var/opt/sqlserver/data
            - name: MSSQL_LOG_DIR
              value: /var/opt/sqlserver/data
            - name: MSSQL_SA_PASSWORD
              value: "Testing1122"
          volumeMounts:
            - name: sqlsystem
              mountPath: /var/opt/mssql
            - name: sqldata
              mountPath: /var/opt/sqlserver/data
  volumeClaimTemplates:
    - metadata:
        name: sqlsystem
      spec:
        accessModes:
         - ReadWriteOnce
        resources:
          requests:
            storage: 1Gi
        storageClassName: mssql-sc
    - metadata:
        name: sqldata
      spec:
        accessModes:
         - ReadWriteOnce
        resources:
          requests:
            storage: 25Gi
        storageClassName: mssql-sc

This is fairly stripped down for this example. No tolerations, resource limits etc. Two persistent volumes will be created, one for the system databases and one for the user database data and log from a storage class already configured in the cluster.

A couple of things to note…

- name: MSSQL_ENABLE_HADR
              value: "1"

Here, HA is being enabled.

      hostAliases:
      - ip: "10.225.115.136"
        hostnames:
        - "z-ap-sql-01"

And here, an entry in the pod’s hosts file is being added for the SQL instance running on Windows.

OK, let’s deploy that (file is sqlserver-statefulset.yaml): –

kubectl apply -f sqlserver-statefulset.yaml

Next thing to do is deploy two services, one so that we can connect to the SQL instance (on port 1433) and one for the AG (port 5022): –

apiVersion: v1
kind: Service
metadata:
  name: mssql-service
spec:
  ports:
  - name: mssql-ports
    port: 1433
    targetPort: 1433
  selector:
    name: mssql-pod
  type: LoadBalancer
---
apiVersion: v1
kind: Service
metadata:
  name: mssql-ha-service
spec:
  ports:
  - name: mssql-ha-ports
    port: 5022
    targetPort: 5022
  selector:
    name: mssql-pod
  type: LoadBalancer

And let’s deploy that (file is sqlserver-service.yaml): –

kubectl apply -f sqlserver-service.yaml

Check that everything looks OK: –

kubectl get all

Great! Ok, now an entry in the SQL on Windows hosts file needs to be created for the external IP address of the service listening on port 5022.

In this example: –

10.225.115.132 mssql-statefulset-0

Confirm that we can connect to the SQL instance in Kubernetes in SSMS: –

Let’s start building the availability group!

Following the Microsoft guide here: –
https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cross-platform

Create a login and user on the Windows SQL instance: –

CREATE LOGIN dbm_login WITH PASSWORD = '<C0m9L3xP@55w0rd!>';
CREATE USER dbm_user FOR LOGIN dbm_login;
GO

And then a master key and certificate: –

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<C0m9L3xP@55w0rd!>';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
TO FILE = 'C:\Temp\dbm_certificate.cer'
WITH PRIVATE KEY (
        FILE = 'C:\Temp\dbm_certificate.pvk',
        ENCRYPTION BY PASSWORD = '<C0m9L3xP@55w0rd!>'
    );
GO

Grab the dbm_certificate.cer and dbm_certificate.pvk files, copy to your local machine, and then copy them into the Kubernetes pod: –

kubectl cp dbm_certificate.cer mssql-statefulset-0:/var/opt/mssql/data
kubectl cp dbm_certificate.pvk mssql-statefulset-0:/var/opt/mssql/data

One copied, run the following on the SQL instance in the Kubernetes pod to create a login/user, master key, and the certificate: –

CREATE LOGIN dbm_login WITH PASSWORD = '<C0m9L3xP@55w0rd!>';
CREATE USER dbm_user FOR LOGIN dbm_login;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<M@st3rKeyP@55w0rD!>'
GO

CREATE CERTIFICATE dbm_certificate
    AUTHORIZATION dbm_user
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
    FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
    DECRYPTION BY PASSWORD = '<C0m9L3xP@55w0rd!>'
)
GO

And then create the endpoint for the availability group on both the Windows SQL instance and the instance in the Kubernetes pod: –

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = 5022)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
        );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];
GO

Fantastic! Now we can create the availability group!

Run on the Windows SQL instance: –

CREATE AVAILABILITY
GROUP [ag1]
WITH (CLUSTER_TYPE = NONE)
FOR REPLICA
    ON N'z-ap-sql-01'
WITH (
    ENDPOINT_URL = N'tcp://z-ap-sql-01:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    SEEDING_MODE = AUTOMATIC,
    FAILOVER_MODE = MANUAL,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)
    ),
    N'mssql-statefulset-0'
WITH (
    ENDPOINT_URL = N'tcp://mssql-statefulset-0:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    SEEDING_MODE = AUTOMATIC,
    FAILOVER_MODE = MANUAL,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)
    )
GO

And then join the SQL instance in the Kubernetes pod: –

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
GO

Once that has completed, we have a cross platform availability group!

Right, final thing to do is get our database into the AG! For this, we are going to auto-seed the database to the Linux instance.

However, Windows and Linux have different file paths! But auto-seeding does work across platform, details are here: –
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatic-seeding-secondary-replicas

As long as the database’s data and log files are located under the Windows SQL instance’s default data and log file paths, the database will be seeded to the Linux instance’s default data and log file paths.

Let’s see this in action! The default file paths for my Windows instance are: –

And the database’s files reside on those file paths: –

And the default file paths for the SQL instance in the pod are: –

So now let’s add the database: –

ALTER AVAILABILITY GROUP [AG1] ADD DATABASE [testdatabase1];
GO

And boom! We have the database auto-seeded into the SQL instance running in the Kubernetes pod: –

Interestingly, if we look at the database’s file paths in SSMS for the instance in the pod: –

SQL in the pod thinks that the files are located at the Windows locations…but that can’t be true…it’s running on Linux!

If we go and check the actual location of the files…they are located at the default file paths for the SQL instance in the pod: –

OK, I fully admit this pretty out there…but it’s just a bit of fun to see what we can do with SQL Server Availability Groups and Kubernetes.

Thanks for reading!