This has been bugging me for a while but I just haven’t had a chance to get around to revisiting it…you can read the original post I wrote here

In that post I came up with a way to attach databases to SQL running in a linux container. There’s an environment variable you can use in windows containers (attach_dbs) that provides the ability to attach database data and log files to a SQL instance running within a container.

Sadly this doesn’t exist for linux containers so the what I came up with was: –

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"

Now this works a treat. It waits ten seconds for the SQL instance to come up within the container and then runs the sqlcmd script below.

The problem with this is, it’s a bit of a hack. The HEALTHCHECK command isn’t designed to run once, it’ll carry on running every 10 seconds once the container comes up…not great.

So, what’s the better way of doing it?

I sat down this weekend and started to write scripts to run the SQL statement above. However I ran into a bit of an issue, each time I ran a script like thus: –

sleep 10s
/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"

The script would error out or I would end up with a dead container. I tried a few alternatives, for example retrying the sql statement until I got a exit code of 0, but no dice.

So what was the fix? Well I was flipping through Bob Ward’s (t) excellent Pro SQL Server on Linux one evening and there was the answer!

The trick is to create two scripts called entrypoint.sh and attach-db.sh

attach-db.sh looks like this: –

sleep 15s
/opt/mssql-tools/bin/sqlcmd -S . -U sa -P Testing1122 \
-Q "CREATE DATABASE [DatabaseA] ON (FILENAME = '/var/opt/sqlserver/DatabaseA.mdf'),(FILENAME = '/var/opt/sqlserver/DatabaseA_log.ldf') FOR ATTACH"

Pretty standard right? Wait 15 seconds and then run the CREATE DATABASE statement.

But the real trick is in the entrypoint.sh script: –

/var/opt/sqlserver/attach-db.sh & /opt/mssql/bin/sqlservr

Huh? The attach-db.sh script is called before the SQL binary??

Yep, even though it’s a bit counter-initiative (well for me anyway), what’s happening here is that the attach-db.sh script is called, starts waiting, and then SQL is spun up. Then once SQL is up and running, the CREATE DATABASE statement is executed.

Containers need a process to be running in order for them to stay up, so without the SQL binary being called after the attach-db.sh script the container will shut down. That’s the issue that I was having (amongst others).

I’ve been playing around with this and ended up with the following dockerfile: –

So the docker looks like this: –

# base this image of the SQL 2017 latest image
FROM microsoft/mssql-server-linux:latest
# make a directory within the container
RUN mkdir /var/opt/sqlserver
# copy attach-db.sh into container
COPY attach-db.sh /var/opt/sqlserver
# copy database files into container
COPY DatabaseA.mdf /var/opt/sqlserver
COPY DatabaseA_log.ldf /var/opt/sqlserver
# use the ENTRYPOINT command to execute the script and start SQL Server
ENTRYPOINT /var/opt/sqlserver/attach-db.sh & /opt/mssql/bin/sqlservr

I’ve removed the entrypoint.sh script and replaced it with ENTRYPOINT command within the dockerfile. The ENTRYPOINT command specifies what will run when the container starts up, so in this case the attach-db.sh script and then SQL Server.

Boom! Once the image is created from the dockerfile, a container can be spun up, and there will be the database!

Thanks for reading!