Last week I presented my session on SQL Server & Containers for the PASS Virtualization Group and during my prep I noticed that there’s some functionality available to Windows containers and not Linux containers.

One of the (if not the) main benefits of working with SQL in a container is that you can create a custom image to build container from that has all of your development databases available as soon as the container comes online.

This is really simple to do with Windows containers. Say I want to attach DatabaseA that has one data file (DatabaseA.mdf) and a log file (DatabaseA_log.ldf): –

ENV attach_dbs="[{'dbName':'DatabaseA','dbFiles':['C:\\SQLServer\\DatabaseA.mdf','C:\\SQLServer\\DatabaseA_log.ldf']}]"

Nice and simple! One line of code and any containers spun up from the image this dockerfile creates will have DatabaseA ready to go.

However this functionality is not available when working with Linux containers. Currently you cannot use an environment variable to attach a database to a SQL instance running in a Linux container.

This was a problem for me as I wanted to change things up a little for the Virtualization Group’s webinar. I wanted to show all the code in my slides running on Windows Server but do my demos on my Windows 10 desktop but working with Linux containers. I wanted to do this as I thought it would be cool to show how you can work with SQL on Linux from Windows.

I started doing some research online and there are different work arounds to attaching the database into SQL in a Linux container but they all involved separate scripts outside of the dockerfile. I wanted to keep things simple, only show minor changes from Windows containers so I had to get a bit creative.

Here’s what I came up with: –

HEALTHCHECK --interval=10s  CMD /opt/mssql-tools/bin/sqlcmd -S . -U sa -P Testing11@@ -Q "CREATE DATABASE [DatabaseA] ON (FILENAME = '/var/opt/sqlserver/DatabaseA.mdf'),(FILENAME = '/var/opt/sqlserver/DatabaseA_log.ldf') FOR ATTACH"

A bit more involved but it performs the same functions as the attach_dbs environment variable in the dockerfile for Windows containers. Here’s what each part of the code does: –

# Instruct docker to wait for 10 seconds (to allow SQL to initialise) and then perform a check to ensure the container is running as expected
HEALTHCHECK --interval=10s
# Use sqlcmd to connect to the SQL instance within the container
CMD /opt/mssql-tools/bin/sqlcmd -S . -U sa -P Testing11@@
# Runs a SQL script to attach the database
-Q "CREATE DATABASE [DatabaseA] ON (FILENAME = '/var/opt/sqlserver/DatabaseA.mdf'),(FILENAME = '/var/opt/sqlserver/DatabaseA_log.ldf') FOR ATTACH"

So that’s how you can get the same result, an image in which you can create containers with DatabaseA available on startup, whether you are working with Linux or Windows containers by running: –

docker build -t demoimage <pathtodockerfilelocation>

If you want to see the full dockefiles, I’ve made both the Windows and Linux versions that I use for my demos available on my GitHub here.

Thanks for reading!