Sql-server – SQL Server 2008 SP1 to SQL Server 2008 R2 upgrade

sql-server-2008sql-server-2008-r2ssisupgrade

One of our project is planning to upgrade from SQL Server 2008 SP1 to SQL Server 2008 R2. I would like to know from this community the suggestions on:

  1. Is there any configuration or any change required for migrating the data?

  2. We have some SSIS packages so does this upgrading require any code change?

  3. Any other suggestions or best practices that we should use to get this done.

  4. What should be the approach?

Best Answer

I would slipstream Service Pack 2 (and perhaps Cumulative Update 2 as well) into the R2 installer prior to running the upgrade, especially if you have enabled the extended partition support. This guide walks you through slipstreaming (it talks about a different service pack and CU, but the process is the same). Slipstreaming will save you a bunch of time and service restarts, since you should be installing the latest service pack at least and not be leaving your new instance at RTM (it is no longer a maintained branch).

You shouldn't have to change SSIS packages.

If you can afford a side-by-side upgrade (e.g. you have another machine or adequate resources for another instance on the current machine, and can re-point your apps later), it will be slightly safer and require less downtime than an in-place upgrade (though it will be more work). You can install another instance, get your logins and jobs set up, then mirror or log ship your user databases to the new instances. When you've ready, you can fail over, take the old databases offline, then the new instance is the primary and you just need to re-point your apps.

Personally, I haven't had any problems with in-place upgrades, but I would probably opt against them if possible in a mission critical environment. The main point is if you do side-by-side and something goes wrong, you can take the new instance down and rebuild from scratch, and it doesn't affect your production system. If something goes wrong in the middle of an in-place upgrade, then you'll be in scramble mode... do we have current backups? Where do we restore them?

In either case, you'll want to make sure you take proper backups before the upgrade.

You will want to update statistics once the upgrade is complete, and if you're on Enterprise Edition and using data compression on tables with nvarchar columns, you may want to rebuild those indexes, since one of 2008 R2's primary advantages over 2008 in the OLTP world is Unicode compression. I wrote a few blog posts about this:

Related Question