SQL SERVER – How to Get Started with Docker Containers with Latest SQL Server?

Just last week, I had to present at SQLBits in Manchester, UK and I used Docker Containers for my SQL Server Presentations. I am an experienced presenter and I usually practice multiple times before I get on the stage to present on any subject. However, a day before the presentation, due to some reason, my docker container did not work and I had to redo my entire container. This is where the problems started for me.

Due to some reasons, my docker container did not start up and I had to restart from the beginning. Here is a brief tutorial if you are a total newbie to Docker Container. Thank you Cathrine Wilhelmsen for inspiring me to write this blog post.

Step 1: Install a Docker Container

This is one of the most important steps and I will say this is very easy as well. First, go to the Docker Store and download the Docker for Windows Community Edition (CE). You will have to register for the store to download the image. Once you download the msi file, you can install the Linux containers on your windows edition. I was initially very much worried if I will be able to run this or not but trust me you do not need any Linux knowledge to get started.

Once the installation is complete, you will have to log out and log back in. Additionally, if you have not enabled HyperV or Containers, it will ask you to do so. Just click on Ok and move to the next step.

It is quite possible that your system may ask you to restart – go ahead and restart your system.

Once you restart your system, it will ask you to log in, please use the same username and password which you used to download the docker MSI.

If after restart your docker services do not come up, you can go ahead and start it manually from Windows Start Menu.

Once the docker service is running you will see the prompt on the toolbar and upon clicking it, you will see it the screen similar to the following:

Well, now you are all set with the Docker, next, we will move to install SQL Server on it.

Step 2: Install SQL Server on Docker.

This is the second most important step. To install SQL Server on the docker, you need to have a link to the image to install SQL Server. Here is what I did to install the latest SQL Server install.

First, open a PowerShell Prompt as “Run as Administrator“. Next, run the following command to pull the latest SQL Server Image.

While I wrote this blog post the latest image of SQL Server was 2019-CTP2.3 so I will pull that image. While you install SQL Server, please make sure that you use the latest image.

docker pull mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu

Once the image is downloaded, you can check if the image is downloaded or not by running the following command:

docker images

Now run the following command to install SQL Server into your docker:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Pass@Word1" `
-p 11143:1433 --name sql19-23 `
-v D:\Docker:/sql `
-d mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu

Please note, that the installation you do will be very quick and it will set a password for the SA account as Pass@Word1.

Additionally, it will install your SQL Server to hear on the port 11143. You can change that to any other port if you wish to.

The next step is to start the SQL Server in my case, I have named it – sql19-23 but you can name it any name you prefer.

You can start the command by running the following command.

docker start SQL19-23

If due to any reason, your SQL Server stops, you can restart it by running the following command as well.

docker restart SQL19-23

You can also check with the following command what are the services which are currently running in your docker container.

docker container ls -a

3. Connecting to SQL Server via SSMS

Now you have successfully installed SQL Server on your Docker Container, let us try to connect to the SQL Server with our favorite tool – SQL Server Mangement Studio (SSMS).

Open SQL Server Management Studio and type the server name as localhost,11143 and username name as sa along with the password Pass@Word1. Please note that all the information are the same which we had provided when we installed SQL Server on the Docker Image.

Click on the Connect and you will be able to successfully connect to the SQL Server Mangement Studio.

4. Installing Sample Databases

I use my docker images for my SQL Server Performance Tuning Practical Workshop and I quite often use the SQL Server Sample databases for my demonstrations.

Before you install sample databases, you can go to settings of your Docker installation and check that if your drive is shared between your Windows and Linux. In my case if you notice that I have specified that I map my D Drive to /sql drive when I install the SQL Server via PowerShell. I enabled that in my Docker settings just to be sure as well.

While we are in the settings area, let me also share the other settings for my machine related to memory and CPU. Please note that these settings can be very much different for your system.

Now you can download the sample databases from the link here and install via following commands.

Install AdventureWorks

RESTORE DATABASE [AdventureWorks2014]
FROM DISK = N'/sql/AdventureWorks2017.bak'
WITH FILE = 1,
MOVE N'AdventureWorks2017' TO N'/var/opt/mssql/data/AdventureWorks.mdf',
MOVE N'AdventureWorks2017_log' TO N'/var/opt/mssql/data/AdventureWorks_log.ldf',
NOUNLOAD, STATS = 5
GO

Install WideWorldImports

FROM DISK = N'/sql/WideWorldImporters-Full.bak'
WITH FILE = 1,
MOVE N'WWI_Primary' TO N'/var/opt/mssql/data/WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'/var/opt/mssql/data/WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'/var/opt/mssql/data/WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1',
NOUNLOAD, STATS = 5
GO

You can further expand your SQL Server Management Studio’s Object Explorer and see the installed Sample Database.

5. Cleaning Up Docker

Well, so far everything you have seen is related to installing SQL Server on the Docker but due to any reason, you want to clean up your installation of SQL Server on Docker you can follow the command listed here.

Here are the four important commands:

The following command will list all the active containers:

docker container ls -a

The next command will stop the container which you are running.

docker container stop {insert here the containerid}

The next command will remove the container which you are running.

docker container rm {insert here the containerid}

If due to any reason, you want to remove all the containers which are not running, you can run the following command. However, there is no undo button for the following command so make sure you know what you are doing.

docker system prune --all --force --volumes

Well, that’s it. Let me know if you have any further questions or additional notes, I should incude in this blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com)