Sql-server – Risks involved in upgrading to SQL Server 2008 R2

sql serversql-server-2005sql-server-2008-r2upgrade

We have quite a number of SQL Servers that need to be upgraded from version 2005 to 2008 R2. Work is planned before the middle of the year as Microsoft is ending its support for the same.

The 2005 SQL Servers are all SP3 and SP4, running on Windows Server 2003 (whose support has already ended, but we have exception of extension for one more year), but where required we might go with a server OS upgrade as well.

These servers include replication (transactional), log shipping, reporting services, and an integration server running SSIS packages.

My question here is not how, rather I would like to know the risks involved or any pre-checks that can be done before planning this upgrade?

Also, will an in-place upgrade be a better plan than side-by-side for this migration/upgrade?

Best Answer

That's a really big question so let's break it up a bit.

What can I do in advance?

Start with some required reading.

These links have links to further information such as

  • Deprecated SQL Server Features
  • Discontinued SQL Server Features
  • Breaking Changes
  • Behavior Changes to SQL Server Features

Read each of them to see what major stuff is changing. Pay particular attention to features you are using.

In addition you should use the Upgrade Advisor. It checks for installed components and identifies those you will need to fix either before or after the install.

In-Place vs Side-by-side

Lots of pro's and con's here on both sides.

In-Place

Pros

  • MUCH easier. All of your configurations stay the same for example. Also the connection strings for your applications probably won't need to be changed.
  • Cheaper. No second set of hardware required.

Cons

  • Backout is difficult to impossible. If something goes wrong you are going to have to power through and finish because backout involves creating a whole new server and reinstalling SQL then restoring backups of your tables.

Side-by-side

Basically the pros & cons are the opposite of In place.

Pros

  • Safer - If something goes wrong you kill the new version and just continue on with the old one. Then you can try again later.

Cons

  • It's more expensive because you have to create a new set of instances probably on new servers.
  • It's more difficult because you have to change connection strings, make sure all of your configurations are the same, etc.

Now you can mitigate the expense of the side-by-side by creating a new instance on the same server, moving everything to it, then uninstalling the old instance. It works and depending on your situation might be the best idea.

General Risk

Honestly the move from 2005 - 2008 R2 isn't that bad. It's nothing compared to 2000 - 2005 or 2008 R2 - 2012 (mostly SSIS changes). I'd say with careful planning and reading you should be in good shape.

Related Question