Patching SQL Server 2016

Question

Most of my SQL Servers are on 2016 SP2. The security group have identified that I need a later security update applied and as SP2 is out of support this year I planned to just patch it up to SP3.

The problem is I have never actually patched SQL Server and was wondering what a good process would be to follow when applying the service pack. I have seen some online, all roughly the same, but just need some opinions on the best way to go about it.

Some questions I have, from looking at these processes online are:

• I'm assuming the SQL Server instance will need to be restarted. Is this done automatically when installing the patch?
• Does all activity on the instance need to be stopped? I.e., do all application services communicating with the instance need to be shut down?
• Should the SQL Agent be stopped as well?

Answer 1

A lot of planning goes into deploying SQL patches, depending on the size of your estate and the HA solutions you're using. My patching routines have usually been executed in large corporations with 100s of SQL Servers and various application requiring high uptime. Your requirements may not be as stringent.

First, before you do anything you'll want to make sure you test your patches in a lower region, if you have one. Let them run there for a few weeks, and ensure you don't run into any issues. If you have QA teams, have them hit the application hard over that span, to really ensure no bugs have been introduced that impact your applications. The last thing you want to do is install a patch directly in prod, and take the whole application or databases down.

After patching your lower regions, you'll want to patch DR.

Finally, once you've patched in lower and DR regions, here's a general process from patching Production/DR.

Patching Standalone SQL Servers

1. For standalone SQL Servers, it's a good idea to first make sure no jobs are running and to stop the SQL Agent. You would not want a long running job to start just before patching, and then either need to wait for it to complete, or have to kill it mid-run.
2. Install the SQL patch and follow the prompts through the wizard.
3. Reboot the SQL Server.
4. Validate health following the reboot. Ensure all databases are back online and transactions are processing as expected.

Patching Availability Groups

1. Patch your secondary replicas first (including DR).
2. Pause data movement to your secondary replicas.
3. Patch your secondary replicas.
4. Reboot
5. Validate health following the reboot. Ensure all databases are back online and transactions are processing as expected.
6. Resume database movement on the secondary replicas.
7. After all secondary replicas are patched, failover the primary and repeat the above steps.

Patching FCI

FCI is probably the simplest to patch. SQL Server and SQL Agent are already stopped on your secondary nodes.

1. Install the SQL Patch on all secondary nodes.
2. Ensure no SQL Agent jobs on running on the Active node. If not, failover to one of the secondary nodes.
3. Validate the new primary is healthy, all databases came online and it's processing new transactions.
4. Patch former Primary node.
5. Failback to the original primary node and repeat validation.

Again, if you have a QA team, after you finish patching prod is a good time to have them test the app as well.

While you're at it, consider installing the latest Cumulative Updates.

Answer 2

To answer your specific questions:

• Im assuming the SQL Server instance will need to be restarted? Is this done automatically when installing the patch?

Yes. The installer will restart SQL Server instance at least once. Plan for that.

• Does All activity on the instance needs to be stopped? I.e all application services communicating with the instance need to be shut down?

It doesn't "need" to be stopped prior - but the instance will restart. Can your applications handle that gracefully? If not, plan for a window that lalows that.

• The SQL AGent should be stopped as well?

Like the database engine - this will go down and up as well. Make sure it's set to automatic start and comes back up.

An older blog post of mine has a few more details to consider on patching. In addition to the other good answers above.

Answer 3

It is good to have service window for patching. SP3 will require Server restart (will not do it automatically) but the SQL Service might become unavailable\restarted during the SP installation. You don't need to stop agent or anything.

If you have HA setup then it would be perfect to install SP3 on secondary node first and do failover.