Understanding SQL Server patching

Once you install SQL Server, you need to watch for future updates released by Microsoft. You can confirm which updates were installed to your server by checking the build number of the SQL Server deployment. You can find the build number in SQL Server Management Studio (SSMS), as you can see in the following screenshot, or via the SELECT @@VERSION command:

Fig. 2.1 – SQL Server Object Explorer (build version)

The build version of the currently installed SQL Server is 15.0.2070.41. We can parse this version into the following portions:

• 15–indicates we work with SQL Server 2019
• 2070.41—4517790 Servicing Update (GDR1) for SQL Server 2019 Release to Manufacturing (RTM)

  Note

  You can find a nice list of updates for all SQL Server versions at http://sqlserverbuilds.blogspot.com/ where you can identify the correct build of your SQL Server.

Based on the data available either on the Buildlist site or on the Microsoft.com site, which publishes the latest updates for all SQL Server versions (https://docs.microsoft.com/en-us/sql/database-engine/install-windows/latest-updates-for-microsoft-sql-server?view=sql-server-ver15), we can see that the latest update (at the time of writing this book) is Cumulative Update 2 for SQL Server 2019.

Historically, there were several types of updates released for SQL Server, as follows:

• Service packs
• Cumulative updates
• Security updates

When you work with older SQL Server installations, you can install all three types of update for your SQL Server environment.

Service packs are usually the largest update option for your SQL Server. They frequently include updates released by more cumulative updates and should be tested more thoroughly regarding performance and stability of the system. It's also important to keep your environment healthy with recent Microsoft system support service packs. Service packs not only fix issues but often also bring new features to SQL Server. A good example was the service pack 1 for SQL Server 2016, which enabled many features previously available only in the Enterprise and Standard editions. This had a tremendous impact on many smaller environments that were not utilizing the Enterprise edition, as database administrators (DBAs) and developers were able to start using many new features that were previously unavailable to them.

Cumulative updates are smaller compared to service packs and are released more frequently. They usually fix many errors and include more updates, undergoing the same comprehensive tests as service packs. As an example, we can see that SQL Server 2014 had only three service packs available, but for the first service pack, Microsoft had already released 12 cumulative updates with additional fixes for errors, performance, and stability. You can now install cumulative updates proactively with the same level of confidence as you would service packs. Cumulative updates are incremental in nature, so cumulative update 4 includes all the updates that were released in cumulative updates 1 to 3.

Security updates are smaller than cumulative updates and usually fix some sort of error or security vulnerability. These are usually released in a monthly cycle alongside the regular Windows updates and should be evaluated for your environment. Usually, a Chief Security Office (CSO) team or a security team in general may request you to install such security updates in a reasonable time frame to your SQL servers, which may be a complex task if you're managing larger environments. For patching such large environments, you most likely won't install any of these updates manually, but you'll utilize a centralized deployment tool such as System Center Configuration Manager (SCCM).

Note

Since SQL Server 2017, service packs are no longer used for SQL Server updates. The servicing model has been simplified to include only cumulative updates and security updates. However, while working with older versions of SQL Server, it is still important to understand both concepts.

Installing updates

If you need to install an update to your SQL Server, you first need to download the correct bits from the Microsoft site and store them locally in your SQL Server. Some updates are downloaded as .exe files and some are available as .zip files, so you need to extract the update.

When you first start the installation, it will automatically extract to a random folder on one of your drives on the SQL server, as shown in the following screenshot:

Fig. 2.2 – Service pack installation

This folder will automatically get deleted once the installation is over. This might not seem important at first glance; however, there are situations where you will need to reestablish the original hotfix structure to apply newer patches, especially with older SQL servers due to missing installation files on your system, as illustrated in the following screenshot:

Fig. 2.3 – Hotfix extraction

When you accept the license terms for the installation, you need to select the instance from the list to which you would like to install the update. You can see the list of features installed for each instance, and the last installation option is Shared Features. These include Integration Services, Data Quality Services, Client Connectivity Tools, and, on older systems, SQL Server Management Studio as well. Via the update installer, you can also see the current build and whether the update was installed or only partially installed due to some error.

Once you select the instance to which you'd like to install the update, you can proceed to the installation. It's common practice to restart the server after the installation. If you're installing more updates in a sequence, they perform system checks and one of them is Restart Pending anyway. After the restart, you need to verify that applications can correctly connect to the SQL server and there is no impact after the update installation. The following screenshot shows the dialog from the SQL Server 2019 setup:

Fig. 2.4 – SQL Server installation dialog

Note

For any high-availability (HA) solution such as failover clusters and mirroring or availability groups, you need to take into special consideration installing updates and following a proper sequence between primary and secondary nodes (with respective naming for all the HA/DR options (where DR stands for disaster recovery). For more information, consider checking books online to find detailed procedures relating to this.

If you are deploying many SQL Servers at once or very frequently, you might consider customizing your installation media to include the latest updates.

In complex environments, you can integrate the installation bits of service packs and cumulative updates in your installation source and use this modified installation to install new SQL Servers directly with proper service packs or cumulative updates. This will speed up your deployment, as the installation will already include the required service packs, updates, or security updates that may be required by your security or architecture team.