Upgrade SQL Server 2000 from x86 to x64?

Question

I have a SQL Server that's currently x86, and I'd like to upgrade it to x64. It's running on an x64 version of Windows, and I think it's as simple as the wrong version having been installed when the server was configured. It's a production server, so my biggest concern is getting the logins over unaltered, since every user has their own SQL login. I've done some searching, and found an Experts-Exchange thread that gave the following steps, though he never reported back if it worked.

1. detach databases - alluser dbs, model and msdb.
2. script logins (http://support.microsoft.com/kb/918992/) and jobs
3. save db files and scripts
4. unintall SQL
5. install sql x64
6. attach dbs
7. re attached dbs once files are in correct place.
8. run scripts for logins and jobs if msdb is not reattached.

Aside from the fact that you don't need to transfer the system databases to the new instance (so step 1 isn't quite correct, aside from scripting any special XPs or anything else you've created), can anybody think of anything else that needs to be done? More importantly, has anybody actually tried this? Can you share any stories or gotchas, or did it go smoothly?

Answer 1

I've done it several times on SQL Server 2005 and 2008 - without any problems as far as I can tell. So based on my experience, I wouldn't expect any troubles as long as you don't have any old things like extended stored procs that might be dependant on 32- vs. 64-bits. The database parts should be just fine.

Marc

Follow-up: referring to the other answer - I didn't have either SQL Mail nor large amounts of DTS packages to upgrade, so I cannot comment on those.

Answer 2

I did it and it was not fun. There were a few problem areas.

1. Sql Mail is not supported on x64 Sql Server. We were making extensive use of it at the time.
2. A lot of our DTS packages did not easily convert to SSIS. If you have any DTS packages, make sure you do plenty of testing before upgrading the server.