Running SQL Server in a container with sqlcmd

One of the options that I completely missed with the new go-sqlcmd CLI tool is the ability to spin up a container running SQL Server.

Let’s have a look at how it works. It’s nice and easy to install with winget package manager tool: –

winget install sqlcmd

Once installed we can easily run a container (make sure your container runtime is up, in my case I’m using Docker): –

sqlcmd create mssql --accept-eula

Note that we still have to accept the end user license agreement, like when running a container via Docker. Thankfully we can set an environment variable now so that we don’t have to include –accept-eula every time we run a container: –

[System.Environment]::SetEnvironmentVariable('SQLCMD_ACCEPT_EULA','YES', 'Machine')

Let’s have a look at the container it’s spun up: –

docker container ls

Pretty standard…the output of the sqlcmd create command told us that it was using the latest image tag but we can also see that the container has port 1435 on the host mapped to port 1433 in the container. This is so that multiple containers can be run on the same host.

There’s a command to see what images are available: –

sqlcmd create mssql get-tags

Which will give us a big long list of all the tags available for SQL Server in the Microsoft Container Registry (MCR).

If we want to run a container from a different image, we can do this: –

sqlcmd create mssql --tag 2022-CU1-ubuntu-20.04

Cool! And if we want to query that instance we can just say: –

sqlcmd query "SELECT @@VERSION"

Good stuff. OK, when we created the containers the output mentioned a context file, created in our user home directory. Let’s have a look: –

cat C:Usersdbafromthecold.sqlcmdsqlconfig

accept_eula: "YES"
contexts:
    - context:
        endpoint: mssql
        user: dbafromthecold@mssql
      name: mssql
    - context:
        endpoint: mssql2
        user: dbafromthecold@mssql2
      name: mssql2
currentcontext: mssql2
endpoints:
    - asset:
        container:
            id: 5b4118f61d6e68ae7c9a92d3b6f163e40053d84a2e4d27ec4f710cc01fde4949
            image: mcr.microsoft.com/mssql/server:latest
      endpoint:
        address: 127.0.0.1
        port: 1435
      name: mssql
    - asset:
        container:
            id: 2d694f1b1517fd4e6145855ce63df2b8c624ee5dbe81a58227b9d6f0249d91d1
            image: mcr.microsoft.com/mssql/server:2022-CU3-ubuntu-20.04
      endpoint:
        address: 127.0.0.1
        port: 1436
      name: mssql2
users:
    - authentication-type: basic
      basic-auth:
        password: M3ckVDNWKlgkNiE2UHdMNkghdDRZWkszJVRTMiFKIU9DVSRMV2pyMnBHIyNGIyoySTc=
        password-encryption: none
        username: dbafromthecold
      name: dbafromthecold@mssql
    - authentication-type: basic
      basic-auth:
        password: N1AqVTFQJFlAciQzWjVRNlkqMTI4S0U1I1dUNSNLblpVekBGTSUkNDdUISRNKmY3QTQ=
        password-encryption: none
        username: dbafromthecold
      name: dbafromthecold@mssql2
version: v1

In here we can see the details (image, port) of the containers that we spun up. Exactly like working with Kubernetes, sqlcmd uses contexts to connect to different instances.

So if we want to switch to the original container that we spun up: –

sqlcmd config use-context mssql
sqlcmd query "SELECT @@VERSION"

One thing I did notice about the config file was these lines for each container: –

basic-auth:
        password: M3ckVDNWKlgkNiE2UHdMNkghdDRZWkszJVRTMiFKIU9DVSRMV2pyMnBHIyNGIyoySTc=
        password-encryption: none
        username: dbafromthecold

So the password for our custom user is not encrypted by default. Which means if you want to get the password created for the user, it just needs to be decoded. The password can be encrypted, it’s one of the options that we have when running a container.

To view all the options, run: –

sqlcmd create mssql --help

There’s a whole bunch of options there! We can change the container name, port, etc. but the option that interests me the most is the –using flag.

There’s an example in the docs for this: –

sqlcmd create mssql --accept-eula --using https://aka.ms/AdventureWorksLT.bak

And then, another cool feature, is that we can then open Azure Data Studio and connect to the container: –

sqlcmd open ads

Nice!!! We can also do this with .bak files stored in Github. I have a testdatabase.bak in a repo called testrepository (imaginative naming, I know). To use that we would run: –

sqlcmd create mssql --using https://raw.githubusercontent.com/dbafromthecold/testrepository/main/testdatabase.bak

And then connect in ADS again: –

sqlcmd open ads

And there it is! OK, not going to be quite as quick and easy if that’s a large database but hey, if you’re planning on running large databases in SQL then containers probably aren’t the best solution. Although could possibly be an option in a future release to mount a database from the host?

Another option I’d like to see is the ability to retain data changes from one container to another. This could be tricky however as sqlcmd does seem to be designed to be container runtime agnostic…but there are already checks in place to ensure that we don’t lose data.

If we try to delete a container that has a custom database in it: –

sqlcmd delete

We get an error! So we have to go and remove the database manually before the container can be dropped.

Oh, and make sure you’re in the correct context before dropping anything!

Thanks for reading!