SQL Server on Docker

Ease of having containerized SQL server for isolated programming

Often programming teams use a single SQL server for their coding work. This limits how much a developer can find creative solutions for the problem at hand. What if we can isolate a copy specifically for the developer, and maintain the integrity of the SQL server for the rest of team? What if we do not need to install SQL server instance running on your local computer? SQL Server can be run on any platform by leveraging container technology. Let’s look at how to setup a SQL Server database inside of a Docker container.

Prerequisites:

1. Docker Desktop
2. Powershell

Install docker desktop:

Install Docker Desktop on Windows

Let’s get started:

With the use of only 2 docker commands, we can have a localhost instance of SQL server to connect to.

The first command pulls mssql image to your local container.

docker pull mcr.microsoft.com/mssql/server:2019-CU8-ubuntu-16.04

The 2nd command creates a local DB with the name my-local-db. Now you can connect to the database using SQL Server management studio.

docker run -d -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Str0ngPwd" --name "my-local-db" -p 1433:1433 mcr.microsoft.com/mssql/server:2019-CU8-ubuntu-16.04
docker ps -a

As you can see, you have a container hosting your SQL DB, locally.

Connect to localhost:

To view your container images Visual Studio provides container window

To connect using SQL server management studio:

Now you can use this as a regular SQL Server, and execute CRUD operations. One thing to remember though, if the containers are removed, the data is lost. If you wish to use this as connection string in your applications:

"DbContext": "Data Source=tcp:localhost,1433;Initial Catalog=my-local-db;Persist Security Info=True;User ID=SA;Password=Str0ngPwd"

In Conclusion:

SQL Server can be run on any platform by utilizing container technology. We can drop and recreate tables, databases, functions, stored procedures. This is specially useful to debug docker DB related issues on pipelines. We can load the exact script used in pipelines for unit tests into the local DB and identify the issues with Unit test tasks.

For more SQL image versions and tags on docker: click here