SQL 2005 to 2008 R2

Question

Hi

We are planning to migrate our production server to another machine. Current production server is 2005 std , installing 2008 r2 on a brand new server.  My plans are

Plan 1:

1)Upgrade current production server ,say server A, to 2008r2 using 2008 r2 setup.exe
2) install sql 2008 r2 on the new server, keep the system database files in same location similar to server A( ie on server A  c:\sysDb, d:\userDb; on server B c:\sysdb, d:\userDb)
3) stop SQL server on server A
4) stop SQL server  on server B
5) copy system database from server A c:\sysDb to Server b c:\sysDb, user database, server A d:\userDbto Server b d:\userDb
6) Rename Server A to server AOld
7) rename Server B to Server A
8) shutdown both servers
9) Start the new Server A

Plan 2:

1) Install SQL 2008 r2 on the new server
2) Script all login, jobs, alerts etc.. from server A
3) Run the script generated in step 2 in  new server
4) detach and attach all user database from server a to server b
5) Rename Server A to server AOld
6) rename Server B to Server A
7) shutdown both servers
8) Start the new Server A

 

What’s peoples comment on these two approach, which one would you prefer. My plan is to go head with plan 1..

cheers

 

 

 

Answer 1

Hello,

PLAN 1.

You don’t have to upgrade the production instance. I would like to suggest leaving it intact, just in case something goes wrong.


PLAN 1 and 2.

Use the following links to implement the migration.

http://support.microsoft.com/kb/314546  (move databases, transfer jobs, alerts, packages, etc)

http://support.microsoft.com/kb/246133

Use Upgrade Advisor to analyze the production instance before migrating.

Hope this helps.   
 
Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

Hi

 

Thanks, Upgrade Advisor is not showing any issues. so hope everything will go fine.

The reason i was looking for Plan 1 is we have service broker sending messages between servers. by over writing Server Bs master and msdb database with server A's master and msdb database ( after upgrading to 2008 r2), we don't have to recreate/reconfigure any service broker details like rout, master key ect...

I should have specified in my last post, b4 upgrading server A to 2008 r2 , we will be take database file backup of sql server 2005 dbs(system, userdb). if anything goes wrong.. then remove the 2008 r2, install 2005, install sp, then overwrite system and user dbs with the backup file that we took prier to upgrade

Whats ur opinion..?

 

cheers

 

Answer 3

Hello,

If you have service broker installed on your actual production instance, then consider the following article:

http://msdn.microsoft.com/en-us/library/ms166055.aspx

I think with the procedure of this link, you can simplify your upgrade plan.

Hope this helps.   
 
Regards,

Alberto Morillo
SQLCoffee.com

Answer 4

Thanks.. Am i correct saying that ..to migrate service broker all i have to do is reconfigure the route and end points.  all other objects comes as part of the db restore right...

 

 

 

 

cheers

Answer 5

Hello,

Yes. Remember to configure logins and activate the Service Broker message delivery also.

Hope this helps.   
 
Regards,

Alberto Morillo
SQLCoffee.com