I have to install the Service Pack 3 on an SQL instance running with the SP1. No test, the install is directly on the production server. Well this is not a complex task and the topic has been already ask for other version, but I'd like to be sure to not forget some steps and having feed back from experienced dba is always welcome. Here my custom planned checklist :
1.list all bugs that have been addressed in the Service Pack.
ie:https://support.microsoft.com/en-us/kb/2509302
2.Take a virtual machine backup and backup all the databases.
3.Disable SQL Server agent.
4.Apply the Sp.
5.Restart server/instance if needed.
6.Check for the Service Pack level of the SQL Instance.
7.Check for all entries in SQL Server Error Log about the Service Pack update and ensure there are no errors written.
8.Refering step N°1, check for eventuals bug issues.
9.Enable SQL Server Agent.
From the client side :
- Check for any compatibility problem between his software and the SP3.
- After the install, check for client connectivity between DB/software.
Is there any step you'll find pertinent to add/remove/modify to this ?
Best Answer
This seems to be a pretty exhaustive list in your question. Service Packs are generally speaking safe affairs - but every so often things can go wrong.
Ideally you would run on a test instance and make sure things are good. Since you can't do that - making sure you take backups is key.
Stopping the SQL Server agent isn't necessary - but if you have many jobs and are trying to make sure a job doesn't kick off just before you start, etc - that is a good plan. Again not necessary, though.
I also like to read the notes on the CUs released after a Service Pack and look for details about potential issues. It doesn't happen all the time, but sometimes a SP ends up breaking something and that then gets fixed in a later CU. In your case you are going with a fairly late release because Microsoft hasn't been generally releasing CUs - only security updates for that product with the current release now 3 versions higher. You can read the list of versions and builds and service packs and fixes post service packs here.