Run your SQL Server MDF file inside a linux Docker Container
by Mauricio Rojas, on May 10, 2020 8:19:27 PM
A few months ago we setup a demo for a VB6 Legacy Modernization to Azure for Microsoft as part of their Tailwind set of examples.
This demo migrates the VB6 app all the way to an app that can be published to Azure.
This works fine in Windows, but lately (especially in COVID times) I have been doing a lot of remote work and I like to be able to run everything from my mac, so I decided to create a branch of this repo that could run on .NET Core and that could run on any mac or Linux.
So far so, so good. The WebMAP migration tool generates a nice ASP.NET Core App so I just cleaned the code a little, and got it compiling on my mac in no time. I made a couple of changes: I removed the MSXML dependency and changed it to HttpClient and the GetProfileString PInvoke was replaced by the Ini-Parser nuget. My only issue there was that the GetProfileString API is case insensitive and the IniParser is not.
But when I got to run it, I got an exception. I had forgotten the DB.
The legacy VB6 App used an Access MDB. On this repo we provide some guidance on how to use Microsoft SQL Server Migration Assistant to migrate from Access to SQL Server. So I just followed those steps and I got a nice SQL Server .mdf and .ldf file.
After that all that was needed was a nice Dockerfile. I thought I could get it from google right away but no. It was not that easy. After some research I found two great references:
The first one explains some different ways of using the SQL Server Docker Images especially how to create custom Docker files. And the second one gives some tips on how to attach your .mdf when you start your container.
Luckily my wife was busy with Pokemon Shield. She is currently fighting in Sir Chester Gym or something like that. The good thing is that gives geeky guy a free pass to do geeky stuff 😀😀.
Ok. So finally I came up with a Dockerfile like this:
COPY attach_db.sh /posdb/
COPY POS.mdf /posdb/
COPY POS_log.ldf /posdb/
RUN chmod +x /posdb/attach_db.sh
RUN (/opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started"
ENTRYPOINT /posdb/attach_db.sh & /opt/mssql/bin/sqlservr
Notice the attach_db.sh in the ENTRYPOINT this script is started at the same time as the sqlservr, but it just sleeps for a while before attaching the database:
/opt/mssql-tools/bin/sqlcmd -S . -U sa -P Password1$ \
-Q "CREATE DATABASE [POS] ON (FILENAME ='/posdb/POS.mdf'),(FILENAME = '/posdb/POS_log.ldf') FOR ATTACH"
I really like this solution because I can just build my image:
docker build -t orellabac:POSDB .
orellabac:POSDB is just the tag for the database. orellabac is my github account, but can use any name here.
And I can start it with:
docker run -p 1433:1433 orellabac:POSDB
I recommend that you use something like Portainer to manage your containers and images. The command line is great but the Portainer UI is awesome.
And if you want to check out of if your docker container work you can just use the Microsoft SQL Server extension.