- You are either running Windows Containers on Windows 10 Anniversary Edition or Windows Containers on Windows Server 2016
- You have installed Docker for Windows
- You have enabled the Containers Windows feature
- You have installed SQL Server Management Studio (SSMS)
Installing SQL Server Express Docker ContainerRight-click on the whale docker icon in the tray and make sure it is in Windows Container mode. This is the case if it displays “Switch to Linux containers…” as shown below:
Open a command prompt and enter the following command:
docker run -d -p 1433:1433 --env sa_password=Sql!Expre55 --env ACCEPT_EULA=Y microsoft/mssql-server-windows-express
The switches used with the above docker command are explained below:
|-p||maps the container port number 1433 to the host computer’s port number. Needless to say, port 1433 is default for SQL Server|
|-d||instructs docker to run in detached mode in the background|
|--env||allows you to pass an environment variables into the container. We have two environment variables – namely: sa_password and ACCEPT_EULA. The password entered above is Sql!Expre55. Feel free to change the password to whatever you want. Just make sure that the password complies to the Microsoft SQL Server Strong Password Requirements. Failure to do so will result in “Invalid Password” errors when you attempt to login.|
The container that we are downloading from Docker Hub is named microsoft/mssql-server-windows-express and is substantial in size. Be patient as it may take some time to download.
You will know that it is completed when you are returned to the terminal prompt. At this point, you can access and use the database server in three ways:
- You can use sqlcmd in windows authentication mode inside the container
- You can use sqlcmd in SQL Server authentication mode inside the container
- You can access the Docker-zed SQL Server database externally. We will use SQL Server Management Studio (SSMS) to access the database externally.
Using sqlcmd in windows authentication mode inside the container
Before we can access the database, we need to determine the container_id. This is done by typing the following docker command:
docker psThis displays information about the running container:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
a66847266360 microsoft/mssql-server-windows-express "cmd /S /C 'powers..." 8 minutes ago Up 8 minutes 0.0.0.0:1433->1433/tcp jolly_shaw
It suffices to grab the first three characters of the container_id. In the above example, this is a66. The next step is enter a sqlcmd prompt inside the container using windows authentication. This is done with the following docker command that uses the correct container_id:
docker exec -it a66 sqlcmdThe –i switch is for interactive and –t is for pseudo-TTY.
You will see a window that looks like this:
Enter the following pair of commands to display existing databases:
EXEC sp_databasesThe output will look like this:
Type exit to close the sqlcmd terminal window.
Using sqlcmd in SQL Server authentication mode inside the containerUsing the same container_id as before, type in the following docker command to get into a sqlcmd prompt:
docker exec -it a66 sqlcmd -S. –UsaThe –S switch points to the current server name and –U is for username.
When prompted for the password, enter the password that you entered when the container was first created. In the above example it is Sql!Expre55.
You will see a very similar prompt as in the previous example when Windows authentication was used.
Using SQL Server Management Studio to access the database externallyWe will need the IP address of our SQL Server Docker container if we are to access it externally. You can obtain the IP address by using the “docker inspect” command with the appropriate container_id. In my case, I entered the following command:
docker inspect a66This displays configuration data regarding the SQL Server Express container:
Grab the IP address from the Networks >> nat section as shown above. In my case, the container’s IP address is 172.27.237.190.
If all is well, you will be connected to the database.
CleanupTo stop the container, you can enter the following docker command with the appropriate container_id:
docker rm -f a66If you want to permanently delete the SQL Server Express image then you must determine the image_id first with this command:
docker imagesOnce you determine the image_id, the following docker command will permanently delete the image from your computer:
docker rmi d5cReferences: