It is very important to patch your SQL Server instance on a regular basis. It will keep your SQL Server instance up to date and fix the bugs you are facing in your previous patching version. By applying regular SQL patching, your SQL server environment will be more secure and less vulnerable to threats from outside of your organization.
Applying a patch on SQL Server is not a very complex process. You just need to download the latest stable patch available and run the downloaded media to start the patching wizard. Select the required options on different pages of the wizard and click on the finish button to start the patching process. Before going into details, let’s discuss what is patching and the different types of patches in SQL Server.
What is patching in SQL Server
Patching in SQL Server is the process of installing the latest patches/updates on the SQL environment to fix security issues and bugs or add new features. Microsoft releases these patches/updates for SQL Server at regular intervals. All the information about these updates is available on the Microsoft website. It is an important part of a DBA’s responsibility to keep their organization’s SQL Server environment up-to-date with the latest available SQL patch. Please note that SQL Patching is not the same as SQL Server upgrades. In SQL Server upgrade, Full version of SQL Server is upgraded to higher version, e.g, from SQL Server 2012 to SQL Server 2019.
What is a Service pack or Cumulative update in SQL Server?
A service pack is a complete package comprising multiple updates and patches, including previously released patches and hotfixes.
A cumulative update (CU) is a collection of minor feature improvements and hotfixes
Microsoft released service packs (SP) and cumulative updates for SQL Server 2016 and earlier versions. Since the introduction of SQL Server 2017, Microsoft no longer releases service packs (SP). Instead, they release cumulative updates in regular intervals of 60 days. Each Cumulative update (CU) consists of some bug fixes, improvements, or new features along with the previous cumulative update.
How to find latest patch applied in SQL Server
To find the latest patch applied in SQL Server. Open SQL Server management studio, connect to SQL Instance, Right click on the SQL Instance and open a new query and execute the following query.
Select @@version
It will show you the latest patch applied in SQL Server for that particular instance as shown in below screenshot.
Steps to apply SQL patch on standalone SQL Server Instance
SQL patching can be done on a standalone SQL instance, Always ON Availability group instances, and FCI (Failover Cluster Instance). But in this article, we are going to apply the latest SQL patch for 2017 (CU 31) on a standalone SQL Server 2017 environment.
It is not mandatory but it is better to stop the SQL Agent to make sure no SQL jobs start executing just before running the patch. Once the installation/patching is started, the SQL server will stop the SQL database engine service. The engine service will start automatically upon the completion of the patching.
To apply the latest SQL patch on the standalone SQL Server 2017, follow the steps mentioned below.
First, copy the latest CU available to the server and locate the path to the SQL Patch media. Right-click on the media and run it as administrator. The media extraction will start, and a new wizard will open as shown below.
Once the rule check in progress is complete, you will see the license terms page. Click on the ‘Accept the license terms’ checkbox, and then click Next as shown below.
On the Select Features page, all the SQL Server instances eligible for the update will be visible. You can select & deselect between different SQL instances. In our example, there is only a single SQL instance. Click the Next button.
The next screen will check for the services and applications that are using the files that the setup needs to complete the installation. When this check is complete, click on the Next button.
On the ready-to-update page, you will see a summary of the actions that will be performed during this update/installation. Read the Summary, and if everything is correct, click on the Update button as shown below to start the update process.
The last page will show you either the success or failure messages against each feature. If any feature fails to update, check the summary log file to find the reason for its failure. The path for the Summary log file is mentioned at the end of this page as shown in the below screenshot.
Validate SQL Server health after the update. Make sure that all the databases are online and the transactions are processing normally.
Conclusion
In this article, we explain the patching in SQL Server and shared the steps to install the SQL patch on a standalone SQL Server 2017 instance. At the time of writing this article, the latest cumulative update (CU) available for SQL server 2017 is CU 31, So we use CU 31 for this article. You can apply these steps to other SQL Server versions as well. I hope this article is helpful.