Setting up Docker for testing – Setting up 3 SQL servers Manchester / Glasgow / Edinburgh

There are a lot of resources out there about Docker, I am just going to cover the steps I ran to get a test environment I used to present with. These steps created 3 different SQL server environments (Glasgow, Manchester, and Edinburgh). I had previously used other names for other presentations, call them anything you want.

  1. Download Docker Desktop for Windows and install.

2. You need to create user and email for Docker.

I did have one issue with logging into docker, I was able to log into Docker using my email address, it accepted it fine, but when running docker login to login to docker at the command prompt I got the following error

docker login

I logged out and back in using my user name and all was good

Docker user

3. Next step is to download the SQL server image you want to use

docker pull microsoft/mssql-server-linux:2017-latest

docker sql install

This will take a while but you only need to do this once for each version of SQL server you want. You might notice I am using Powershell, I can also use CMD and will switch between the two in the demos.

Now we can create our SQL Servers

docker run -e “ACCEPT_EULA=Y“

-v sqlvolume:/var/opt/mssql

-e “SA_PASSWORD=Passw0rd”

-p 12435:1433 –name glasgow

-d –hostname glasgow

microsoft/mssql-server-linux:2017-latest

I’ll go through each line above

docker run -e “ACCEPT_EULA=Y“

Docker is the command you need to run for all the docker statements the ACCEPT EULA is the same as if you were installing SQL manually and accepting the license agreement.

 -v sqlvolume:/var/opt/mssql

This is specifying a sqlvolume location, you might notice that its a Linux format as its a Linux image but we will be connecting from a Windows 10 machine.

 -e “SA_PASSWORD=Passw0rd”

The SA password with a simple password. To lock down the password takes more of an effort and out of the scope of this blog as I will show you how you can see what the password is set to in a further step

 -p 12435:1433 –name glasgow 

We now need to set the port that SQL will communicate on, you might notice the 1433 value which is SQL servers default port. The 12435 will be used to distinguish each different server, this value will be different for Edinburgh and Manchester.

 -d –hostname glasgow

The –hostname bit is quite important for the demo as without this you can still connect to the server by name but running a SELECT @@servername will bring back a gibberish value which isn’t great when I want to pull back geographical server names.

microsoft/mssql-server-linux:2017-latest

And finally what we are wanting to run.

If we then run the whole command, within seconds we have SQL server up and running.

To confirm this  we can run

docker ps -a

Docker started

This shows the containers running, what they are running, the ports and the names of the server.

There are a couple of tweaks required to allow me to connect through the SSMS ( and Azure data studio) on the servername.

To allow return from ping (as I demoed I could ping it) you need to run as administrator the following to create a static route

route add 172.17.0.0 MASK 255.255.0.0 10.0.75.2

I also mentioned you can see the SA password that has been set, if you run

docker inspect manchester

it prints out the SQL configuration as you can see in clear text is the SA password

docker passwordand also the IP Address detailsdocker ipaddressI could have used SQL alias to create a redirect of a DNS name to point to that IP Address, however, I planned on using Azure data studio and aliases don’t work, this is due to it being a new application build from the ground up (utilising Visual Studio Codebase)

There are other ways I could have achieved what I wanted ( creating a network for example) but decided on setting the hostname to IP address in the Lmhosts file for simplicity.

This file is located in C:\Windows\System32\drivers\etc and called lmhosts with no extension. To edit in a text editor you will need to run as administrator.

The entry in the file looks like

172.17.0.2 glasgow #PRE
172.17.0.3 edinburgh #PRE
172.17.0.4 manchester #PRE

Once saved you can refresh the Lmhost file running  (and yes its upper case R)

netstat -R

So now in Management studio, you should be able to connect to Manchester, Glasgow or Edinburgh

docker -manchester

Also As the sqlvolume was added the data is persisted and stopping and removing the docker container still retains the data if you recreate.

So there we have it 3 SQL environments spun up in no time at all.

This entry was posted in Azure Data Studio, DataScotland, Docker, Management Studio and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s