Monthly Archives: August 2018

Questions from PASS Marathon Containers

Thanks to everyone who attended the PASS Marathon Containers edition and to PASS for the opportunity to present. I received the Questions from the session and wanted to provide answers to the attendees and the community.
If you want to see the session again, check it out on YouTube. The decks are available online at
Here’s the list of questions from the session and my answers.
  • What do you mean it is not for production environment in Windows?
    • It’s my understanding that only Linux based SQL Server containers are supported and that Windows based containers are not. I’m looking to find an official statement, like a web site link) from Microsoft on this but I am having troubles doing so. Here is the official statement on running SQL Server on Linux in a Container –

  • When you say App1 on a container, is it just 1 executable/service or can be multiple of those on the same container?
    • Generally speaking you’ll want only one process in a container. A primary reason for using containers is agility and a core way of achieving that is breaking dependencies by reducing what’s included inside the container.. Technically speaking, you can have more than one process inside a container. If fact SQL Server on Linux does. There’s the Watchdog process, then the actual SQL Server process. The output below is a process listing from inside a running SQL Server on Linux container. You can see PID 1 and 7 are processes inside the container.

      root 1   /opt/mssql/bin/sqlservr

      root 7   /opt/mssql/bin/sqlservr

      For the internals geeks out there, let’s look a a process listing on the host OS that’s running our container. From there we can see that the sqlservr process is a child process of containerd which is managed by dockerd. This is the same SQL Server process inside the container. But in the first example you here can see the impact of namespaces…the process IDs are rebased and start at 1 and the second SQL Server PID is 9. In the output below you can see the PIDs are 2172 and 2213.

      root 1034 /usr/bin/dockerd

      root 1245 \_ docker-containerd 

      root 2154     \_ docker-containerd-shim -namespace moby -workdir 

      root 2172         \_ /opt/mssql/bin/sqlservr

      root 2213             \_ /opt/mssql/bin/sqlservr

  • Maybe I missed this part, how do I know what kind of image I could pull down?
    • In the demos I show how to use docker search to find images that are available from the Docker Hub. If you prefer a web browser experience, check out the Docker Hub to see what containers are available to you. Here’s the code to find the mysql-server images available in Docker Hub.
      • docker search mssql-server | sort
  • Does SQL Container fit into production environment?
    • Here is a link to the official word from Microsoft on running containers in production –
    • What I want you to leave this session with is an introduction to containers, starting your journey on what’s next when using containers. To that end here are some of the things you’ll need to consider before using containers in production
      • Is your organization ready – Do the operational skills and technologies exist to support using containers in production.
      • Backup and recovery – Does the organization have a strong backup and recovery environment. How are you going to protect the data running in a SQL Server container. Luckily, it’s just SQL Server on Linux so you can use the traditional technologies and techniques to backup your data. 
      • Data persistency – Understanding the underlying physical infrastructure and how to persistent data in ways that it’s protected and well performing.
      • Orchestration – Is there technologies in place to manage the state of your containers, things like workload placement, starting, stopping and also data persistency.
  • How do SQL Containers work with High Availability and Disaster Recovery?
    • Backups and data persistency are primary concerns here. You still need to care and feed for your SQL Server databases just as if they were platformed on a full operating system. For HA, Microsoft has some guidance on how to use Kubernetes to provide HA services to your SQL Server containers here. What I want you to think about when using containers for SQL Server is deploying a new container is VERY fast. We want to be able to persist the data and be able to stand up a new container and mount our data inside that container. Using this technique we can restore SQL Services very quickly with low RTO. That itself is an interesting way to provide HA services without any additional technologies.
  • Is there a way to have persistent storage for the system databases (e.g. master database for logins and what not)?
    • In the demos during the session I defined a Docker Data Volume when we started the container where we mounted that as /var/opt/mssql/ inside the container.  When SQL Server on Linux starts for the first time it will copy the system databases from its package directories into /var/opt/mssql/data. Since this data is stored in the persistent data volume if we stop and delete this container and start a new container pointing at that same docker data volume when SQL Server starts up it will use those system databases.

      Starting a SQL Server Container with a Docker Data Volume. The -v parameter names the volume sqldata1 and /var/opt/mssql is where it will be mounted inside the container.

      docker run \
          --name 'sql1' \
          -p 1433:1433 \
          -v sqldata1:/var/opt/mssql \
          -d microsoft/mssql-server-linux:2017-latest
  • How about the backup of a container? can it be like VM’s snapshot? 
    • You can snapshot the state of a container with docker commit. This will create a new image from the container and that image can be used to create additional containers. But recall, containers are intended to be ephemeral, we really want to define the state of the container OUTSIDE of the container in code. The things inside the container that require data persistency, like databases should be taken care of using  techniques like Docker Data Volume, backups and other high availability scenarios.