Sql-server – Upgrading SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3

service-packsql serversql-server-2012upgrade

We need to upgrade our production SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3. So which one would be more stable out of those 2 service packs and also how could this be achieved with a detailed step if possible.

Before upgrading the Production Server directly, we plan to upgrade our test servers which have SQL Server 2012 Enterprise 11.0.2100.60 version on it.

Best Answer

Microsoft recommends you update to both the latest SP and the latest CU which means SP3 and CU2. As a side note if you used WSUS with the right classifications ticked this is how it would be updated "automatically". I wouldn't recommend this but it does help when explaining your update policy to users.

For future versions you can also check http://sqlserverupdates.com/ which is managed by the Brent Ozar company. It's incorrect on some of the older versions of SQL Server but perfectly fine for SQL 2012+ (I've notified them and they thanked me but didn't fix the problem).

It's always highly recommended to test updates on lower environments first. I started keeping a list for fun and found literally 20 different ways for the update to fail. Problems occur and not infrequently, though you can almost always recover the situation without resorting to the backups.

Which reminds me, always, and especially before any update, make sure you have valid working backups of everything, especially including your system databases. If you use encryption keys and certificates (or SSRS) you should export and save those as well.

As to the process:

  • If you have a simple standalone install of SQL Server then it's just a matter of running it and clicking through; or running it from the command line with options to update all instances and shared components.

  • But if you use other features of SQL Server such as Replication, Clustering, Availability Groups, then you need to look up more specific instructions because the servers should be done in a specific order.

  • Also if you use Master Data Services you MUST check it's working afterwards as if the update touches its components it will stay non-functional until you go into the GUI and click to update, and this can't be tested or automated; thanks Microsoft.

I suggest starting to keep your own list of your update process and document the failures and resolutions you find as you go along, because you or your successor may need to refer to it later.

Related Question