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:

version: "3.2"
services:
  db:
    image: mcr.microsoft.com/mssql/server:latest
    volumes:
        - ./container_db:/var/opt/mssql/data
    environment:
        ACCEPT_EULA: Y
        SA_PASSWORD: P@ssword123
    ports:
        - 6000:1433

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:

mkdir container_db

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

/container_db/

Start SQL Server

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

docker-compose up

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:

USE [master]
GO

CREATE DATABASE [AdventureWorks2019]
GO

RESTORE DATABASE [AdventureWorks2019] 
FROM  DISK = N'/var/opt/mssql/data/AdventureWorks2019.bak' 
WITH   
MOVE N'AdventureWorks2017' TO N'/var/opt/mssql/data/AdventureWorks2019.mdf',  
MOVE N'AdventureWorks2017_log' TO N'/var/opt/mssql/data/AdventureWorks2019_log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5

GO

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

cd /var/opt/mssql/data
/opt/mssql-tools/bin/sqlcmd -S 127.0.0.1,1433 -U SA -P $SA_PASSWORD -i ./restore.sql

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:

Your local SSMS connects to your database in the container