How to keep SQL Server up to date in 3 simple steps

We have been helping our clients run their SQL Servers for several years, and frequent problems we see are instances that are either out of date or entirely out of support!

We keep SQL Server up to date by either:

1. applying the latest patch, that is, either the latest Cumulative Updates (CU), General Distribution Releases (GDR) or Service Packs (SP) to the existing version
2. or upgrading from one version to another, for example, from SQL 2016 to SQL 2019.

Since SQL Server 2017, Microsoft has introduced a “Modern Servicing Model” that sees discontinuation of Service Packs and increased frequency of Cumulative Updates.

1. Plan

Firstly, decide whether you will patch your SQL Server or upgrade. If you are running SQL 2017 or newer (as of July 2022), you can install Cumulative Update when it’s released (usually every couple of months).

• Plan for any downtime and prepare for rollback when things go wrong.
• Agree on the maintenance window with the business (you should have one already anyway for all sorts of ongoing maintenance)
• Agree on the success factor to know when to roll back.

Patch or upgrade

The main reason to patch SQL Server is to ensure that Microsoft is fixing bugs and security holes.

The primary reason to upgrade is to stay in support. In July 2022, Microsoft ended support for SQL Server 2012, meaning no more security patches, bug fixes or feature updates. According to my recent LinkedIn poll, 50% of businesses still run SQL Server 2012 in production:

The second most important reason is to benefit from the latest and greatest features and improvements Microsoft brings to each version. For example, SQL Server 2012 introduced Column Storer Indexes to help aggregate data, and SQL Server 2016 brought improved Availability Groups to help increase Business Continuity. SQL Server 2019, on the other hand, introduced Big Data Clusters (that no one used).

On top of new features, every version brings many performance improvements and other tweaks.

2. Test

Every time a new SQL Server version is released, there is a chance that the existing code may stop working or may behave differently. The costs associated with testing the database code are usually the main reason businesses avoid upgrades.

Don’t cheat unless you have to

Features are linked to Compatibility Levels. If upgrading, you can “trick” the new SQL Server into behaving like the old one by setting the old compatibility level. It’s like buying a new car but putting the old engine in it. It is a database-level setting and does not always work with server-level settings.

Using an old compatibility level is like shuffling dirt under the carpet. If you are migrating to the latest version, ensure your application is compatible. The old compatibility level may no longer be supported in a few years, and your technical debt will have built up over time. You will face a big development task to bring your database up to speed.

This setting, however, may be handy if you are a company that relies on vendor-supported applications that are not always up to speed with the SQL Server release cycle. You can go ahead and upgrade your SQL Server estate but make each database behave like it was running on the old SQL Server by changing compatibility levels.

Analyse database compatibility

You can use Visual Studio or Database Migration Assistant (DMA) to produce a compatibility report to see if databases will work with the new version of SQL Server.

Test your workload

You can capture and replay the production workload on a test instance of the new server to ensure everything works. Any problems will be flagged on the test instance without affecting your production workload.

3. Execute

As with most IT-related things, planning and preparations take most of the time. Execution should be easy. Once you are ready to patch or upgrade, follow these steps:

1. Deploy the patch or upgrade in the development environment first. This low-risk, low-cost exercise will help you and your developers get a feel of the patch or upgrade.
2. Deploy the patch or upgrade in a pre-production environment. These environments are meant to be the same as production, with precisely the same or similar data sets and volumes to production. As the saying goes, “If it works in pre-prod, it will work in prod”.
3. Deploy the patch or upgrade the secondary node of the production cluster (don’t tell me you run single-node production servers, even if it is a VM!). Failover the workload to the already patched secondary node and leave it there for a week. If everything is good, patch the primary node and fail back the workload to the primary node. Pat yourself on the back. Well done.

Finally

You want to ensure all your databases are ready for the next version when it comes out. I recommend using Visual Studio for database development. This way, you can let VS flag any issues right away. Contrary to the common opinion, moving to the cloud will not solve any of that. PaaS databases such as Azure SQL can do automated patching and upgrades. If Microsoft decides to implement a change that may break your code, they will give a certain notice and then simply break your application. They won’t hang around for years until you fix your code.