When applying a Service Pack to a production SQL server, I usually have a planned downtime window of around 30 minutes.

Having been bitten before, and discovering that a required precondition like a patch is not in place, that can add several minutes to your downtime window (and maybe taking you over the window, or forcing a reschedule).

Now when the update is in planning stage, I move the Service Pack to the server and test it through "Check Files in Use" or "Ready to update". I cancel the update at that point, and feel as confident as I can that during the update, I will not run over the scheduled downtime window.

You can cancel from either screen, but as the "Update" button on "Ready to update" is in the same location as the "Next >" button on "Check Files in Use", an accidental double click could accidentally start an update, while validating.

My question:

Should I stop validation at "Check Files in Use" or "Ready to update"? Have I validated everything I can by the end of "Check Files in Use"? Does going to "Ready to update" add value to the validation?

enter image description here enter image description here

  • If you don't have a test server yet on which to test procedures like these, get that set-up first before applying any further upgrades. – Mast Sep 2 '16 at 14:23

Should I stop validation at "Check Files in Use" or "Ready to update"? Have I validated everything I can by the end of "Check Files in Use"?

Just go ahead click NEXT and apply the service pack, you can safely ignore this process. The check files in use process is to handle scenario where end user does not want to restart after applying service pack in that case you need to make sure all such processes are stopped, but in any case I strongly recommend to start windows machine/node on which you are applying service pack.

The only thing which can happen is after successful upgrade you need to start the windows machine. This as such is NOT going to cause SP to fail

Does going to "Ready to update" add value to the validation?

The ready to update basically shows you what all features you are going to update and nothing more than that. You have to click update here, it does not adds any value but would just show you what configuration you have choosen

| improve this answer | |
  • 1
    I am sorry it looks like my question was not clear. I was not asking if I should wait for files in use, and I am specifically NOT applying the service pack at this time. I am validating there will not be issues prior to starting the update at some time in the future where there is a scheduled down time. – James Jenkins Sep 2 '16 at 14:08
  • 1
    It has been pointed out to me that while the timing of this answer is slightly off from my scenario. It does indicate that there is no added value by going to the "Ready to update" window. I can stop at any point on "Check Files in Use" with the same results as far the actual update is concerned. I am accepting it, thank you. – James Jenkins Sep 6 '16 at 16:50

The ideal situation is to have a server that, in terms of OS and other software, exactly matches the production environment, upon which you can perform the update first. That way you know everything that is required.

This would preferably be a VM that you could snapshot, so if you encounter and fix a problem you can revert to the snapshot and start again with your updated procedure. Repeat this until the upgrade procedure works and you know the reboot requirements and such, then plan to repeat the process in production.

One of your development/testing VMs might be ideal for this if you have them (i.e. if your dev/test/release process isn't just "smash code together and throw directly into production"!). This way you are essentially treating the service pack the same as you would one of your own bug fix or feature releases meaning you can perform a full regression test on your application after the service pack is applied to the test environment (to make sure MS haven't introduced any bugs or changes to undefined behaviour that your application depends upon - or that they haven't fixed a bug that your code is depending on!).

Obviously this "ideal" could be more time consuming than other options...

| improve this answer | |
  • Concur, where a separate test system exists from the production, update the test first, then update production. The problem is usually seen on older servers that don't have a test system. But in reality, any sever can fall of the patch/update list, and you don't figure out until you do something like a service pack. I think we all try and have checks so that does not happen, but along comes Murphy, and well an ounce of prevention... Is there a chance of finding an issue between the two screens? – James Jenkins Sep 2 '16 at 14:06
  • @JamesJenkins Can you snapshot the "older server", spin it up as a VM somewhere, test on that, and then throw it away when you're done? Obviously requires having some hardware available, but if you can throw it away when you're done, much less than duplicating the entire fleet of servers. – jpmc26 Sep 2 '16 at 18:33
  • @jpmc26 wouldn't you also have to duplicate the OS as well as make sure both versions has all the same patches in place? – James Jenkins Sep 2 '16 at 23:53
  • @JamesJenkins I meant a full snapshot of the whole machine. That would include the OS. – jpmc26 Sep 3 '16 at 5:30
  • @JamesJenkins: I've used VMWare's converter to copy a running physical machine to a VM for testing in the past, and I assume similar tools exist for HyperV and so forth. Of course there may be licensing issues to worry about depending on how your company's Windows and SQL Server licensing arrangements are. – David Spillett Sep 4 '16 at 0:22

To answer your question you can skip the files in use validation; it doesn't ever block you from proceeding and is only there to inform you if you might need to reboot AFTERWARDS.

There are many more situations where you must still reboot and it won't tell you (usually .NET Framework related) so you're always going to reboot no matter what. Besides, if you don't reboot now you'll need to do it next month when the next pack comes out because that IS a patch blocker.

But to address the elephant in the room even if you're patching just one or two servers then you should allocate more time than 30 minutes; 60-120 minutes is about right especially if you have AGs/FCIs/mirroring/replication and Enterprise features. If you have a few dozen servers you can compress that into about four hours because you'll be partly automating at that stage and it's pretty rare that they'll all fail with completely different issues.

The reason you need more time is you never know what's going on with an ESX host, temporarily slow SAN, or the bugs in 2012 they allegedly fixed recently with slow update installs. Or you somehow forgot to remove SSISDB from an AG first and now it's hosed and you need to fix it. Or the repeated failures from MS screwing up instances with filestream so you have to go into Add/Remove Programs and do a repair before reapplying the update. Or you need to wait for the AG to come back in sync after patching (easily 30 minutes on a busy server), failing over and doing the replica.

What basic health checks have you automated? It takes a few minutes per server to run all the AG policy checks. If you're doing it by hand it's more; validating DQS MDS SSRS SSAS have all come back up and aren't throwing stupid errors.

I can fairly confidently say that while it's useful to test on QA first there has been many, many a time a patch has only failed in PROD because someone somewhere sometime did somethings differently.

Anyway the list isn't endless but it's definitely more than 30 minutes. You don't want to be looking at the clock while you're trying to fix a disaster just because you under quoted with a short time limit. I understand managers want to hear it - and that's why DBAs get paid the big bucks because we need to say No.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.