x

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.
more ▼

asked May 25, 2011 at 03:03 AM in Default

Murali gravatar image

Murali
906 101 117 120

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.
more ▼

answered May 25, 2011 at 03:09 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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][1]:

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.

[1]: http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1Af/Support_for_15000_Partitions.docx
more ▼

answered May 25, 2011 at 03:22 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Good catch there Matt +1
May 25, 2011 at 03:31 AM WilliamD
Talk about the edgiest of edge cases. You have a mind for extremes :)
May 25, 2011 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, 2011 at 02:04 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered May 25, 2011 at 04:41 AM

Tim gravatar image

Tim
36.4k 38 41 139

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x587
x84
x68

asked: May 25, 2011 at 03:03 AM

Seen: 3562 times

Last Updated: May 25, 2011 at 03:11 AM