Getting started with Windows containers & SQL Server

SQL Server containers are gaining popularity as a way of enhancing and standardizing development environments for Windows & Linux based SQL databases. SQL containers allow developers to have their ‘own’ dedicated copy of a database, usually without the need for extensive server infrastructures. Additionally, a single computer can host multiple containers, each with a different edition/version of SQL Server. This allows the user to quickly switch between environments, without the need to reinstall. Currently, a popular option for implementing containers on Windows-based computers uses Docker.

For those not familiar with containerization, here is a Microsoft article on Windows containers.

Installing Docker

Windows 10
Download and install the Docker Desktop for Windows application here:
Once the Docker application has finished installing, the Docker icon will appear in the taskbar.
Docker Desktop can host Windows and Linux based containers, but cannot run both types simultaneously. To verify that Docker is configured to run Windows containers:

  1. Click on the Docker icon (white whale) to open the pop-up menu.
  2. The menu will have the option to switch to Windows or Linux containers.Docker settings menu

Windows Server 2016 (and newer)
On Windows Server the Docker application is installed using PowerShell.
Open a PowerShell session As Administrator.

Note: check with your organization’s IT security administrator(s) before changing the PowerShell execution policy.

Once Docker is installed, a SQL Server image needs to be downloaded. Currently, Microsoft has 2 Windows-based SQL Server images available: Developer Edition and Express Edition. Both can be used for development & testing, but are unsupported for production use.

Note: unless otherwise specified, all of the code/commands below are being run from a PowerShell session.

Downloading a SQL Server image

Downloading a container image is known as ‘pulling’ the image from a repository. Once the image has been downloaded, one or more containers can be started (based on that image). Currently, Docker for Windows Desktop can run Windows or Linux based containers, but Windows Server can only run Windows containers, and Linux can only run Linux based containers. To pull an image, the command is: Docker pull <repository/image name>.

Alternatively, Docker will automatically download/update an image when the run command is executed to start a container.

Starting a SQL Server container

The basic run command syntax for Windows containers is Docker run <repo/image name>. With SQL Server containers there are a few additional attributes that must be included for the container to start. These attributes include: -it (interactive mode) or -v (detached mode), -p host_port:container_port (TCP ports), -e sa_password=”password (SA password), and -e ACCEPT_EULA=Y (EULA acceptance).

docker run command output
Pulling a container image using Docker Run

Once the image has been downloaded & extracted, a container will be created. At the end of the container’s startup process, the system will return the container id (a8b5b4d… in the image above).

Connecting with SSMS

The SQL Server container can now be accessed using SQL Server Management Studio (SSMS) or any other similar tool. The default server name for a container using port 1433 on the local machine is: .,1433 (a period, signifying the local machine, followed by a comma, and then the host TCP port specified in the run command).

The default SQL Server container is a very basic instance.

Attaching a persistent database

To add some functionality to the default container, a persistent database can be attached. A persistent database is one where the database files (mdf, ldf, ndf) reside in a folder that is external to the container. This allows changes to the database to remain after the SQL container has been restarted or recreated.

To attach a persistent database, there are 2 attributes that need to be included in the Docker run command: -v maps the folder(s) containing the database file(s) from the host to the container, and -e attach_dbs=”[<array>]” specifies the database name and database files to be attached in the container.

  • -v <host path>:<container path>
    • (Directory mapping) The drive letters specified for both host and container paths must be valid for the host machine (ie. the container path cannot be D:\ if the host doesn’t have a D drive). Also, the container path cannot be a folder that already exists in the image.
  • -e attach_dbs=”[{‘dbname’:’database name‘,’dbfiles’:[‘mdf file path‘,’ldf file path‘]}”
    • (Environment variable for attaching databases) Note: Docker uses \ as the default escape character, so the database paths need to use \\.

Note: the ` (back-tick) character can be used in PowerShell to wrap the command to the next line.

SQL container with persistent database

Naming a container

When Docker creates a new container, it is assigned a unique ID that can be used to reference the container when starting and stopping it. The container IDs consist of random letters and numbers, which can make it difficult to differentiate between 2 containers that are based on the same image (ie. container 71339ce0d567 has one configuration, while container e0bbd15582d has a different configuration).

To make it easier to differentiate containers, the optional attribute –name can be used to give each container a unique friendly name.
Note: unlike other attributes, the name attribute must be prepended with 2 dashes.

Docker run syntax

Here is the consolidated syntax for the Run command:
Docker run <attributes> <repository/image name>
-d (Detached Mode)
-it (Interactive Mode)
-p (Port mapping) syntax: <host port>:<container port>
-v (Directory mapping) note: drives letters must be valid for the host machine.
–name (optional container name) note: must prepend with 2 dashes.
-e (Environment variables)

  • SA_PASSWORD=<password> *Required*
  • ACCEPT_EULA=Y *Required*
  • attach_dbs=”[{‘dbName’:’PersistentDB’,’dbFiles’:[‘c:\data\PersistentDB.mdf’,’c:\log\PersistentDB_log.ldf’]}]”

Container operations

Enumerate containers

Shutdown container

Start container

Image operations

List images

Remove image
-f (force)

System cleanup

System prune

Next: Creating a customized SQL container image.

2 thoughts on “Getting started with Windows containers & SQL Server”

Leave a Reply

Your email address will not be published. Required fields are marked *