Getting Started with SQL Server in a Docker Container

You’ve probably heard of running SQL Server in a Docker container. The technology has been around for some years now. However it’s still unexplored territory for many.

A SQL Server Docker container image is a lightweight and standalone package that includes everything needed to run SQL Server. It’s often used in DevOps as part of the Continuous Integration / Continuous Deployment (CI/CD) processes. Using containers, SQL Server systems can be quickly and easily configured and reconfigured programmatically for dev, test, QA, and demo environments.

Using SQL Server in a Docker Container

The first experience many of have with SQL Server in a Docker container is setting up a development or demo system on a laptop or desktop. The process can initially seem a bit foreign so let’s walk through it here.

Install the Docker Engine

The first step is installing the Docker Desktop. There are versions of the Docker Desktop available for Windows, Mac, and Linux. Choose the one that’s right for you and download it. I’m installing on Windows 10 Enterprise in this example.

There are plenty of good how-tos on the internet for installing Docker. So, I won’t go into much detail here other than to highlight a couple of potential gotchas.

Once downloaded, run the Docker Desktop Installer.exe file. It may take several minutes to unpack and load everything it needs.

After the install program has completed, you’re prompted to restart your Windows computer. I frequently do not worry with restart messages. However, in this case, it is usually needed. If you launch the Docker Desktop app without a restart, you may get this Docker Desktop – Access denied message. A restart will typically resolve it.

Also, if your Docker Engine will not start, you may have to update the Linux kernel update package for WSL.

Pull the SQL Server Docker container image

Once your Docker Engine is running, use the following Windows command to pull, or download, the latest SQL Server Docker container image.

docker pull mcr.microsoft.com/mssql/server:2019-latest

Run the SQL Server Docker container

Now that we have Docker Desktop installed and the latest SQL Server image downloaded, it’s time to run the SQL Server Docker container. Use the following command in your command window.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=34r0TNhvgOde" -p 1433:1433 --name sql2019 -h sql2019 -d mcr.microsoft.com/mssql/server:2019-latest

The ACCEPT_EULA parameter signifies that you agree to Microsoft’s End User Licensing Agreement.

SA_PASSWORD is where you set the sa password for this instance. Replace 34r0TNhvgOde in the example with a complex password of your choosing.

The -p parameter allows you to map a TCP port on the host environment to a port in the container. In this example, I’m mapping the default host environment port of 1433 to the container port of 1433.

Set the name for your container using the –name parameter. I’m naming this one sql2019.

Likewise define your container’s hostname using the -h parameter.

And finally, the -d parameter is the name of the container image your want to run.

Connecting to and querying the SQL Server Docker container

You can connect to your new SQL Server in the Docker container as you would any other SQL Server instance. The image below show how I can connect my new sql2019 container instance using Azure Data Studio on my Windows desktop.

Once connected, you can query the instance. For example, I can run the following query to gather information about the SQL instance.

SELECT  SERVERPROPERTY('MachineName') AS MachineName,
        SERVERPROPERTY('ServerName') AS InstanceName,
        SERVERPROPERTY('Edition') AS SQLEdition,
        SERVERPROPERTY('ProductVersion') AS ProductVersion,
        SERVERPROPERTY('ProductLevel') AS ProductLevel;
SELECT  host_platform, 
        host_distribution, 
        host_release
FROM    sys.dm_os_host_info;

In the results, you can see this Ubuntu Linux container is running SQL Server 2019 Developer Edition.

Restoring a backup to your SQL Server Docker container

Now that you have a working SQL Server instance, let’s restore a copy of our BaseballData database to it.

Copy the backup file to the Docker container

Create a backup folder in the container using the following Docker command.

docker exec -it sql2019 mkdir /var/opt/mssql/backup

The exec parameter is used to execute commands inside the specified container. The -it parameter indicates that it will be an interactive terminal command.

Next, copy the SQL Server backup file to the newly created backup folder in the container using the Docker cp command. In this example, I’m copying the BaseballData.bak file from the present working directory on the host computer to the /var/opt/mssql/backup folder in the sql2019 container.

docker cp BaseballData.bak sql2019:/var/opt/mssql/backup

To ensure the file landed where intended, I can run the following command. The ls command is the Linux equivalent of the Window’s dir command.

docker exec -it sql2019 ls /var/opt/mssql/backup

Restore the SQL Server backup

Now we can restore the database backup as we typically would. Since I’m restoring a backup from another machine with different operating system, I need to move the data and log files when I restore the backup.

Run the following command in Azure Data Studio (or SQL Server Management Studio) to get a list of the database files for this database.

RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/BaseballData.bak';

Now, I can restore the database using the following TSQL command. Notice I’m moving the physical files to a new location in the container.

RESTORE DATABASE BaseballData 
    FROM DISK='/var/opt/mssql/backup/BaseballData.bak' 
    WITH MOVE 'Baseball' TO '/var/opt/mssql/data/Baseball.mdf', 
    MOVE 'Baseball_log' TO '/var/opt/mssql/data/Baseball_log.ldf';

You can see the new database in Azure Data Studio. You can use it just as you would any other SQL Server database.

Stopping and starting a SQL Server Docker container

If you’re using the SQL Server Docker container in a development or test environment, you may want to stop it when you’re not using it. Here’s how you can easily do that using a Windows command prompt. Replace sql2019 with the name of your instance.

docker stop sql2019

To start the Docker container again, just run the following command.

docker start sql2019

Some more information

Looking for some additional SQL Server information? The following posts may be helpful.

• Docker: Install containers for SQL Server on Linux – SQL Server | Microsoft Docs
• Use a database server running as a container | Microsoft Docs
• What SQL Server Version Am I Running?
• Do I Really Need SQL Server Enterprise Edition?
• Script: How Long Until My SQL Server Backup/Restore Completes?
• Want to Learn SQL Server? Here Are 3 Free Sources

Want to work with The SERO Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.