Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Aug 31 '12 at 10:46

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer 1

up vote 10 down vote accepted

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:

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/08/11/sql-server-2008-r2-a-quick-experiment-in-unicode-compression.aspx

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/08/23/sql-server-2008-r2-digging-deeper-into-unicode-compression.aspx

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/04/more-testing-of-unicode-compression-in-sql-server-2008-r2.aspx

share|improve this answer
1  
Thanks Aaron. Just perfect articles i was looking for. –  AJ01 Aug 31 '12 at 12:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.