By: Edwin Sarmiento | Updated: 2020-01-30 | Comments | Related: > Containers
Problem
Our company has started evaluating Containers to modernize our SQL Server databases. I have deployed SQL Server 2017 RTM on Linux for our development environments and would like to get it updated. How do I update my SQL Server 2017 on Linux containers?
Solution
For years, DBAs have installed either service packs (SPs) or cumulative updates (CUs) to keep their SQL Server instances updated. This required downloading the specific update from the Microsoft Download Center and installing it on the SQL Server instance. While this process works for SQL Server instances running on a machine, be it physical or virtual, it’s not quite the same for SQL Server on containers.
The Ephemeral and Immutable Nature of Containers
Containers were designed to be disposable – here today and gone tomorrow. That’s because they were first used in development and test environments where developers can write and test their code without having to wait for ages just for a server. They are short-lived, which is the opposite of how we dealt with servers in the past. We keep our servers as long as we can, installing updates or moving them around to keep them up and running. It’s one of the reasons why we have so much unstable environments – we don’t know exactly how long they have existed or if there are unnecessary components in them that affect the stability of the platform.
Containers were also designed to be immutable – they never change. The images you created as the basis for your containers look exactly the same since the day they were made. Should you decide to make changes to the application inside a container, you don’t go around adding files or installing updates on the running container. You create a new image that contains all of the changes you need using the steps outlined in this tip. You then deploy a new container based on this newly created image.
This is the reason why Docker volumes were used for SQL Server in this tip. Because we need to update our SQL Server instances and we don’t want to lose our data when we replace our existing containers with updated ones.
Updating a SQL Server 2017 on Linux Container
Let’s say you have a SQL Server 2017 RTM (version number 14.0.1000.169) on Linux container with databases running on a Docker volume named sqldbdata as outlined in this tip. Below is a high-level overview of the steps that you need to perform in order to update the container to SQL Server 2017 with CU 18 (version number 14.0.3257.3):
- Create a SQL Server 2017 with CU 18 on Linux Docker image
- Stop the existing SQL Server 2017 RTM on Linux container
- Create and start a new container based on the SQL Server 2017 with CU 18 on Linux Docker image
Step #1: Create a SQL Server 2017 with CU 18 on Linux Docker image
You can create your own custom SQL Server 2017 with CU 18 on Linux Docker image by following the steps outlined in this tip. Or if your container is based on an image from the Microsoft Container Registry, you can run the command below to pull an existing image.
docker pull mcr.microsoft.com/mssql/server:2017-CU18-ubuntu-16.04
Step #2: Stop the existing SQL Server 2017 RTM on Linux container
The reason you are only doing this step after having the SQL Server 2017 with CU 18 on Linux Docker image available is because you don’t want to introduce unnecessary downtime. You might be wondering if doing this makes sense given that this is just for a development environment. You want to develop the habits that can help you further along in your career as a DBA. One of them is always thinking about high availability and disaster recovery. You then decide based on the criticality of the environment and the databases you are dealing with.
Run the command below to stop your existing container. The name I passed to the command is the name of my existing container. Other commands were used to list and display the metadata of the Docker volume assigned to the container.
docker stop sqldevlinuxcon01
Step #3: Create and start a new container based on the SQL Server 2017 with CU 18 on Linux Docker image
Run the command below to create and start a new container with a new name – sqldevlinuxcon02 - based on the SQL Server 2017 with CU 18 on Linux Docker image.
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=mYSecUr3PAssw0rd" -p 1433:1433 --name sqldevlinuxcon02 -d -h linuxsqldev01 --mount source=sqldbdata,target=/var/opt/mssql mcr.microsoft.com/mssql/server:2017-CU18-ubuntu-16.04
There are a couple of things to consider in the command:
- A new name was assigned to the new container. This is because the old container still exists and have not been deleted. You want to keep the old container just in case something goes wrong with the update and you want to revert to the original container. Think disaster recovery.
- The same port number and hostname were used. You don’t want the applications connecting to the database to panic and fail. You want to keep existing application connection strings while making your network engineers happy. As far as the applications are concerned, nothing changed.
- The same Docker volume name was used. Since the system and user databases are stored in the Docker volume, updating the container by detaching it and re-attaching the new one to the existing Docker volume is no different from detaching and re-attaching database files – without using sp_detach_db or CREATE DATABASE <databaseName> FOR ATTACH. The metadata of the system and user databases remain the same, only the SQL Server database engine has changed.
The results of the docker ps command is a bit misleading. Just because the container is up and running doesn’t mean that your SQL Server instance is already available. That’s because the container is performing an update, just like when you’re manually installing a CU. You can look at the SQL Server error logs to monitor the update process by running the command below.
docker logs sqldevlinuxcon02 -f
Once the update completes, you can verify the version number and the status of the databases.
Next Steps
- Review the previous tips on running SQL Server on Docker
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-01-30
