5 FAQs on SQL Server containers and how to manage them

Docker containers are great for SQL Server development and test scenarios, as they allow database administrators and developers to deploy SQL Server instances within seconds. Users are able to spin up a container complete with SQL Server and attached databases as quickly as they can type in a docker run command.

Containers can also be used in production SQL Server applications. However, there are some things you should know about deploying SQL Server containers before you start to use them. Here are answers to five frequently asked questions on running SQL Server in Docker containers.

Q: Can I use SQL Server Management Studio (SSMS) or Azure Data Studio to connect to SQL Server in a container and run queries?

Yes. You can connect to a SQL Server container to query and manage it in three ways; two of them can be done with SSMS or Azure Data Studio, which was called SQL Operations Studio before Microsoft made it generally available in September 2018. The three options are:

• Connecting from within the container itself. You need to use the sqlcmd command-line tool to do this because Docker containers don't support a GUI. The easiest way is to run a docker exec command with the notation for the server name. For example:
  docker exec -it dab sqlcmd -S. -Usa.
• Connecting from the Docker host. To do this, you can use sqlcmd or a management tool like SSMS or Azure Data Studio. You simply enter the IP address of the container, as here: Server = 172.17.0.2.
• Connecting from an external system. You can also use sqlcmd, SSMS or Azure Data Studio to connect to a SQL Server container from a host system that's separate from the Docker host by entering the Docker host's IP address followed by a comma and the container's TCP port mapping. For example: Server = 192.168.100.123,1403.

Note that when you start a container, you have the option of mapping it to SQL Server's default port 1433 or another port that's exposed on the host. If you want to run multiple SQL Server containers on the same host, you need to map them to different ports to avoid conflicts.

Q: How can I attach external databases to SQL Server containers?

Docker containers are stateless. While that can work for development and test uses with a system like SQL Server, in production applications, you typically need to persist databases and the changes made to the data in them. Docker lets users do so by attaching database files stored externally to a container that's running SQL Server. The external repositories are maintained separately from the container so the databases remain in place if it's stopped and deleted.

There are two ways to attach external databases to containers. One is to use Docker data volumes, which reside in the Docker-managed portion of the host machine's file system. Volumes are the preferred method of enabling data persistence in containers, according to the online documentation for the Docker technology.

First, use the docker volume create command to set up a volume. You can then mount the volume to a container using the following command:

docker run -d -p 1433:1433 -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD=SQLpwd20017 -v MSSQLDBvol:/var/opt/mssql microsoft/mssql-server-linux

Volumes can be used with SQL Server in both Windows and Linux systems, but they work differently in the two OSes. In Linux, you map SQL Server's default data directory to one that Docker creates in a volume and Docker copies the system's database files into the volume. To add more databases, copy their data and log files to the volume and attach them to the container. Otherwise, you can copy a database backup file to the volume and restore it, which makes the database available to the container.

That isn't the case with Windows-based volumes. With Docker on Windows, you can only map to empty directories, and only user database files can be present in a volume. Also, databases need to be attached to containers via a docker run command.

The other way to attach external databases is to mount a file or directory that's on the Docker host in the container. Called a bind mount, this process is also somewhat different for SQL Server containers on Windows and Linux.

You can directly attach a folder that contains a database's data files and transaction log files to a SQL Server container running on Windows, as you can see in the following example:

docker run -d -p 1433:1433 -e sa_password=SQLpwd2017 -e ACCEPT_EULA=Y
-v C:/temp/:C:/temp/ -e attach_dbs="[{'dbName':'AdventureWorks2014','dbFiles':['C:\\temp\\AdventureWorks2014_Data.mdf',
'C:\\temp\\AdventureWorks2014_Log.ldf']}]" microsoft/mssql-server-windows-express

SQL Server on Linux supports mounting a folder on the Docker host to the default SQL Server data directory in a container. Here's an example of how to do that, after which you can attach the database files to the container by following the steps listed above on how to attach files in a volume to a Linux-based container:

docker run -d -p 1433:1433 -e ACCEPT_EULA=Y -e SA_PASSWORD=SQLpwd2017
-v /home/administrator/MSSQLDBs:/var/opt/mssql microsoft/mssql-server-linux

Q: How are SQL Server containers licensed?

The licensing for SQL Server in containers is similar to running it in a virtual machine (VM). Containers differ from VMs in that all of the containers running on a physical host share the operating system and other resources. This can have financial benefits, as you require fewer OS licenses, but each instance of SQL Server must still be licensed.

Under Microsoft's Server + CAL licensing model, which can be used with the Standard edition of SQL Server, you need to purchase one SQL Server license for every container running a SQL Server instance. Also, each user or device that accesses an instance will require a SQL Server 2017 client access license (CAL).

Under the per-core licensing model available for both the Standard and Enterprise editions, all the virtual cores in the container running an instance of SQL Server must be licensed, and there's a four-core license minimum per container. Additional licenses can be purchased in packs of two.

With SQL Server 2017 Enterprise Edition, customers who have licensed the physical cores on a server instead of using a virtual OS environment can run a number of containers equal to the number of core licenses assigned to the server.

Q: How can I control CPU assignments for SQL Server containers?

Unlike VMs, where you explicitly configure the number of virtual CPUs in them, containers, by default, share all of the host resources -- meaning that if the host has 16 CPUs, each container will have 16 virtual CPUs. While that's good for performance, it can also have unintended licensing ramifications.

However, you can limit the virtual CPUs in a SQL Server container by using the --cpus=<value> parameter as part of the docker run command to specify how many of the available CPUs the container can utilize. For example:

Docker run -d -p 1433:1433 --cpus=1 -e sa_password=PwdSQL2017 -e ACCEPT_EULA=Y microsoft/mssql-server-windows-express

In Windows, you can also use the -cpu-count parameter to do the same thing. In addition, you can use --cpuset-cpus in both Linux and Windows to specify the particular CPUs that will be used to execute processing jobs.

You can likewise limit the maximum amount of memory a container can use via the -m or --memory=<value> parameter; the minimum allowed value is 4 MB.

Q: How do I troubleshoot SQL Server running in a Docker container?

Docker containers typically run in detached mode, which makes them a background task -- meaning that you don't have any visual feedback when something goes wrong. Here are the three biggest issues you may need to troubleshoot when you start running SQL Server containers, and how to do so:

• A container won't run or only runs for a couple of seconds. If this happens, use the docker logs command to examine the container logs. If that doesn't help, try the docker run command with the -it parameter in place of the -d parameter. Doing so will show the container activity, hopefully allowing you to see what's wrong.
• You can't connect to SQL Server in the container. If that's the case, use the docker inspect command to review the container's IP address and port. If you're trying to connect from an external host, be sure that you're using a comma between the Docker host's IP address and the container's mapped SQL Server port. For example: 192.168.100.123,1403.
• You can't attach an external database to the container. First, use docker logs to check for any startup errors. Next, verify the host path and the names of the database files in the external directory. If you're using Windows-based containers, make sure the JSON string used in the attach_dbs parameter is correctly formatted.