This blog post comes from an interesting experience with one customer about upgrading SQL Server on a Docker environment. Let’s set quickly the context: a production environment that includes a standalone Docker engine on the top of openSUSE Linux distribution with some SQL Server 2017 Linux containers. The deal was to update those SQL Server instances from 2017 CU1 to 2017 CU7.
The point is we applied the same kind of upgrades earlier on the morning but it concerned virtual environments with SQL Server 2017 on Windows Server 2016. As you already guessed, we cannot go on the same way with SQL Server containers. The good news is that the procedure is fully documented by Microsoft but let’s focus on my customer’s question: Can we achieve rolling upgrades with SQL Server containers? Rolling upgrade in this context may be defined as keeping the existing system running while we each component. Referring to the context of this customer the quick answer is no because they manage the standalone instances. However, we may hope to be as closed as possible to the existing rolling upgrade scenarios with SQL Server HA capabilities but it is pretty limited currently on Docker and didn’t make sense in this specific context.
In addition, let’s say that my customer spins up SQL Server containers by running docker run command. In this case, we had no choice to re-create the concerned containers with the new image. So basically, according to the Microsoft documentation, the game will consist of the following main steps:
- To download the latest SQL Server image from the Microsoft docker hub.
- To ensure we are using persistent volumes with SQL Server containers.
- To Initiate DB user backups (Keep safe here)
- To remove the concerned container
- To re-create the container with the same definition but the upgraded based image
The aforementioned steps will lead to some SQL Server instance unavailability.
Let’s simulate this scenario on my lab environment with a custom image (but the principle remains the same as my customer).
[clustadmin@docker1 PROD]$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES aa6b4411e4e4 dbi/dbi_linux_sql2017:CU4 "./entrypoint.sh 'ta…" 22 minutes ago Up 22 minutes (healthy) 0.0.0.0:1433->1433/tcp prod_db_1 153b1cc0bbe0 registry:2 "/entrypoint.sh /etc…" 6 weeks ago Up About an hour 5000/tcp registry.1.pevhlfmsailjx889ktats1fnh
The first docker container concerns my SQL Server instance with a custom base image dbi/dbi_linux_sql2017:CU4. My environment includes also one user database dbi_tools and some related external objects as SQL Server jobs and additional logins – dbi and test logins. A simplified representation of my customer scenario …
So, the challenge here is to upgrade the current container running on SQL Server 2017 CU4 with the last SQL Server 2017 CU7. The first step will consist in upgrading the dbi/dbi_linuxsql2017:CU4 image. Note I will use docker-compose in the next part of my demo but we’ll achieve exactly the same goal differently. So, let’s modify the FROM command line inside the docker file line as follows:
# set the base image (2017 version) # > CU4 includes SQL Server agent now FROM microsoft/mssql-server-linux:2017-CU7
Then let’s run a docker-compose command with the following docker-compose input file in order to generate a new fresh SQL Server image (CU7). The interesting part of my docker-compose file:
version: '3.1' services: db: build: . image: dbi/dbi_linux_sql2017:CU7 …
[clustadmin@docker1 PROD]$ docker-compose build
Let’s take a look at the existing docker images:
[clustadmin@docker1 PROD]$ docker images REPOSITORY TAG IMAGE ID CREATED SIZE dbi/dbi_linux_sql2017 CU7 0b4d23626dae 44 minutes ago 1.43GB dbi/dbi_linux_sql2017 CU4 0db4694645ec About an hour ago 1.42GB …
My new image has been created successfully. We may also notice 2 images now: the current one (with CU4 tag) and the new one (with CU7 tag)
Obviously persistent volumes are an important part of the customer architecture, so I also simulated some volume mappings inside my docker-compose file.
version: '3.1' services: db: build: . image: dbi/dbi_linux_sql2017:CU7 #container_name: dbi_linux_sql2017_cu4 ports: - "1433:1433" volumes: - /u00/db2:/u00 - /u01/db2:/u01 - /u02/db2:/u02 - /u03/db2:/u03 - /u98/db2:/u98 environment: - MSSQL_SA_PASSWORD=Password1 - ACCEPT_EULA=Y - MSSQL_PID=Developer - MSSQL_USER=dbi - MSSQL_USER_PASSWORD=Password1 - TZ=Europe/Berlin
Let’s move forward to the next step that consists in removing the current SQL Server 2017 CU4 container (prod_db_1):
[clustadmin@docker1 PROD]$ docker stop prod_db_1 && docker rm prod_db_1 prod_db_1 prod_db_1
And finally let’s spin up a new container based on the new image (SQL Server 2017 CU7)
[clustadmin@docker1 PROD]$ docker-compose up -d
Just out of curiosity, a quick look at the docker log output command reveals some related records concerning the upgrade process:
2018-06-04 22:45:43.79 spid7s Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install. 2018-06-04 22:45:43.80 spid7s 2018-06-04 22:45:43.80 spid7s ----------------------------------------- 2018-06-04 22:45:43.80 spid7s Execution of PRE_SQLAGENT100.SQL complete 2018-06-04 22:45:43.80 spid7s ----------------------------------------- 2018-06-04 22:45:43.81 spid7s DMF pre-upgrade steps... 2018-06-04 22:45:44.09 spid7s DC pre-upgrade steps... 2018-06-04 22:45:44.09 spid7s Check if Data collector config table exists... … 2018-06-04 22:45:59.39 spid7s ------------------------------------ 2018-06-04 22:45:59.39 spid7s Execution of InstDac.SQL complete 2018-06-04 22:45:59.39 spid7s ------------------------------------ 2018-06-04 22:45:59.40 spid7s ----------------------------------------- 2018-06-04 22:45:59.40 spid7s Starting execution of EXTENSIBILITY.SQL 2018-06-04 22:45:59.40 spid7s ----------------------------------------- 2018-06-04 22:45:59.40 spid7s ----------------------------------------- 2018-06-04 22:45:59.40 spid7s Finished execution of EXTENSIBILITY.SQL 2018-06-04 22:45:59.41 spid7s ----------------------------------------- 2018-06-04 22:45:59.44 spid7s Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install. 2018-06-04 22:45:59.44 spid7s Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install. 2018-06-04 22:45:59.45 spid7s Configuration option 'Agent XPs' changed from 1 to 1. Run the RECONFIGURE statement to install. 2018-06-04 22:45:59.45 spid7s Configuration option 'Agent XPs' changed from 1 to 1. Run the RECONFIGURE statement to install. 2018-06-04 22:45:59.53 spid7s Dropping view [dbo].[sysutility_ucp_configuration] 2018-06-04 22:45:59.53 spid7s Creating view [dbo].[sysutility_ucp_configuration]... 2018-06-04 22:45:59.54 spid7s Dropping view [dbo].[sysutility_ucp_policy_configuration] 2018-06-04 22:45:59.54 spid7s Creating view [dbo].[sysutility_ucp_policy_configuration]... 2018-06-04 22:45:59.55 spid7s Dropping [dbo].[fn_sysutility_get_is_instance_ucp] ….
The container has restarted correctly with the new based image as show below:
[clustadmin@docker1 PROD]$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES a17800f70fff dbi/dbi_linux_sql2017:CU7 "./entrypoint.sh 'ta…" 4 minutes ago Up 33 seconds (health: starting) 0.0.0.0:1433->1433/tcp prod_db_1 153b1cc0bbe0 registry:2 "/entrypoint.sh /etc…" 6 weeks ago Up 2 hours 5000/tcp registry.1.pevhlfmsailjx889ktats1fnh
Let’s check the new SQL Server version and if all my objects are still present:
The upgrade seems to be done successfully and all existing objects previous the upgrade operation still exist:
Great job! But let’s go beyond to this procedure with the following question: Could we have done better here? From a process perspective, the response is probably yes but we have to rely on more sophisticated features provided by Swarm mode (or other orchestrators as K8s) as service deployment that will make the upgrade procedure drastically easier. But don’t get me wrong here. Even in Swarm mode or other orchestrators, we are not still able to guarantee the zero down time but we may perform the upgrade faster to be very close to the target.
Previously I used docker-compose to spin up my SQL Server container. Now let’s use this counterpart on a Docker Swarm environment.
[clustadmin@docker1 PROD]$ docker info | grep -i swarm Swarm: active [clustadmin@docker1 PROD]$ docker node ls ID HOSTNAME STATUS AVAILABILITY MANAGER STATUS hzwjpb9rtstwfex3zsbdnn5yo * docker1.dbi-services.test Ready Active Leader q09k7pqe940qvv4c1jprzk2yv docker2.dbi-services.test Ready Active c9burq3qn4iwwbk28wrpikqra docker3.dbi-services.test Ready Active
I already prepared a stack deployment that includes a task related to my SQL Server instance (2017 CU4):
[clustadmin@docker1 PROD]$ docker service ls ID NAME MODE REPLICAS IMAGE PORTS 2bmomzq0inu8 dbi_db replicated 1/1 dbi/dbi_linux_sql2017:CU4 *:1433->1433/tcp [clustadmin@docker1 PROD]$ docker service ps dbi_db ID NAME IMAGE NODE DESIRED STATE CURRENT STATE ERROR PORTS rbtbkcz0cy8o dbi_db.1 dbi/dbi_linux_sql2017:CU4 docker1.dbi-services.test Running
A quick connection to the concerned SQL Server instance confirms we run on SQL Server 2017 CU4:
Now go ahead and let’s perform the same upgrade we’ve done previously (2017 CU7). In this case the game will consist in updating the corresponding docker-compose file with the new image as follows (I put only the interesting sample of my docker-compose file):
version: '3.1' services: db: build: . image: dbi/dbi_linux_sql2017:CU7 …
… and then I just have give the new definition of my docker-compose file as input of my stack deployment as follows:
[clustadmin@docker1 PROD]$ docker stack deploy -c docker-compose.yml dbi Ignoring unsupported options: build Updating service dbi_db (id: 2bmomzq0inu8q0mwkfff8apm7) …
The system will then perform all the step we previously performed manually in the first test including stopping the old task (container), scheduling the old task’s update with the new image and finally starting the updated container as shown below:
[clustadmin@docker1 PROD]$ docker service ps dbi_db ID NAME IMAGE NODE DESIRED STATE CURRENT STATE ERROR PORTS 4zey68lh1gin dbi_db.1 127.0.0.1:5000/dbi_linux_sql2017:CU7 docker1.dbi-services.test Running Starting 40 seconds ago rbtbkcz0cy8o \_ dbi_db.1 dbi/dbi_linux_sql2017:CU4 docker1.dbi-services.test Shutdown Shutdown 40 seconds ago
A quick check of my new SQL Server version:
That’s it!
In this blog post, I hope I managed to get you interested in using swarm mode in such case. Next time I will talk about SQL Server upgrade scenarios on K8s that is a little bit different.
Stay tuned!
By David Barbarin