Update SQL Server 2017 on Linux Container


By:   |   Updated: 2020-01-30   |   Comments   |   Related: More > 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):

  1. Create a SQL Server 2017 with CU 18 on Linux Docker image
  2. Stop the existing SQL Server 2017 RTM on Linux container
  3. 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
stop docker

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.
create and start container

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
get docker logs

Once the update completes, you can verify the version number and the status of the databases.

query results server name and version
Next Steps


Last Updated: 2020-01-30


get scripts

next tip button



About the author
MSSQLTips author Edwin Sarmiento 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.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

Create SQL Server on Windows Docker Container using Dockerfile - Part 6

Getting Started with Windows Containers for SQL Server - Part 1

Create SQL Server on Linux Docker Container using Dockerfile - Part 5

Build Docker Containers with External Storage on Your Desktop

Docker Container Names, Internals and Configuring Storage for SQL Server - Part 4





get free sql tips
agree to terms


Learn more about SQL Server tools