December 15, 2016 at 11:27 am
All:
I have been asked to upgrade an existing SQL 2012 instance to SQL 2014.
This can be a simple operation of an in-place upgrade (after making sure we have good backups of everything), or a complex operation of clean install of a new instance, then migrating everything over (in this case large databases).
Our sense is that clean installs are preferred and upgrades are to be avoided, but this may be based on bad experiences people had 15 years ago with old versions of SQL.
Based on real-world experience, are 2012 to 2014 upgrades known to be problematic?
December 15, 2016 at 2:53 pm
dan-572483 (12/15/2016)
All:I have been asked to upgrade an existing SQL 2012 instance to SQL 2014.
This can be a simple operation of an in-place upgrade (after making sure we have good backups of everything), or a complex operation of clean install of a new instance, then migrating everything over (in this case large databases).
Our sense is that clean installs are preferred and upgrades are to be avoided, but this may be based on bad experiences people had 15 years ago with old versions of SQL.
Based on real-world experience, are 2012 to 2014 upgrades known to be problematic?
'
In the long run, a clean install has a lot less risk, because if things go sufficiently wrong during an upgrade, your recovery time is extended by virtue of the time required to restore things back to where you started. While a clean install is also somewhat more complicated, issue resolution may be easier because you can do a lot of testing prior to go-live and be a lot more confident that the actual migration will work well.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 21, 2016 at 1:07 pm
I never upgrade in place. If something goes wrong the backout is quite ugly.
December 22, 2016 at 2:32 am
Clean installs are always preferred. Easier to revert back if anything goes wrong to start.
On the 2012-2014 side, you MUST test before you do that upgrade. One of the changes in SQL 2014 was to the cardinality estimator. As a result of that most queries either get a slight performance improvement or stay the same, but a small portion (~5% in my experience) get a severe performance degradation. You need to identify those before you upgrade and fix them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 27, 2016 at 5:23 am
I normally never recommend an in-place upgrade due to the problems that can occur if it goes wrong.
The only exception is when it is a VM and that VM can be restored quickly if it doesn't work and ALL of the databases on the server have been tested and approved for the new platform. Most of the time this results in far more work than is reasonably practicable.
The best solution is side-by-side: Install a new server and migrate the databases over one-by-one until the process is complete.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply