How to Build Your First SQL Server Container on Windows Server 2016

Containers offer a lightweight, fast alternative to full virtual machines for running SQL Server. Because they virtualize at the operating system level rather than the hardware level, you can run far more containers than VMs on a single host. They are also stateless by default, which makes them ideal for development, testing, and scenarios where you need to spin up a SQL Server instance quickly without waiting for a full installation.

This guide walks through the end-to-end process of creating and managing a SQL Server container on Windows Server 2016 using Docker and Microsoft’s prebuilt SQL Server Express image. The same principles apply when working with the Developer Edition image or when building custom images for Standard and Enterprise editions.

Why Run SQL Server in a Container?

• Speed: Containers start in seconds, eliminating lengthy SQL Server installation processes.
• Density: More containers than VMs can run on the same host hardware.
• Isolation: Containers isolate application workloads, which is useful in multi-tenant architectures.
• Disposability: Containers can be cloned, stopped, and deleted instantly, making them ideal for CI/CD pipelines and test environments.
• Persistence with volumes: By mounting host volumes, you can persist database files across container lifecycles.

Enabling Containers in Windows Server 2016

Before running any container, you must enable the Containers feature in Windows Server 2016:

1. Open Server Manager.
2. Click Manage and select Add Roles and Features.
3. Click through the wizard until you reach the Features page.
4. Check the Containers checkbox and click Next.
5. Click Install and allow the process to complete (typically a few minutes).
6. Restart the server when prompted.

After the restart, install Docker using an elevated PowerShell prompt:

Install-Module -Name DockerMsftProvider -Repository PSGallery -Force
Install-Package -Name docker -ProviderName DockerMsftProvider

A second reboot is required after installing Docker. Once the system is back up, verify the installation:

docker version

Pulling the SQL Server Express Image

A Docker image is a read-only template that includes the operating system layers and application binaries. A running instance of an image is called a container. Microsoft publishes a prebuilt SQL Server 2016 Express image that bundles Windows Server Core and SQL Server Express together.

Pull the image with the following command (note: the download is large because it includes the full Windows Server Core layer):

docker pull microsoft/mssql-server-windows-express

After the pull completes, list your locally available images to confirm:

docker images

You should see microsoft/mssql-server-windows-express in the output, along with its image ID and tag.

Running and Stopping a SQL Server Container

Starting the Container

Use docker run to launch a SQL Server container:

docker run -d -p 1433:1433 \
  -e sa_password=TempPa550wd# \
  -e ACCEPT_EULA=Y \
  microsoft/mssql-server-windows-expressCode language: PHP (php)

Key parameters explained:

• -d — Detached mode; the container runs in the background.
• -p 1433:1433 — Maps TCP port 1433 on the host to port 1433 inside the container. Incoming traffic on the host port is forwarded to the container.
• -e sa_password — Sets the SQL Server sa (system administrator) password. The value must meet Windows password complexity requirements.
• -e ACCEPT_EULA=Y — Required to accept Microsoft’s End User License Agreement for SQL Server.

Confirm the container is running:

docker ps

The output lists all running containers, including the container ID, image name, and uptime.

Stopping and Removing a Container

Docker provides several commands to dispose of containers. You only need the first three characters of the container ID as a unique shorthand:

• docker stop <id> — Sends a graceful stop signal; Docker force-kills after a 10-second timeout.
• docker kill <id> — Immediately terminates the container without a graceful shutdown.
• docker rm <id> — Removes a stopped container. Add -f to force-kill and remove in one step.

Example (assuming the container ID begins with 98c):

# Stop the container gracefully
docker stop 98c

# Then remove it
docker rm 98cCode language: PHP (php)

Attaching Databases to a Container

Containers do not have persistent storage by default — all data written inside a container is lost when it is removed. To persist database files, mount a host directory as a volume using the -v flag, and attach an existing database using the attach_dbs environment variable.

The following example mounts the host directory C: emp into the container and attaches an existing SQL Server database whose .mdf and .ldf files reside there:

docker run -d -p 1433:1433 \
  -e sa_password=TempPa550wd# \
  -e ACCEPT_EULA=Y \
  -v C:/temp/:C:/temp/ \
  -e attach_dbs="[{'dbName':'EditorialDb','dbFiles':['C:\\temp\\editorialdb.mdf','C:\\temp\\editorialdb_log.ldf']}]" \
  microsoft/mssql-server-windows-expressCode language: PHP (php)

Key parameters explained:

• -v C:/temp/:C:/temp/ — Mounts the host path C:\temp into the container at the same path. Note the Linux-style forward-slash convention required by Docker.
• attach_dbs — A JSON-formatted environment variable that specifies the database name and the full paths to its data (.mdf) and log (.ldf) files as seen from inside the container.

When the container is later removed, any changes made to the database files on the mounted volume are preserved. However, configuration changes made inside the container — such as enabling accounts, changing passwords, or modifying server settings — are discarded unless you rebuild the image to include them.

Connecting to the SQL Server Container

Using SQLCMD from Inside the Container

Use docker exec to launch an interactive SQLCMD session directly inside the container. Replace 5e9 with the first three characters of your container ID:

docker exec -it 5e9 sqlcmd -S.

The -it flag creates an interactive terminal session. SQLCMD uses Windows authentication to connect to the local SQL Server instance inside the container. Once connected, you can run any supported T-SQL statement.

Note: The sa account may be disabled in the SQL Server Express image by default. If you need to connect via SQL Server authentication, enable the sa account first using a SQLCMD session with Windows authentication before attempting to connect from external tools.

Using SQL Server Management Studio (SSMS)

To connect from SQL Server Management Studio running on the host or another networked machine, you need the container’s IP address. Retrieve it with docker inspect:

docker inspect -f "{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}" 5e9Code language: JavaScript (javascript)

In SSMS, open a new connection and enter the container’s IP address followed by the SQL Server port (for example, 172.21.0.2,1433). Select SQL Server Authentication and supply the sa credentials you set when running the container.

Once connected, the containerized SQL Server instance behaves identically to any other SQL Server instance. You can browse databases, run queries, manage objects, and use all standard SSMS features.

Connecting from Another Networked System

To reach the container from a different machine on the network, connect to the host server’s IP address (not the container’s internal IP) on the port you mapped with -p. For example, if the host IP is 192.168.1.100 and you mapped port 1433, use 192.168.1.100,1433 as the server name in SSMS.

Summary

Running SQL Server in a Docker container on Windows Server 2016 is straightforward once you understand the core workflow:

1. Enable the Containers feature in Windows Server 2016 and install Docker via PowerShell.
2. Pull the prebuilt microsoft/mssql-server-windows-express image.
3. Use docker run with appropriate port mappings, environment variables, and volume mounts to launch a configured SQL Server instance.
4. Attach existing databases via the attach_dbs environment variable and a mounted host volume to ensure data persistence.
5. Connect interactively using docker exec and SQLCMD, or remotely using SSMS with SQL Server authentication.

The stateless nature of containers means that configuration changes inside the container are ephemeral, but your database files stored on a mounted volume remain intact across container restarts and replacements. This pattern is well-suited to dev/test workflows, CI pipelines, and high-density database hosting scenarios.

Original source article: TechTarget