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.
General Distribution Releases
Since Version 2017, Microsoft will release General Distribution Releases when needed to fix urgent security-related issues.
Cumulative Updates contain product fixes and improvements. They could also include new features. Since version 2017, They are usually released every two months in the first year of the product lifecycle and every three months for the remainder.
Service packs are usually released annually and contain all Cumulative Updates released so far. However, since version 2017, there are no Service Packs anymore, only Cumulative Updates. Historically, Service Packs were significant updates often treated as a version upgrade rather than a “patch”. If you are on version 2017 or later, you don’t have to worry about Service Packs.
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.
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.
Behavioural and Breaking changes
The database may behave differently because of underlying changes to the SQL Server Engine. The code may start returning different results. Or, if there have been changes to the optimiser, the query may start generating completely different execution plans with degraded performance. Behavioural changes are challenging to capture as they will not cause the query to fail but could return different data or run much slower.
These are the features that had been deprecated in the past and are now being removed. If we have a piece of code that relies on such a feature, it will stop working immediately.
Deprecated features mean the feature is no longer being actively developed and, whilst still available in the current version, will be removed in future versions. These don’t immediately break our code, but we should not be deploying production application that relies on deprecated features.
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.
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:
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.
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”.
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.
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.