SQL Server 2005 to SQL Server 2014 upgrade. Risks/things to consider...

Erland Sommarskog

2015-03-30 21:36:15 UTC

Post by Jellyman
Can you help me please? I have an upgrade to do from Sql-Server 2005
to Sql Server 2014.
I am trying to do a bit of preliminary risk analysis.
Firstly I would like to know how I would do the upgrade. Should I
install 2014 on another box or the same box (or do I just upgrade
over the 2005 version - which seems risky)?

Obviously that depends on what hardware you have available. If you are
running SQL 2005, I kind of suspect that the hardware is a tad old, and
so is the OS.

My preference is certainly for new a installation rather than upgrading
an existing instance, as the lattter is more difficult to back out of.
Well, at least if the instance is on a physical machine. If you have it
on a VM, you can take a snapshot and revert to the snapshot if the upgrade
goes south.

Making a new upgrade, also permits you to keep the old instance for
references if there are queries that suddenly runs slow.

Post by Jellyman
How do I transfer all of
the objects from 2005 to 2014? I'm referring to the
tables/sprocs/views/etc already on 2005?

BACKUP/RESTORE.

Post by Jellyman
Anything else I should consider? I'm referring to risks of functions
that hav been deprecated things like that?

To that end, the is the upgrade advisor.

How many issues you can run into, depends on the current compatibility
level. If the current level is 90, there are a few features that have
been discountinued. If you are on level 80, it's more likely that you
will run into surprises.

SQL 2014 comes with a new cardinality estimator which is not always
on the mark (although, I think recent CUs has addressed some of the
issues). You only get the new CE, if you set the compatibility level
to 120.

--
Erland Sommarskog, Stockholm, ***@sommarskog.se