SQL Server Service pack update

Question

I have to update the current SQL server installed in my PROD server from service pack 1 to 2. What all things i should consider before starting the upgrade and how to pre-determine if there are going to be any issues if we upgrade or what all will get impacted or where will it break or what precautions to be taken..does it need a restart ?

Need help in all various aspects - basically a step by step process would be very helpful for me as I will be doing it for first time.

Answer 1

Your question does not indicate you are using any type of HA/DR (Clustering, Availability Groups, Mirroring, etc.), so my answer is based on the assumption of a relatively plain install of Sql Server.

This list may not be all inclusive (and I’m interested in what others might do), but it’s a list I have cobbled together over the years from many different web posts. Since I don’t even remember where each item came from, my apologies to the original authors for not citing them directly.

• Review the Readme for Service Pack/cumulative update/hotfix. Any concerns/recommendations will be found in the readme.

• Always apply Service Packs and Updates on non-production Instances first. Once the Instance with the new Service Pack is up and running without any issues for a week or two, plan the same steps on your Production.

• Before applying any service pack or cumulative update, run SELECT @@VERSION to retrieve your current install level and save it somewhere. If you need to fall back from a failed upgrade, you’ll need this information.

• Backups, Backups, Backups! - As I understand it, Sql Server service packs and cumulative updates only affect ‘system’ databases, so you should ‘at least’ take full backups of master, model and msdb. I’m paranoid, so I usually back up my user databases as well. You should also run RESTORE WITH VERIFYONLY on all backups.
• Plan for failure. Depending on the type of failure, you ‘may’ be able to simply restart the upgrade and it will be successful. A failure during the upgrade may force you to uninstall the upgrade using Add/Remove programs AND force you to restore your system databases. If you haven’t taken the time to get comfortable with restoring the system databases (master, model, msdb), now is the time to practice that on a test server. A failed upgrade is not the time to try and learn that process. Search the internet for “sql server restore system databases”. Make sure you have the ‘original’ base install media and service pack/cumulative updates for your ‘current’ @@VERSION. You ‘might’ need to remove the current instance and reinstall up to the level you were at before the upgrade – then, you could restore your system databases and at least be back where you were before the upgrade.
• I usually stop the Sql Server Agent before starting the upgrade.
• Now it’s time to apply the service pack. Hopefully all goes well and you’ll get to a successful completion screen.
• You should immediately take new full backups (at least master, model, msdb)
• Restart the Sql Server Agent, if stopped.
• If possible, I usually restart the entire server.