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