patternsqlMinor
How far to go when Validating a SQL Service Pack install?
Viewed 0 times
installhowsqlpackvalidatingservicefarwhen
Problem
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?
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?
Solution
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...
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...
Context
StackExchange Database Administrators Q#148665, answer score: 7
Revisions (0)
No revisions yet.