This post is about running Microsoft SQL Server Express inside a (Windows) container.

However before beginning I would recommend to go ahead an download the MS-SQL Docker image. This because unlike many of the Linux based Docker images, this container is huge (around ~7 GB).

Open a Windows PowerShell window and execute the following command.

PS > docker pull microsoft/mssql-server-windows-express

Depending on your Internet connection speed this could take some time to download an extract the image. Once it has been downloaded and extracted the image would be around 13.8 GB, so who said containers are small again?  😎
 

Pre-requirements

Once the containers with MS-SQL are running we need to have something to manage them. Because it’s Microsoft SQL there is a great free tool available from Microsoft to manage the SQL instance, named Microsoft SQL Server Management Studio.

So go ahead and install the Microsoft SQL Server Management Studio, this will be very helpful once we need to manage the MS SQL containers.
 

Running a MS-SQL container

Alright once the Docker images has been downloaded, you are good to go to start the MS-SQL container.

Starting the container;

Starting the container is very simple, however some settings must be set like setting the password for the SA account (you will need this later) and Accepting the EULA.

PS > docker run --detach --name mssql --publish 1433:1433 --env sa_password=SuperSecretPassw0rd --env ACCEPT_EULA=Y --name mssql microsoft/mssql-server-windows-express

If everything went okay the container should be running, just to make sure the container is running execute the following command.

If you see something similar like here above, congrats the container it is running!

Manage the SQL Server container

Before we are going to use the Microsoft SQL Server Management Studio we need the IP Address and the SA password to be able to login on the MS SQL instance. Because we are running the container locally on the system it would make sense to use something like 127.0.0.1 or localhost, however this does not work on Windows system. So we need to get the IP address of the container. To get the IP address, just execute the following command.

PS > docker inspect -f "{{ .NetworkSettings.Networks.nat.IPAddress }}" mssql

So in my case the IP address is 172.28.15.76, however this could be something else on your system.

Okay no we have the IP address we still need the password that we have used when starting the MS-SQL container. If you didn’t change it the password would be “SuperSecretPassw0rd“.

So let’s start the Microsoft SQL Server Management Studio and fill in all the required information.

Once logged in we could see some of the system databases.

We could get start creating databases however because we have started the container without any persistent storage, any changes would be gone once the container has stopped and removed. So that’s not very handy when it comes to databases.

To make sure that we can store persistent databases we need to stop this instance of MS SQL and create a persistent mount point.

Exit the Microsoft SQL Server Management Studio, and stop the container.

PS > docker stop mssql
PS > docker rm mssql

 

Running a MS-SQL container with persistent storage

Before starting the container with the mount option we need to make sure that there is a directory that the container can use to store data outside the container, in this example I’m using a new directory in C:\mssql-data.

Create a persistent directory

Creating the directory can be done with a simple Windows PowerShell command.

PS > mkdir C:\mssql-data

Let’s go ahead and open the new directory in Windows Explorer, it should be still empty 😉

Running the MS-SQL container with persistent storage

Great now it’s time to start the MS-SQL container with the volume mount option.

PS > docker run --detach --name mssql --publish 1433:1433 --env sa_password=SuperSecretPassw0rd --env ACCEPT_EULA=Y --volume c:\mssql-data:c:\data microsoft/mssql-server-windows-express

As you can see in the command here above Docker will use our directory that we just have created and mount it inside the container as C:\data (we need this directory name later on when creating a new database).

If everything went okay the container should be running, just to make sure the container is running execute the following command.

If you see something similar like here above, congrats the container it is running!
Note that the directory on the Docker host should still be empty, so don’t expect any databases or other files appeared in C:\mssql-data.

Creating persistent databases

Open the Microsoft SQL Server Manager Studio to create a persistent MS SQL database.
Login with the correct IP address and password.

Note that the IP address of the MS SQL container could have been change to something else.

Once logged in, right-click on Databases and click on New Database.

Fill in a name for the database (in this example I’m using “persistent-database”).
Next click on the button (…) below Path.

Select C:\data as the new folder for both the database and transaction files.

Make sure both files are stored in C:\data.

Once you click on OK the database should be created, as shown here below.

Because the database has been created outside the container (trough the volume mapping) it should be visible if you open the directory C:\mssql-data on the Docker host.

No once you stop and remove the container the database and transaction files will still be there, because it’s stored outside the container.

If you start a new MS SQL container instance with the same volume mount, MS SQL would not import the database automatically. Because you need to tell MS SQL to import the database upon starting the container.
 

Starting MS SQL container with an existing database

So let’s start the MS SQL container with an existing database.
It’s basically the same command but now we have added an extra environment option so MS SQL knows where it can find the database it needs to import.

PS > docker run --detach --name mssql --publish 1433:1433 --env sa_password=SuperSecretPassw0rd --env ACCEPT_EULA=Y --env attach_dbs="[{'dbName':'persistent-database','dbFiles':['C:\\data\\persistent-database.mdf','C:\\data\\persistent-database_log.ldf']}]" --volume c:\mssql-data:c:\data microsoft/mssql-server-windows-express

Once you have checked that the container is running, it would be handy to check the log output of the MS SQL container, you can do this with the following command.

PS > docker logs mssql

As you can see here below once MS SQL has been started it will import the “persistent-database” located on the persistent volume.

And when opening the Microsoft SQL Server Management Studio we could see that the database have been imported successfully.

That’s it you are running Microsoft SQL Server inside a Docker container, happy dockering!!


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.