SQL Server Service Pack Questions

Question

We are currently running our database server on SQL Server RTM; i.e. no service packs or cumulative updates have been installed.

We need to install the latest Service Pack for SQL Server.

I had the following questions based on this subject, I was told I needed to stand up new servers and transfer the databases to that server.

1. Will installation of a SQL Server service pack affect any of our production databases? We will do this in test first but still wanted to ask.

2. Do we need to restore the databases, after installation of a service pack? I will be doing a backup of the databases before we apply the SP, just want to know how it affects the database.

3. Are there any particular prerequisites we need to follow before the installation of service packs? Backup of course, but are there any others?

4. How can I determine if there are any known bugs in a given service pack? Should any Cumulative Updates be installed after the service pack?

5. Can I directly jump on to install SP3, instead of installing SP1 or SP2 first?

Answer 1

You do not need to stand up new servers to apply a SP.

1 - Will installation of SQL Server service pack affect any of our production databases? We will do this in test 1st but still wanted to ask

It should be fine but always apply them to a test environment and let them soak for a while just to be safe. Always a good idea to dig into the SP fixes and see what, exactly, they fixed.

2 - Do we need to restore the databases, after installation of a service pack? I will be doing a backup of the DB's before we apply the SP, just want to know how it affects the DB.

No, you apply the SP during a maintenance window, it will stop the instance, patch it, then restart the instance for you. Good idea to give the server a reboot if possible, that's personal preference though. There is no need to backup/restore any DBs. There will be a 5-10 minute outage while the instance is down.

3 - Are there any particular prerequisites we need to follow before the installation of a service pack? Backup, of course, but are there any others?

I'd take a good backup of your DBs just to be completely safe. Other than that, make sure your company knows that server will be unreachable for the time that you are patching.

4 - How can I determine if there are any known bugs in a given service pack? Should any Cumulative Updates be followed after service pack installation?

https://sqlserverupdates.com/ is a great resource for getting the latest SP and CU. As far as bugs, 2012 SP3 is very solid and I haven't heard of any major issues or deal breakers.

5 - Can I directly jump on to install SP3, instead of installing SP1 or SP2?

Yes, you can apply SP3, it encompasses all previous CUs and SPs. Then you'll need to apply the most recent CU for SP3 (CU6, currently).