Author Archives: dbafromthecold

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!

The Docker debug command

~

In the latest version of Docker Desktop a new command has been included call docker debug.

Now this is only available with a Pro Docker licence but it’s an interesting command so I thought I’d run through what it can do here.

Typically when I’m testing SQL Server in containers I build my own images as sometimes I need tools that aren’t available in the sql images from the microsoft container registry.

One example of this is ping. When spinning up containers on a custom bridge network all containers on that network should be able to reference each other by container name (containers on the default bridge network can only communicate by IP address).

In order to confirm (or troubleshoot) connectivity between the containers, I built a custom image with ping installed.

But with the new docker debug command I no longer have to.

What docker debug does is open up a shell to any container (or image) with a whole load of tools available. You can also install more tools without affecting the running container!

So effectively, this replaces the need to exec into a container and install the tools there.

Let’s have a look at this in action. I’m going to spin up two SQL containers (from WSL on Windows) on custom brigde network.

So first thing to do is create the network: –

docker network create sqlserver

And then spin up the containers (using the SQL Server 2022 image in the MCR):-

docker container run -d \
--publish 15789:1433 \
--network sqlserver \
--env ACCEPT_EULA=Y \
--env MSSQL_SA_PASSWORD=Testing1122 \
--name sqlcontainer1 \
mcr.microsoft.com/mssql/server:2022-CU11-ubuntu-20.04

docker container run -d \
--publish 15790:1433 \
--network sqlserver \
--env ACCEPT_EULA=Y \
--env MSSQL_SA_PASSWORD=Testing1122 \
--name sqlcontainer2 \
mcr.microsoft.com/mssql/server:2022-CU11-ubuntu-20.04

Ok, once they’re up and running…let’s use docker debug to connect to sqlcontainer1: –

sudo docker debug sqlcontainer1

Note – using sudo here as it’ll throw an error in WSL without it

And now let’s try to ping the other container by name: –

ping sqlcontainer2 -c 4

Cool! That worked, so I’ve confirmed that my containers can talk to each other by name on my custom bridge network.

Docker debug also comes with a few custom tools, one of which is entrypoint. This let’s us see what the ENTRYPOINT and CMD statements are of the underlying image the container was built from. Let’s check it out with our sql container: –

entrypoint

Nice, ok that’ll be really handy when debugging!

All in all, this is a very useful tool when working with containers. It’ll help keep our container images as small as possible because, let’s be honest, as container images go…the sql server image are huge!

Thanks for reading!

Building a Docker image from a Github repository

~

To build a custom Docker image we create a docker file with instructions in it.

For example, a really simple custom SQL Server 2019 Docker image can be built with: –

FROM mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04

USER root

RUN mkdir /var/opt/sqlserver
RUN mkdir /var/opt/sqlserver/sqldata
RUN mkdir /var/opt/sqlserver/sqllog
RUN mkdir /var/opt/sqlserver/sqlbackups

RUN chown -R mssql /var/opt/sqlserver

USER mssql

CMD /opt/mssql/bin/sqlservr

We then build the image with: –

docker build -t <NEW IMAGE NAME> <PATH TO DOCKERFILE>

But if our dockerfile is hosted in a Github repository, we don’t need to manually pull the repo down and then run the build command.

We can reference the dockerfile in the repository directly in the build command with: –

docker build -t <NEW IMAGE NAME> <URL#BRANCH#PATH>

So for a docker file that’s in my dockerdeepdive repository, in the main branch, located at Demos/CustomImages/Image1/dockerfile: –

docker build -t testimage https://github.com/dbafromthecold/dockerdeepdive.git#main:Demos/CustomImages/Image1

N.B. – Ignore that error on line 2. It’s a known issue but the image has been successfully built.

Thanks for reading!

BUILTIN\Administrators in SQL Server on Linux

~

When I first started working with SQL on Linux one of the first things I did was to remove the default the [BUILTIN\Administrators] login. This is pretty much standard practice with SQL on Windows as we don’t want administrators on the server to have automatic sysadmin rights into the SQL instance.

But this login makes no sense on Linux as there is no administrators group, so it should be dropped…right?

However, there’s a catch. In the Frequently Asked Questions about SQL Server on Linux here: –

Dropping Builtin\administrators for SQL Server on Linux breaks execution of some of the system stored procedures. We suggest to not remove or drop the Builtin\administrator account from SQL Server on Linux/containers.

One of these stored procedures is sp_readerrorlog. Let’s see what happens.

I’m connected into my SQL on Linux instance in SSMS and am going to drop the login: –

DROP LOGIN [BUILTIN\Administrators];

And now try to read the error log: –

EXEC sp_readerrorlog;

And we get the following error: –

Argh! OK, let’s try to recreate: –

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS;

And we get another error: –

That’s pretty much expected but, how can we fix this?

To get the login back, we need to rebuild the system databases. Not ideal I know!

So stop the SQL instance: –

sudo systemctl stop mssql-server

Rebuild the system databases: –

sudo -u mssql /opt/mssql/bin/sqlservr --force-setup

Control+C once the setup has completed and restart the SQL instance: –

sudo systemctl start mssql-server

However, if we now try connecting to the SQL instance: –

Bah, we need to set the SA password again! Stop the instance: –

sudo systemctl stop mssql-server

Set the SA password: –

sudo /opt/mssql/bin/mssql-conf set-sa-password

Btw, if you see an error saying that the SA password was unable to be set, try logging in anyway as sometimes the errors lie! 🙂

EDIT – 2023-12-18 – The false error message is documented here: –
https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes-2022

Thanks Randolph!

Now start the SQL instance again: –

sudo systemctl start mssql-server

And now you should be able to log in and see that the [BUILTIN\Administrators] login is back: –

You will now also be able to run sp_readerrorlog. The sysadmin rights can be removed from that login and sp_readerrorlog will continue to run, so that’s at least one thing that can be restricted.

Something to watch out for when working with SQL on Linux.

Thanks for reading!