Author Archives: dbafromthecold

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 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!

EightKB 2024 – Schedule and Registration

~

Hello Hello, We. Are. Back!

The schedule for EightKB 2024 Edition has been announced!

We’re kicking off at 1pm UTC on August 8th (8th of the 8th…get it? 🙂 )…here’s the schedule: –

N.B. – If you click on the session on the website, it shows the session abstract…nice touch eh?

Once again we have five top class speakers delivering five great, in-depth sessions on various SQL Server internals topics.

We only have five slots in each event which meant we ended up not picking some amazing sessions. I want to say a MASSIVE thank you to all who submitted…session selection is the WORST part of running a conference! Really, truly…thank you to everyone who submitted.

Registration is now open, it’s completely free and you can sign up here: –
https://eightkb.online/

The event will be in Zoom and we’ll have chat going in the EightKB channel in the SQL Community Slack…please come and hang out with us there!

EightKB is a 100% community driven with no sponsors so, we have our own Bonfire store selling t-shirts!

Don’t they look snazzy?!

Any money generated from the store will be put straight back into the event.

Hope to see you on August the 8th!

Automating Container Image Builds with Docker Build Cloud and Github Actions

~

In a previous post we went through how to use Docker Build Cloud to remotely build a Docker container image from a Github repository.

In that example, we kicked off a build and pushed the image to a container registry using the syntax: –

docker buildx build https://github.com/dbafromthecold/sqlserver2022.git `
--builder cloud-dbafromthecold-default `
--tag dbafromthecold/sqlserver2022:latest `
--push

This all seems a bit manual, doesn’t it?

What if we could automate the building of the image and the push to the container registry when we commit to the Github repository?

Thankfully, with Github Actions…we can do just that!

I don’t really have much experience with Github Actions if I’m honest…but all the syntax needed is provided in the Docker Build Cloud: –

N.B. – this option is under the builder that you use to build the image (using the default one here)

This gives the following code: –

name: ci

on:
  push:
    branches:
      - "main"

jobs:
  docker:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout
        uses: actions/checkout@v4
      - name: Log in to Docker Hub
        uses: docker/login-action@v3
        with:
          username: ${{ secrets.DOCKERHUB_USERNAME }}
          password: ${{ secrets.DOCKERHUB_TOKEN }}
      - name: Set up Docker Buildx
        uses: docker/setup-buildx-action@v3
        with:
          version: "lab:latest"
          driver: cloud
          endpoint: "dbafromthecold/default"
      - name: Build and push
        uses: docker/build-push-action@v5
        with:
          context: .
          tags: "dbafromthecold/sqlserver2022"
          outputs: ${{ github.event_name == 'pull_request' && 'type=cacheonly' || 'type=registry,push=true' }}

The only update I’ve had to make to the code provided is line 29 where I’ve changed the tag to “dbafromthecold/sqlserver2022”

OK, so let’s go and create this Github Action.

The first thing to do is create secrets in the Github repository to allow the action to connect to Docker Build Cloud.

For this example we’ll use the repo from the previous blog: –
https://github.com/dbafromthecold/sqlserver2022

In the repo, go to /settings/secrets/actions and create two repository secrets: –

These are your Docker Hub username and an access token created under your account (https://hub.docker.com/settings/security): –

Once the secrets are created, we can then create the workflow in Github.

In the repo, go to Actions and then select new workflow: –

Choose simple workflow: –

Remove the template code and replace with the code from Docker Build Cloud (remember to update the tag name on line 29!): –

Commit the changes and we’re good to go!

I have the repo locally so I’ll pull down the changes made in the web gui…

git pull

And now let’s test committing a change to the repo and pushing.

There’s a dockerfile in the repo (to build the image from)…I’ll make a simple change (adding a LABEL for example) and push: –

git add .
git commit -m "testing github action"
git push

And if we go back to Actions in the repo in Github we should see it working!

But did it build and push the image? Let’s check the Docker Hub: –

Awesome stuff, the image has been built and pushed to the Hub!

So by using Docker Build Cloud and Github Actions, we can push changes to a Github repository, remotely build a container image, and then push to a container registry without having to use any local resources!

Thanks for reading!

Building a Docker image with Docker Build Cloud

~

In a previous blog post we went through how to build a Docker container image from a remote (Github) repository.

Here we’re going to expand on that by actually building the image itself remotely, using Docker Build Cloud.

What we can do with Docker Build Cloud is instead of building the image locally and then having to push to a remote container registry (for example the Docker Hub), we can build remotely and then immediately push that image to the registry so that it is available for immediate use by say, our team members or deployment/testing pipelines.

This has advantages as we no longer are reliant on our local system. Build Cloud uses isolated Amazon EC2 instances so we’ll get a consistent build speed and if our images are large (I mainly work with SQL Server images, which are about 1.5GB in size), and we have a poor internet connection, we don’t have to wait for ages to push the image to the registry.

I have a remote repository that we’re going to use to build a SQL Server 2022 container image. Very simple repo, with just a dockerfile in it to build the image.

Now, to build an image from that remote repository locally, we would run: –

docker build -t dbafromthecold/sqlserver2022:latest https://github.com/dbafromthecold/sqlserver2022.git

But how do we use Build Cloud?

First thing to do is create a Docker account and sign up for Build Cloud.
Full directions are here: –
https://docs.docker.com/build/cloud/
N.B. – Docker Personal accounts are free and will give you 50 “build minutes” per month.

Once we have the account signed up we are good to go!

To build an image using the cloud builder we first need to log into Docker on the command line: –

docker login

Then we use the docker buildx build command to use Build Cloud: –

docker buildx build https://github.com/dbafromthecold/sqlserver2022.git `
--builder cloud-dbafromthecold-default `
--tag dbafromthecold/sqlserver2022:latest `
--load

Let’s break down what this is doing…

docker buildx build https://github.com/dbafromthecold/sqlserver2022.git
This is saying to use Build Cloud and pull the dockerfile from that repository on Github

–builder cloud-dbafromthecold-default
Use the default builder that is available in Build Cloud

–tag dbafromthecold/sqlserver2022:latest
Tag the build image with a name

–load
Once the image is built, pull it to the local machine (encrypted)

When executed, we can see the image being built and pulled to the local machine: –


N.B. – I built the image a few times to test before taking this screenshot, hence why there are cached layers in the build.

Once complete, we can check our local images: –

docker image ls

And there it is! But hang on, didn’t I say earlier we don’t want to have the image locally? We want to push it to a remote repository!

To to that, we need a remote repository in a registry. I’ve set up one in the Docker Hub for this example here but any other registry will work (just make sure you’re authenticated).

To use Build Cloud and push to a remote registry: –

docker buildx build https://github.com/dbafromthecold/sqlserver2022.git `
--builder cloud-dbafromthecold-default `
--tag dbafromthecold/sqlserver2022:latest `
--push

Only difference here is that we’re using –push instead of –load. And make sure that the tag used for the image matches the repository that you’re pushing to!

When executed, we’ll see something similar to: –

And if we check the repository in the registry: –

There’s the image! Available for our wider team to use or be utilised in a pipeline!

We can even use something like Github actions to trigger the build when we push to the GitHub repo, pretty cool…huh?

Thanks for reading!

EightKB 2024

~

EightKB is back!

The biggest online SQL Server internals conference is back in 2024…it’s all happening on August the 8th!

We’ve open our call for speakers, you can submit here: –
https://sessionize.com/eightkb-2024/

We’re looking for experts, not necessarily expert speakers. If you haven’t presented before we offer mentoring as part of our speaker program to help you prepare for your session so that you can enjoy presenting on the day.

As a speaker this is your chance to really go all out! If you’ve ever wanted to deep dive into a topic, this is the event to do so. No topic is too advanced…you can do as many (or as little or none at all!) demos as you would like. Field questions during the session or respond after the event…completely up to you.

Speakers do not have to use a slide template, and we don’t ask for speakers to add our logo to their deck. We just want you to turn up and enjoy presenting!

After the event, we’ll provide feedback of your session from the attendees and an unbranded video of your session that you can use however you would like.

Hope to see you there!