SQL2005 32 bit to 64 bit migration path

Question

SQL2005 32 bit to 64 bit migration path

Viewing 10 posts - 16 through 24 (of 24 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

I agree with George. Test your move plan in a sandbox first. Detach, attach or backup restore both should work fine. I would also test the restore of the msdb to the x64 environment. I would not try that with the master database.

Here is a resource to read on the topic.

First is a powerpoint by a Microsoft MVP

http://www.charlotte-sql.org/powerpoints/Oct_16_Meeting.ppt

He basically points out the same two methods already mentioned in this thread. Another Microsoft doc says that it is a seamless transition (but did not provide how to do it).

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

You can restore, detach/attach from 32 to 64 bit no problem as the file formats are identical.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

The files don't change from 32 bit to 64 bit. Only operations in memory are different (using 64 bits instead of 32).

There are some driver issues in a few places, not sure how many are sorted in SS2K5 SP3.

WayneS SSC Guru Points: 95460 More actions · Answer 4

Lynn Pettis (1/29/2010)
.... 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.

see this link for how to script out the logins AND passwords. The password is the actual hash of the password, and it is loaded in with the "PASSWORD HASHED" option, so you can completely reload everything. It even loads the logins with the same SID, to help you eliminate orphaned logins.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

WayneS (1/29/2010)
Lynn Pettis (1/29/2010)
.... 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.
see this link for how to script out the logins AND passwords. The password is the actual hash of the password, and it is loaded in with the "PASSWORD HASHED" option, so you can completely reload everything. It even loads the logins with the same SID, to help you eliminate orphaned logins.

Awesome, I am going to have check this out as well.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Lynn Pettis (1/29/2010)
WayneS (1/29/2010)
Lynn Pettis (1/29/2010)
.... 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.
see this link for how to script out the logins AND passwords. The password is the actual hash of the password, and it is loaded in with the "PASSWORD HASHED" option, so you can completely reload everything. It even loads the logins with the same SID, to help you eliminate orphaned logins.
Awesome, I am going to have check this out as well.

That helprevlogin script is extremely helpful in migrating users. I have been using it for a couple of years now. It was helpful in moving users from sql2k to sql2k5 as well.

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

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

Agreed, sp_helprevlogin has got to be one of the most useful scripts in a DBAs armoury, and the best way to migrate logins.

I wouldn't leave home without it.

🙂

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

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

All

Thanks alot for the info. I will be testing both methods shown in:

http://blogs.msdn.com/cindygross/archive/2009/07/07/x64-windows-upgrade-from-32bit-sql-server-to-64bit-sql-server.aspx

I still can't find any official Microsoft note on this. Does anyone know if there is one?

Cheers

John

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 9

I think the official note is in there

"we do not support an in-place upgrade from any 32bit SQL Server to any 64bit SQL Server."

I'm sure that's noted in BOL somewhere. So essentially you're doing a separate install and moving dbs.

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

Steve

I want to migrate from SQL2005 x86 on W2K x86 to SQL2005 x64 on W2K x64 while keeping the same server name, SQL Server instance name and SQL Server version. Do you know of any Microsoft note that covers this?

In particular I would like to know if it is supported to restore master, msdb and model from SQL2005 x86 to SQL2005 x64. I know I can script out logins and jobs but I just want to know if restoring system databases is also an option.

E.g. Would it be supported to:

1. Backup all system and user databases and copy to another server or tape.

2. Trash W2K SP2 x86 server and rebuild as W2K SP2 x64 with same server name.

3. Install SQL2005 SP2 x64 with same SQL Server instance name.

4. Rebuild master database using x64 media.

5. Restore master, msdb and model databases from backups taken at 1 above.

6. Restore user databases.

Thanks

John