Patching SQL Server on Windows notes from the field

Reading Time: 5 minutes

After dealing with a few issues for a client and seeing a few questions on SQLHelp I thought I better do this post. Here’s my take on patching SQL Server on Windows essentials.

Main points discussed in todays post are as below.

• Quick recap
• Important information about SP’s and CU’s
• When to apply these patches
• Powershell
• Adding MDS, Polybase or R to an existing install
• R Services (In-Database)
• Failover Cluster Instances (FCI)
• Post patching
• Third Party encryption

Quick recap

Here is a brief overview about the patches that can be installed for SQL Server. For a more detailed overview you can review SQL Server documentation online.

Problems in SQL Server tend to be discovered and have a knowledgebase article (kb) raised against them. Subsequently, if a fix for these issues are found Microsoft will trelease a hotfix for them.

After a certain amount of time Microsoft bundles a collection of these hotfixes together into what they call a Cumulative Update. Each new Cumulative Update would have the last hotfixes in it as well as any new ones that have been deemed fit to be in the same update package.

Microsoft would periodically release Service packs for SQL Server. They contained all the same fixes as Cumulative Updates as well as any new ones which had passed testing. Sometimes they released new features with these Service packs as well, like new DMV’s.

However, a while ago Microsoft announced they probably only be doing Cumulative Updates from now on. But, just to confuse the situation after that announcement they did release some Service packs for various versions of SQL.

Important information about SP’s and CU’s

Now pay attention as this part is important. Especially since I have seen it being asked a few times on the SQLHelp hashtag recently. If you want to install a Cumulative Update for a particular Service Pack, you must make sure you have that Service Pack installed first.

You can view the latest updates for each version of SQL Server here.

When to apply these patches

I am fully aware that in larger organisations you probably have no control as to when SQL Server patching takes place. However, if you are responsible for patching SQL Server there are some things to consider.

My advice

I’m sure some of you are keen to apply these patches as soon as they come out. However, my advice is to wait a while because Microsoft have been known to recall patches for various reasons.

Now don’t wait for months because than you might end up getting in trouble with your boss or auditors. A week or two would certainly be better. For example, if you have to install new patches within a month maybe make a point of installing them the weekend before that month period ends

Maintenance windows

Always try and find maintenance windows when the databases are very quiet. For example, when no applications are querying any of the databases and no maintenance tasks are being performed.

Patching during reindexing has been known to cause problems in the past so try to avoid patching whilst reindexing is taking place.

If in doubt when you are about to patch an important production server, then it might be a good idea to check what is running in SQL first. You can then make sure no vital maintenance tasks or other queries are running.

In addition, you might want to give a script to others who you have entrusted to do the patching to give yourself peace of mind.

Patching timeframe

Another tip is to always give yourself a bit of extra time if you must inform the business the timeframe when you intend to install these patches. Main reason I recommend this is because you might have issues if Windows updates are being done at the same time.

On some Windows Operating Systems I have experienced issues with the Windows Update during server restarts. It has been known to stay at the “Do not restart your computer” screen for an hour until the Windows Update service times. After the timeout the server finally reboots.

I have seen this happen if Windows Updates are being applied at same time as patching SQL Server.

Powershell

I highly recommend scripting out your Service Pack deployments using Powershell or running the command on the servers at the very least. It will certainly make your life a lot easier.

In addition, there are some potential solutions you can use to automate patching online which you could investigate.

Adding MDS, Polybase or R to an existing install

Now, I have encountered a few issues with doing this recently. Especially with SQL Server 2016 installs. When I say R above I mean the R Services (In Database) feature in SQL Server 2016. In addition, the Machine Learning Services feature in SQL Server 2017.

For instance, you might have added one of the below three to an existing SQL Server install recently and experienced the below issues:

• MDS looks to have installed OK, but when your application team goes to use it they have issues
• Polybase service fails to start after an install
• MS Launchpad Service fail to start after an installation

To check this, you can run ‘Select @@version’ in SQL Server to get the current version number. Alternatively, look in the latest log file in the setup bootstrap folder. If you have a version number for SQL Database Engine you can compare on it on the website I talked about in a previous post here.

There is another thing to consider if you have installed these from the original SQL Server install set and have since patched. You will have to reapply the patches again to the same level to get the above three working properly.

Admittedly there could be other issues with Polybase and R but that is for another post.

R Services (In-Database)

In addition, there is something else to keep in mind if reapplying patches for R Services in SQL Server 2016. You will need to make sure you apply the correct cab files required. I highly recommend that if dealing with R you save all the cab files you think will need for R in the same folder.

The SQL Server install set and patch installers knows exactly which versions of the cab files it needs. Which brings me to one other point. Do not rename these cab files.

Failover Cluster Instances (FCI)

Now, if you are using Failover Cluster Instances (fci) there’s other things to consider. For example, if you’re using a vendor’s tool which creates a cluster resource like Idera’s Compliance Manager you might want to take that resource offline during patching to avoid any potential issues.

If you do this make sure it is logged somewhere in case your databases are audited.

There is another thing to keep an eye out for when failing over these instances to another node during patching. Sometimes third-party vendor tools that create a shared resource can have issues closing the related exe properly.

If this happens make sure it’s not due to the resource being in use first. For example, if it is a backup tool make sure a backup is not running. If it is just refusing to close, kill the process to allow the resource to failover properly.

Post patching

Now you may have issues with SQL Agent jobs after patching has been performed on your SQL Servers. For example, you might go into the office the next working day and find maintenance jobs have failed due to a reboot after patching.

I strongly recommend that before you automatically restart these jobs you check with whoever uses the databases first that they are OK with the potential performance impact of these jobs being run. Otherwise you might get complaints from users that vital business processes are running slowly.

If it is vital these jobs are restarted, then I recommend creating a SQL Agent job that runs when SQL Agent is restarted. Within this job put the logic in place to query the msdb database and identify which of these jobs got cancelled due to a restart and which step they were running at the time.

After identifying where these jobs were cancelled have the new job restart these jobs. However, restart them from the steps they were running at the time they were cancelled.

Third Party encryption

If you are using a third-party encryption tool and you have offline databases after patching do not panic. I recommend ichecking the encryption application and making sure the license key is in place.

There have been occasions previously where license keys have disappeared from the application taking the database offline.

Final word

Well that’s it from me. I’ve tried my best to just point out the essentials you need here. I hope this has helped.