Blog Post

SQL Server Cross Platform Availability Groups and Kubernetes

,

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 CU12 (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-CU12-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:Tempdbm_certificate.cer'
WITH PRIVATE KEY (
        FILE = 'C:Tempdbm_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, a bit of a trick is needed as (obviously) the Windows instance has different filepaths than the instance in the Kubernetes pod.

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

So, if we have a database on C:data we can auto-seed it to our instance in our Kubernetes pod!

But I don’t want to have to move my database…so let’s use symbolic links!

Here’s the file paths for the database data and log files on the Windows SQL instance: –

Create the C:data location: –

New-Item C:data -Type Directory

And then create two symbolic links from the database data/log locations into C:data:-

New-Item -ItemType SymbolicLink -Path C:datatestdatabase_data -Target E:datatestdatabase
New-Item -ItemType SymbolicLink -Path C:datatestdatabase_log -Target E:logtestdatabase

Then detach the database in the SQL instance and re-attach via the symbolic links: –

EXEC sp_detach_db 'testdatabase1';
GO
CREATE DATABASE [testdatabase1] ON 
( FILENAME = N'C:datatestdatabase_datatestdatabase1.mdf' ),
( FILENAME = N'C:datatestdatabase_logtestdatabase1_log.ldf' )
 FOR ATTACH
GO

The other thing that needs to be done is to set the default data/log locations of the SQL instance to C:data: –

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultData', REG_SZ, N'C:data'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultLog', REG_SZ, N'C:data'
GO

N.B. – The Windows SQL instance will need to be restarted in order for this change to take effect.

OK, now we can add the database to the availability group: –

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 file paths of the database in the Kubernetes pod: –

It’s showing as C:data! However we know that’s not true, the files are actually in the default data/log location specified when the statefulset was created: –

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!

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating