SQL2005 32 bit to 64 bit migration path

Question

SQL2005 32 bit to 64 bit migration path

John Burke-413068

SSC-Addicted

Points: 417
More actions
January 29, 2010 at 7:32 am

#136990

Does anyone know if there is a microsoft supported migrated path for moving from SQL2005 x86 to SQL2005 x64?
Thanks
John

Viewing 15 posts - 1 through 15 (of 24 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Can you give us a bit more information as to what you are looking for here? For the most part, moving from 32 bit to 64 bit is quite easy. There are areas of concern, such as linked servers to some applications where there are any 64 bit drivers.

ravikanth. Hall of Fame Points: 3472 More actions · Answer 2

Also if you have any SSIS packages which use 32bit drivers for connectivity make sure you have appropriate 64bit drivers.

John Burke-413068 SSC-Addicted Points: 417 More actions · Answer 3

Lynn

We plan to migrate from x86 to x64 to enable us make better use of the physical memory available to us. I wanted to know if there was a recommended way of doing this as I wasn't sure how the database files got converted to x64 format etc.

E.g. Would the method below be supported?

1. Backup all databases.

2. Trash W2K SP2 x86 server and rebuild as W2K SP2 x64.

3. Restore all databases.

Thanks

John

Alan. T. Hall of Fame Points: 3221 More actions · Answer 4

Another SSIS note: Script tasks are required to be compiled to the 64bit platform so they will need to be updated.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

So, you have 64 bit hardware running 32 bit OS and SQL Server? If this is the case, then yes. Backup all your databases, make several copies of the backup files in multiple locations. TEST the backup files to ensure they work. You should also script out all jobs in msdb. Probably all the logins (sans passwords) in master as well. You will have to deal with that part separately probably to set the passwords when adding thos back in to master.

If you are using any SQL Server logins, you will need to run sp_change_users_login after you getting the logins reentered and the databases restored.

Once you have the OS and SQL Server restored, restore the databases, logins, and jobs.

Do you have any SSIS packages?

Alan. T. Hall of Fame Points: 3221 More actions · Answer 6

I have been able to detach and reattach databases both ways. From 32 bit to 64 bit and back again. Quarterly I take the production databases from the 64 bit machine and copy them back to test to refresh and match test to production. Production is running 64 bit SP 2 2005 and test is 32 bit SP2 2005.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Alan. T. (1/29/2010)
I have been able to detach and reattach databases both ways. From 32 bit to 64 bit and back again. Quarterly I take the production databases from the 64 bit machine and copy them back to test to refresh and match test to production. Production is running 64 bit SP 2 2005 and test is 32 bit SP2 2005.

True, that works. The only issue I have here though, is that it appears that the server needs to be rebuilt from scratch. That is why I recommend several backups. You could take one backup of each database, move it to multiple locations. Detach the databases, move those files to another location. After rebuilding the server, move the mdf/ldf files back and after installing 64 bit SQL Server 2005, attach the databases.

Still need to script the jobs, logins, and other items stored in msdb/master that you need to migrate from th 32 bit to 64 bit SQL Server.

Alan. T. Hall of Fame Points: 3221 More actions · Answer 8

You are right I have gotten spoiled by having a virtual base SQL server image. Whenever I need a new server I just create a new virtual server and move the databases.

John Burke-413068 SSC-Addicted Points: 417 More actions · Answer 9

Lynn/Alan

Thanks for the info. There are no SSIS packages (thankfully). A couple more questions:

1. From what you have said it seems that restoring or attaching user databases from x86 to x64 works seemlessly, do you know if SQL Server is performing any x86 to x64 conversion behind the scenes.

2. Is it impossible to restore or attach system or msdb from x86 to x64?

Thanks

John

Alan. T. Hall of Fame Points: 3221 More actions · Answer 10

John Burke-413068 (1/29/2010)
Lynn/Alan
Thanks for the info. There are no SSIS packages (thankfully). A couple more questions:
1. From what you have said it seems that restoring or attaching user databases from x86 to x64 works seemlessly, do you know if SQL Server is performing any x86 to x64 conversion behind the scenes.
2. Is it impossible to restore or attach system or msdb from x86 to x64?
Thanks
John

1. Yes that is what I am doing, our test is x86 running server 2003 and our production is server 2003 x64. I would also strongly agree with Lynn before you start remove all users and take full backups of all databases and use this as you recovery plan prior to going back to the old machine.

2. I have not tried that. I always do an audit of all login information every time I build a new server so I only add the one I am sure I need to a new server and wait until the other are needed. And for MSDB I script all of the SQL jobs and many have files paths that may need editing and simply apply them one at a time.

Alan

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

You "might" be able to restore the msdb database, but I'm pretty sure that you couldn't do that for the master database (but then again I could be wrong). I hope some of the other heavy hitters here will take a look at this thread and add their 0.02 as well.

george sibbald SSC Guru Points: 104210 More actions · Answer 12

don't know about heavy hitter, does the occasional home run count? 🙂

This blog would suggest you can restore X86 system dbs to 64bit

http://blogs.technet.com/keithward/archive/2009/10/15/upgrading-sql-server-from-32bit-to-64bit.aspx[/url]

there seems to be a possible msdb bug, and normal caveats about exact same version of SQL, and I would not recommend it unless servers\instance name is the same.

---------------------------------------------------------------------

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

IMHO, I personally would not attempt to restore the system databases (master, model, msdb) from the 32 bit environment to the 64 bit environment.

george sibbald SSC Guru Points: 104210 More actions · Answer 14

I'd certainly want to test it first in a sandpit environment.

Only sensible to have scripted out objects as a fall back position, and backup the x64 system dbs first. I would also copy the original x64 system dbs off somewhere first, then if there are problems they can just be slid back into position.

More potential backout options the better,

And certainly if server name is not the same, don't even go there.

---------------------------------------------------------------------