Skip to content

SQL Development Made Easy with Docker

These notes are taken from the YouTube video "SQL Development Made Easy with Docker - Install SQL in 5 minutes or less!" By Tim Corey. This is using most of the stuff I normally do anyway.

It takes about 5 minutes to get SQL up and running within Docker. Additionally, using Docker is a good approach to test backups of SQL data.

Getting Started

Docker Desktop (Linux) must be installed and running.

The official image for Microsoft SQL Server can be found at https://hub.docker.com/r/microsoft/mssql-server. This page will tell you how to download and install the latest images.

The sample AdventureWorks database can be downloaded from https://github.com/microsoft/sql-server-samples/releases/tag/adventureworks, for this example download the latest from the section AdventureWorksLT (Lightweight) full database backups.

Copy this download to a new folder on your C: drive created for this demo, and open up a PowerShell terminal in that folder.

Creating a SQL Server Image

To start a new SQL Server container from an official image, type the following in the terminal:

docker run -e "ACCEPT_EULA=Y" -e 'SA_PASSWORD=Pwd12345!' -p 11433:1433 --name 'DockerDemo' -d mcr.microsoft.com/mssql/server

The following parameters are used:

Parameter Description
-e "ACCEPT_EULA=Y" Automatically accepts the End User Licence Agreement.
-e 'SA_PASSWORD=Pwd12345!' Sets the password to use for the System Administrator (sa) account.
-p 11433:1433 Listen externally on port 11433 and map this to the standard SQL server port of 1433 internally - this prevents this instance clashing with other running instances on this machine that may be usinbg the default external port (or local default installations).
--name 'DockerDemo' Gives the container a name of DockerDemo so it can be easily identified.
-d Run in disconnected mode - don't listen for commands from this terminal instance. Just start the container and let it go.
mcr.microsoft.com/mssql/server The image to create this container from, as found from the Docker Hub page listed above.

Once this command has finished an Id will be displayed, for example:

5f1f3500ff0a80e319747bd016e561470354c69385a8f008acc7fa38951ccd00

Connecting SQL Server Management Studio to our Image

With this completed we can connect via SQL Server Management Studio remembering to specify our non-standard port after a comma (,) rather than a colon as we would normally do:

SSMS Docker Server Connection Properties

Manipulating the Image

We can see the local container list by issuing the command:

docker ps -a

Which will show something like:

Containers List

We can stop the container by running a stop cvommand with a partial section of the Container ID:

docker stop 5f1f3

And then remove the container with the command:

docker rm 5f1f3

To restore the container, we run the original docker run command again (The container ID will be different).

This is a super fast way of creating a new local SQL Server instance that uses no resources when not running (other than less than 2Gb of storage space).

Restoring a database backup to the image

You can also restore a database backup with docker. We will do this by creating a dockerfile to build a new image, which must be located alongside the downloaded backup from above:

FROM mcr.microsoft.com/mssql/server AS build
ENV ACCEPT_EULA=Y
ENV SA_PASSWORD=Pwd12345!

WORKDIR /tmp
COPY AdventureWorksLT2022.bak .
COPY restore-backup.sql .

RUN /opt/mssql/bin/sqlservr --accept-eula & sleep 10 \
    && /opt/mssql-tools18/bin/sqlcmd -C -S localhost -U SA -P "Pwd12345!" -i /tmp/restore-backup.sql \
    && pkill sqlservr

FROM mcr.microsoft.com/mssql/server AS release
ENV ACCEPT_EULA=Y

COPY --from=build /var/opt/mssql/data /var/opt/mssql/data

NOTE:

  • WORKDIR /tmp will set the working directory in the image, not on the local machine.
  • AdventureWorksLT2022.bak is the AdventureWorks backup file we downloaded above. The path is relative to where this dockerfile is, so ensure it is saved alongside this backup since we are not specifying a path.
  • You may need to increase the sleep seconds time from 10 for a slow machine.
  • The second FROM creates a new image without our restore SQL file (which contains our password!) and backup - it just has the restored database (ldf & mdf files).
  • I had to change the path for sqlcmd slightly as the location changed.
  • I had to add the -C parameter to trust the server certificate.

We also need to create a SQL file (which we will call restore-backup.sql) alongside the other files, to actually perform the restore:

RESTORE DATABASE [AdventureWorks] FROM DISK = '/tmp/AdventureWorksLT2022.bak'
WITH FILE = 1,
MOVE 'AdventureWorksLT2022_Data' TO '/var/opt/mssql/data/AdventureWorks.mdf',
MOVE 'AdventureWorksLT2022_Log' TO '/var/opt/mssql/data/AdventureWorks.ldf',
NOUNLOAD, REPLACE, STATS = 5
GO

SQL TIP: If you don't know them, you can find the logical names in a backup by running the SQL command below against the backup:

RESTORE FILELISTONLY FROM DISK = 'C:\[Path]\AdventureWorksLT2022.bak`

Our file folder will look like this now:

Files

Now to build the image from the dockerfile, run the following in the terminal:

docker build -t restored-db .

NOTE: If something doesn't work, try running this command instead to keep all the progress messages in the console:

docker build --no-cache --progress=plain -t restored-db .

This has now created a new image (not container) with the restored data (restored-db at the top of the list):

Image List

So now the image has been created we can create a running container:

docker run -p 11433:1433 --name AdventureWorks -d restored-db

We do not need to specify any environmental variables as they have already been added to the image via the dockerfile.

We can now connect to the running container via SQL Server Management Studio and see the database has been restored as expected, and run the usual SQL queries against the data:

AdventureWorks Database in SSMS

Sanitising Data

Suppose we have data in the restored database that we don't want to have as part of the image (for example if the original data has real telephone numbers, but we want to give this image to developers to develop and test against). In this case we can perform additional SQL on the data as part of the restore SQL. In a new SQL file restore-and-sanitise.sql we start with the previous SQL, but add commands so it looks like this:

RESTORE DATABASE [AdventureWorks] FROM DISK = '/tmp/AdventureWorksLT2022.bak'
WITH FILE = 1,
MOVE 'AdventureWorksLT2022_Data' TO '/var/opt/mssql/data/AdventureWorks.mdf',
MOVE 'AdventureWorksLT2022_Log' TO '/var/opt/mssql/data/AdventureWorks.ldf',
NOUNLOAD, REPLACE, STATS = 5
GO

USE AdventureWorks;
GO

UPDATE SalesLT.Customer
SET EmailAddress = FirstName + '@imorital.com',
    LastName = 'Customer',
    Phone = FORMAT(CAST(LEFT(CAST(ABS(CAST(CAST(NEWID() as BINARY(10)) as INT)) as VARCHAR(max)) + '0000000000', 10) as BIGINT), '###-###-####'),
    PasswordHash = '',
    PasswordSalt = '';

and update the dockerfile accordingly:

FROM mcr.microsoft.com/mssql/server AS build
ENV ACCEPT_EULA=Y
ENV SA_PASSWORD=Pwd12345!

WORKDIR /tmp
COPY AdventureWorksLT2022.bak .
COPY restore-and-sanitise.sql .

RUN /opt/mssql/bin/sqlservr --accept-eula & sleep 10 \
    && /opt/mssql-tools18/bin/sqlcmd -C -S localhost -U SA -P "Pwd12345!" -i /tmp/restore-and-sanitise.sql \
    && pkill sqlservr

FROM mcr.microsoft.com/mssql/server AS release
ENV ACCEPT_EULA=Y

COPY --from=build /var/opt/mssql/data /var/opt/mssql/data

Stop and delete the container from before (if running). You can add a new version to the new image when we now build it:

docker build -t restored-db:1.0.1 .

Now run with:

docker run -p 11433:1433 --name AdventureWorks -d restored-db:1.0.1

Then in SQL Server Management Studio you can query the database table and see the data has been sanitised as expected.