tiero - Fotolia

Manage Learn to apply best practices and optimize your operations.

Use these commands to deploy SQL Server Docker containers

Containerized apps are everywhere, but you can also run databases in containers. Follow these commands to customize SQL Server images in Docker containers.

When you build containers for your applications, you also need a strategy to handle SQL Server databases. They can be standalone, or they can be containerized.

You can create SQL Server containers using one of the preexisting SQL Server images from the Docker Hub. There are four images available for SQL Server Docker containers: 

  • SQL Server vNext Community Technology Preview for Linux -- microsoft/mssql-server-linux
  • SQL Server 2016 Express Edition for Windows -- microsoft/mssql-server-windows-express
  • SQL Server 2016 Evaluation Edition for Windows -- microsoft/mssql-server-windows
  • SQL Server 2016 Developer Edition for Windows -- microsoft/mssql-server-windows-developer

While you can use these images to run SQL Server in a container, they are not really suitable for large-scale production implementations. SQL Server vNext is a Linux implementation, and it is still in preview mode. SQL Server 2016 Express is limited in scalability. SQL Server 2016 Evaluation Edition expires in 180 days, and SQL Server 2016 Developer Edition is restricted from production usage.

What if you want to run SQL Server 2016 Standard or Enterprise Edition in a container to support a production application?

I'll show you how to build your own custom SQL Server container using Windows Server Core and SQL Server 2016 Standard Edition.

Build the image layers

First, copy the SQL Server installation media to a local volume on your container host. For more information on setting up a Windows Server container host, refer to "Tips on how to build your first SQL Server container."

In this example, I'll use the folder c:\SQLinstall, and then create a container using the Windows Server Core image with the following Docker run command, and attach the c:\SQLinstall folder to the container.

docker run -d -ti --name SQLStd --hostname SQLStd -v C:/SQLinstall/:C:/SQLinstall/ microsoft/windowsservercore

The -d parameter indicates this will be a detached container that runs in the background. The --name parameter gives the container a friendly name. The --hostname parameter provides a computer name for the container host. The -v parameter specifies the local volume to mount. In this example, that volume contains the SQL Server installation media.

Next, the Windows Server Core image is specified.

Build the custom SQL Server image

To install SQL Server, open an interactive command prompt to the container using the Docker exec command, as you see here:

docker exec -it SQLStd cmd

This command will start an interactive command shell for the container.

Next, use the cd command to navigate to the mounted c:\SQLInstall directory, and then run the command line SQL Server setup program using the following commands:

cd c:\sqlinstall


The /Q switch indicates that the setup will be unattended. The /ACTION switch indicates SQL Server will be installed. The /FEATURES switch controls which features are installed. The values of SQL and IS specify that the SQL Server relational engine and Integration Services will be installed. The /INSTANCENAME parameter specifies that the new SQL Server instance will use the default instance of MSSQLSERVER. /SQLSVCACCOUNT specifies the account under which the SQL Server service will run. /SQLSYSADMINACCOUNTS creates a login for the sysadmin roll. /AGTSVCACCOUNT specifies the account under which the SQL Server Agent will run. The /SECURITYMODE parameter indicates that SQL Server authentication will be used. The /SAPWD parameter provides a password for the sysadmin account. The /TCPENABLED parameter enables remote connections using the TCP/IP protocol. The /IACCEPTSQLSERVERLICENSETERMS=1 parameter is required to accept the license agreement for an unattended install.

For more complete information about how to install SQL Server 2016 from the command line, refer to "Install SQL Server 2016 from the Command Prompt." You can see the results of running the unattended SQL Server installation below.

Install SQL Server 2016 Standard Edition

The unattended installation first displays a copyright prompt. If you have supplied all the required parameters, it will run for a few minutes, and then display the installation experience message and return to the command prompt when it has finished.

After SQL Server has been installed on the Windows Server Core container, you can use the Docker commit command to create a new image using that container.

Optionally, you could create user databases in the new container. However, be aware that any changes in those databases will be lost when the container ends. Alternatively, it's typically best to attach databases to a container at runtime using an external volume.

To commit the container to a new image, first run the Docker stop command to stop the container. Windows Server does not support creating images from running containers. After stopping the container, you can use the Docker commit command to create a new container image. Since the new image consists of Windows Server Core, plus the new SQL Server installation, it can take a while for Docker to create the new image. You can see the commands to stop the container, commit the new image and then list all of the local images below, and in the graphic that follows.

Docker stop SQLStd
Docker commit SQLStd teca/sqlstd1:latest
Docker images

SQL Server images on the Docker Hub

Now the new SQL Server Standard Edition container can be started using the Docker run command from the committed image. In this example, the new image's name is teca/sqlstd1. It will run Windows Server Core, plus a fully functioning version of SQL Server 2016 Standard Edition. The following list shows how you can start the new container and attach the sample AdventureWorks2014 database, which is in an external volume. Here's an example of running the new custom SQL Server container with the Docker run command:

docker run -d -ti -p 1433:1433 --name SQLStdContainer -v C:/temp/:C:/temp/ -e attach_dbs="[{'dbName':'AdventureWorks2014','dbFiles':['C:\\temp\\AdventureWorks2014_Data.mdf','C:\\temp\\AdventureWorks2014_log.ldf']}]" teca/sqlstd1

At this point, you can connect to the containerized SQL Server instance and use it like any other SQL Server instance. You can use the Docker inspect command to retrieve the TCP/IP address of the SQL Server container as shown here:

docker inspect -format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' SQLStdContainer

To connect to the containerized SQL Server instance using SQL Server Management Studio (SSMS), you can supply the TCP/IP address, followed by a comma, and the port number 1433 when you open a database connection from SSMS. You can see SSMS connected to the custom SQL Server Standard Edition container below.

At this point, the SQL Server Docker container can be deployed to support production workloads.

Next Steps

Learn to build your first SQL Server container

Windows Server containers 101

Hyper-V vs. Windows Server containers

SQL Server 2016 licensing guide

Dig Deeper on Microsoft SQL Server Consolidation and Virtualization