For years, IT teams were told that containers were best suited to stateless workloads and that it wasn't a good...
idea to put databases in them. But then, starting with SQL Server 2016, Microsoft decided to enable its relational database software to run in Docker containers. So, why is it now OK to containerize SQL Server on Docker?
To answer that question, it's helpful to understand a little bit about the anatomy of a container. Although containers are technically a type of virtualization technology, they differ significantly from virtual servers. Server virtualization abstracts a server's hardware layer so multiple operating system copies can be run simultaneously, each isolated on virtualized hardware.
Conversely, containers perform virtualization at the application layer. Different Docker containers running on a single host system share a common base image, meaning that they all use the same OS kernel and there's no need for a guest OS within each container. This is why containers have a reputation for being lightweight, and for performing so well on startup.
Data loss a non-starter for databases
If containers are really just virtualized application environments, why was running SQL Server on Docker once deemed a bad idea? One of the key differences between virtual machines and containers is that the latter are essentially disposable. Containers can be spun up at a moment's notice, but they are often removed from systems just as quickly.
When that happens, the data within a container is lost. It's the reason why containers are so often used to run stateless workloads that don't require persistent data, such as the processing jobs done by front-end web servers.
Docker does give IT administrators the option of associating what's called a union file system with a container to act as a writable layer of storage. However, that approach is most often used in situations in which a permanent modification needs to be made to the base container image, thereby altering the containers that are based on the image. It might be appropriate, for example, when installing an application patch.
The problem is that the files created in the writable layer of a container go away when the container does. While that's OK for stateless uses, most applications -- and certainly databases like SQL Server -- need to be able to write to disk, even if only on a temporary basis.
To accomplish that, an alternative to writing data to a union file system is to write it to a volume. Volumes are mapped to containers, but they are set up as subdirectories in the Docker-managed portion of the host file system outside of the containers themselves.
Multiple containers can share a single volume and simultaneously access the data stored in it, and volumes aren't automatically destroyed when the containers associated with them are removed. Instead, you have to explicitly delete volumes if they're no longer needed.
Volume approach to database containers
Using volumes has other advantages beyond data persistence. It doesn't expand the size of individual containers, and volumes typically provide better read and write performance than the storage drivers used with union file systems. As a result, when it comes to hosting SQL Server on Docker -- or running any other database in a container -- the general rule of thumb is to use volumes for data storage.
One trick to ensure that you don't accidentally lose your data is to create volumes in a way that guarantees ongoing data persistence. For example, you could create a permanent, named container and associate a particular volume with it. Doing so enables you to safely share the volume with your SQL Server containers. Even if all of the database containers are removed, there's -- hopefully -- no danger that the volume will be deleted because it's associated with a container that still exists.
The persistent data storage enabled by volumes makes containerizing SQL Server more palatable for database administrators and developers -- a change in attitude that Microsoft confirmed when it first added support to put SQL Server on Docker in Windows systems as part of the 2016 release. It then expanded the support to include Linux-based containers when SQL Server 2017 was released.
In spite of all of the hype, a container may or may not be the best choice for running SQL Server depending on your needs. It's still easier to run SQL Server