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.
Best Answer
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.
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.