How to Create a Dev Container for SQL Server 2019

Dev containers are not only for old software. They are also a great help when you plan a migration and need a new technology stack. Let’s look how SQL Server 2019 works nicely with our dev containers.

 

SQL Server 2019 on Linux

If you want to use SQL Server 2019 in a container you need the Linux version. The Windows container version is dead and no longer maintained. Outside of a container the Windows version for SQL Server 2019 is perfectly fine and supported, just not in a container.

 

Create a docker-compose.yaml file

We can use the Docker image from Microsoft and skip the part of creating our own Dockerfile. That allows us to jump directly to the docker-compose.yaml file where we can add this configuration:

I map the SQL Server port outside the container to port 6000 because my port 1433 is already in use. If I connect from within the dev container I need port 1433.

We use the container_db folder as we did with PostgreSQL. Make sure that this folder exists next to your docker-compose.yaml file:

Do not forget to add this line to your .gitignore file:

 

Start SQL Server

As with all other dev containers we can run this command to start SQL Server:

 

Restore a database

SQL Server needs to run before we can do something useful. That is the reason why we need a separate script to create our database and cannot put it into a Dockerfile.

Download the AdventureWorks sample database from GitHub and put it in the folder container_db.

In the same folder we need a restore.sql script with these commands:

We now can connect to the SQL Server container, go to the mapped folder and run our script:

The password is in the environment variables and so we do not need to type it in. If everything works we should get an output like this:

Changed database context to ‘master’.
5 percent processed.
10 percent processed.
15 percent processed.
…
100 percent processed.
Processed 26344 pages for database ‘AdventureWorks2019’, file ‘AdventureWorks2017’ on file 1.
Processed 2 pages for database ‘AdventureWorks2019’, file ‘AdventureWorks2017_log’ on file 1.
RESTORE DATABASE successfully processed 26346 pages in 4.227 seconds (48.692 MB/sec).

 

Connect with SQL Server Management Studio

SQL Server Management Studio has its own way to separate hostnames and ports. Instead of the usual : it uses a , (comma):

Server name:	localhost,6000
Authentication:	SQL Server Authentication
Login:	sa
Password:	P@ssword123

If you enter the correct credentials you can access your AdventureWorks database in the container:

 

Next

SQL Server works great with .Net. Next week we create a .Net 6 dev container and connect it to our SQL Server.