This past week Microsoft released SQL Server 2017 CU6, Microsoft Support. So, I thought it would be a good idea to set up my Linux SQL Server to follow the same automated WSUS-driven patch methodology as we do on some of our Windows SQL Server installations. Some of these servers apply Microsoft updates on a weekly scheduled maintenance window on Sundays. For these servers, it’s a little extra effort to closely monitor them through the maintenance window, and makes me nervous about Monday morning phone calls. However, it’s a good feeling to know these databases are running the latest patches, and Microsoft recommends “ongoing, proactive installation of CUs as they become available.”
My first Linux version of this scheduled maintenance window for SQL Server is just a cron job running apt-get commands. I’m going to follow up with my Linux colleagues on other patch maintenance approaches and will come back to edit this post with any better ideas, or likewise I’ll edit the post if I’m missing something important.
Patching SQL Server on Linux is super simple to do because the apt-get repository for Microsoft was added during the original installation Quickstart: Install SQL Server and create a database on Ubuntu. It’s literally just two simple apt-get commands:
sudo apt-get update
sudo apt-get install mssql-server
To repeat this in the weekly Sunday maintenance window I’m just using this cron job:
#
# m h dom mon dow command
0 13 * * sun /usr/local/scripts/sun-maint-win-sqlserver.sh
The bash script looks like this:
#!/bin/bash
######################################################
# Name: sun-maint-win-sqlserver.sh
# Description: runs SQLServer update scripts during the Sunday maintenance window
#
# Inputs: none
#
# Known limitations:
# author has limitations
# Revisions:
# Date who Description
# 20180423 kpk created
#
######################################################
######################################################
# update apt .. includes the microsoft repository
apt-get update
######################################################
# update sqlserver, if able
apt-get install mssql-server
So those are the two tiny blocks of code.
I didn’t have a local scripts directory yet, so I need to create that first:
sudo mkdir /usr/local/scripts/
Then I took the code for the bash script and pasted into the following file in that directory
sudo vi /usr/local/scripts/sun-maint-win-sqlserver.sh
To set up the crontab for the root account we need these two commands, the first one to list the root crontab and the second one to edit the root crontab.
sudo crontab -u root -l
sudo crontab -u root -e
Once again, this is the crontab command to paste into sudo crontab -u root -e. This scheduled time is 1:00 p.m. on Sunday.
#
# m h dom mon dow command
0 13 * * sun /usr/local/scripts/sun-maint-win-sqlserver.sh
I tested the script interactively with this command:
sudo bash /usr/local/scripts/sun-maint-win-sqlserver.sh
A note with this script is that it doesn’t use sudo since it’s going to run as root. The script run looked like this with [apt-get update] in brown font and [apt-get install mssql-server] in green font:
The script worked and now it’s up-to-date:
coder@localhost:~$ sqlcmd -S localhost -U sa
Password:
1> SELECT @@version;
2> GO
——————————————
Microsoft SQL Server 2017 (RTM-CU6) (KB4101464) – 14.0.3025.34 (X64)
Apr 9 2018 18:00:41
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 16.04.2 LTS)
(1 rows affected)
1>
With this approach, I can plan to keep an automated, consistently up-to-date mainstream Linux SQL Server platform for years by following a pattern inspired from https://www.ubuntu.com/info/release-end-of-life. The pattern will be to migrate from Ubuntu 16.04 LTS to 18.04 LTS within a year. Then run 18.04 for 2 years through Ubuntu’s most popular two years until 20.04 LTS stabilizes. This will give me a year of my current 16.04 LTS Linode server running SQL Server 2017. This coming year on 16.04 will probably get me up to CU 2 of V.Next SQL Server if the v.Next upgrade is ready before the distribution upgrade. This kind of takes the pain out of planned obsolescence as long as I plan for a 24-month distribution upgrade and V.Next SQL server upgrades as appropriate.
If we were running more Linux in our shop, a final step in our shop will be to add the dates for this migration pattern into the same license database as our other proprietary software as a reminder to plan the next 24-month distribution migrations.
This is a healthy change of philosophy. In the past, some of our Linux servers would just run for decades. Curiously, there’s one RedHat server running in our office that we built 12 years ago which is still running a simple service even though the OS went out of support many years ago.