how to upgrade from sql server 2008 SP2 to 2008 R2

in the current instance of 2008 server, we are planning to upgrade from SQL Server 2008 SP2 to SQL Server 2008 R2. What are the precautions do i need to take before upgrading the server. In repsect to database level and login level and any other.

asked May 25 '11 at 03:03 AM in Default

Murali
896 ● 64 ● 100 ● 117

(comments are locked)

4 answers:

Largely, it's the same as any other move. Are you doing an in-place upgrade or a side-by-side? If in-place, there's nothing to migrate, so your primary concern is simply to be sure you have tested backups in the event something goes wrong and you need to reset the system and restore the databses. With a side-by-side upgrade you'll need to plan on migrating not just the databases and logins, but SSIS packages and SQL Agent jobs. You need to document your database and it's processes thoroughly so you don't miss anything.

Plan for testing with either approach.

answered May 25 '11 at 03:09 AM

Grant Fritchey ♦♦
62.6k ● 12 ● 20 ● 66

(comments are locked)

A word of caution. SQL Server 2008 SP2 introduced support for 15000 Partitions, which R2 RTM does not have. So, if you are using that, you need to make sure you are on R2 SP1.

From the MSDN support document about 15000 partitions:

However, if support for 15,000 partitions is enabled, you cannot migrate the database to an instance of SQL Server 2008 R2 RTM. Because SQL Server 2008 R2 RTM does not support more than 1,000 partitions, the database with support for 15,000 partitions enabled has a later version than the SQL Server 2008 R2 RTM instance, which prevents migration. SQL Server 2008 SP2 databases with support for 15,000 partitions have version number 662, while SQL Server 2008 R2 RTM supports databases with version numbers up to and including 661.

answered May 25 '11 at 03:22 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

Good catch there Matt +1

May 25 '11 at 03:31 AM WilliamD

Talk about the edgiest of edge cases. You have a mind for extremes :)

May 25 '11 at 12:37 PM KenJ

@KenJ - I don't know where I heard about it either - I just remembered that it was an issue :) Now, if only I could remember that command for when you want to put a new row in a table...

May 25 '11 at 02:04 PM Matt Whitfield ♦♦

(comments are locked)

Just to add on a bit of support for @Grant Fritchey. R2 in technically another major release of SQL Server. Many folks felt it should have received a different version number than 10.x. With that said Grant is right on. Treat this as you would upgrading from 2005 to 2008. Make sure you have tested your database and applications prior to upgrading to R2 and when the time comes for the upgrade make sure you have validated backups of all your databases. Master, Model, MSDB, and your user databases. If it is a side by side upgrade then you are in much better shape for a backup out plan but will have a bit more work to do with migrating SSIS, users, linked servers, etc. Best of luck and let us know how it goes.

answered May 25 '11 at 04:41 AM