upgrade from SQL server 2005 to 2008R2

Question

upgrade from SQL server 2005 to 2008R2

sqlfriend

SSC Guru

Points: 52781
More actions
February 23, 2012 at 10:49 am

#391636

We have 1 SQL server 2005 sp2 server, we would like to upgrade it to SQL server 2008 R2.
We are preparing to have a new box for the upgrade.
What is the best route to go?
Currently we are planning to install the newest windows OS on the new box.
Then I wonder should I install SQL 2005 on it , then upgrade, or should I install 2008 R2, then restore the database from the production server to the new one?
which way is clean and easist?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

If you have a new box, install SQL 2008 R2 on that box. Restore the database(s) from backup. Then change the compatibility level and test the application against the database to confirm it works.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thanks, when you say change the compatiblity, you mean change to 2008 (100) correct?

Also I have another server, which hosts reporting server and its databases, I need to upgrade it from 2005 to 2008 R2, should I do the same way - install 2008 R2, then restore databases, report and reporttemp db?

It also has some subsriptions on it

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

sqlfriends (2/23/2012)
Thanks, when you say change the compatiblity, you mean change to 2008 (100) correct?

yes

Also I have another server, which hosts reporting server and its databases, I need to upgrade it from 2005 to 2008 R2, should I do the same way - install 2008 R2, then restore databases, report and reporttemp db?
It also has some subsriptions on it

This other server that is your report server is different. In that case, you need to make sure you have your reporting services key backed up (can be backed up from report manager). I would make sure you have all databases backed up and then do an in-place upgrade.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52781 More actions · Answer 4

This other server that is your report server is different. In that case, you need to make sure you have your reporting services key backed up (can be backed up from report manager). I would make sure you have all databases backed up and then do an in-place upgrade.

But we will have a new box with newer OS installed for this report server. (it is now windows 2003)

So how can I do a in place upgrade? I need to move it to another server any way.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Have you installed the lastest software updates for 2008R2 ?

http://www.sqlservercentral.com/articles/SQL+Server+2008+R2/70092/

and remember that ALTER DATABASE syntax replaces the sp_dbcmptlevel procedure

corrected spelling error

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

bitbucket-25253 (2/23/2012)
Have you installed the lastest software updates for 2008R2 ?
http://www.sqlservercentral.com/articles/SQL+Server+2008+R2/70092/
and remember that ALTER DATABASE syntax replaces the sp_dbcmptlevel procedure
corrected spelling error

Thanks, but I am not sure if this is the answer of my previous post.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

sqlfriends (2/23/2012)
This other server that is your report server is different. In that case, you need to make sure you have your reporting services key backed up (can be backed up from report manager). I would make sure you have all databases backed up and then do an in-place upgrade.
But we will have a new box with newer OS installed for this report server. (it is now windows 2003)
So how can I do a in place upgrade? I need to move it to another server any way.

If you are moving boxes anyway, then look at this

http://technet.microsoft.com/en-us/library/ms143724.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlfriend SSC Guru Points: 52781 More actions · Answer 8

Thanks, I will study it.

May come back with more questions.

FTdenali SSCertifiable Points: 5362 More actions · Answer 9

Hi,

I have a slightly different scenario, I have a SQL 2005 SP4 cluster instance that i need to upgrade to SQL 2008 R2. How do i go about doing this. When i run the initial setup and choose upgrade option, the installation media does not detect the SQL 2005 instance to be upgraded it only shows other existing SQL 2008 instances to choose from.

Did i miss something?

homebrew01 SSC Guru Points: 55548 More actions · Answer 10

Rather than dig up an old thread to post a different question, I think you'd be better served by starting a new thread.

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 11

If you have a new box, install SQL 2008 R2 on that box. Restore the database(s) from backup. Then change the compatibility level and test the application against the database to confirm it works.

Does this include the system databases? Any restore seems to be VERY picky as to require the exact version. Does this overcome that or are you only including the user databases? Thanks,