Risks involved in upgrading to SQL Server 2008 R2

Question

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?

Answer 1

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.

• 2008 R2 Backwards Compatibility
• 2008 Backwards Compatibility

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.

Answer 2

So my question here is not how, rather will like to know the risk involved or any pre-checks that can be done before planning this upgrade?

You should run upgrade advisor and address the issues reported by it before migrating.

Refer to my answer for an extensive list of pre and post upgrade steps.

SQL server which needs to be upgraded from version 2005 to 2008R2

You are picking the a path where you will be back to square 1 (since within 3 years, you will have to upgrade again). See below table

will Inplace upgrade be a better plan or side by side for this migration/upgradation?

Based on my experience, I would suggest you to do a side-by-side migration since you are getting new OS and SQL version. This is a much cleaner approach since you will have your old servers still around, just in case you want to failback. Refer to : Are SQL Server in-place upgrades as ill advised as they used to be?. Dont get me wrong when I suggest a side-by-side migration, its just a safer side when it comes to rollback.

Answer 3

IMO if you are going to the effort to migrate then you should go all the way to 2014 even if you run it in 10.0 compatibility mode.

You are going to pay for the license anyway. Also the regression testing effort and developer/DBA learning curve will be significant in either case. If you stop at 2008R2 now you will just have to repeat the exercise again in a couple of years. 2008R2 has already seen its last Service Pack and in a few months (weeks) will be 3 full versions behind the current version.

I am recommending my organization move from 2008R2 directly to 2016 for the same reasons. I expect we will start a testing effort as soon as 2016 hits RTM.

BTW, I agree that a Side-By-Side upgrade is preferred. When I last did this exercise we ran the "Pre-Production" version in a Dev environment for about a month.