Upgrading SQL Server from 32bit to 64bit
Recently I have been seeing a lot requests for guide to upgrade SQL Server from x86 to x64 as customers are buying new hardware and everything seems to be coming with x64 processors. As I was researching this information I came a post by Cindy Gross that is very good guide to follow. See the full post here . Great job Cindy!
If you are moving to new hardware and don’t have to keep the same hostname or instance name it can be as simple as backup, copy, restore (detach, copy, re-attach). Plus several other things to think about like, users, user-defined data types, functions, etc to ensure your new system works properly.
If you do need to perform and in place upgrade Cindy a some very useful steps I have included below.
You want to upgrade this instance from SQL Server 2000 32bit to SQL Server 2005 x64 on the same box. You would like to keep the same instance name. However, we do not support an in-place upgrade from any 32bit SQL Server to any 64bit SQL Server. Additionally, you cannot restore system databases (master, model, tempdb, msdb) to a different version, not even a different service pack or hotfix level.
· Version and Edition Upgrades "Upgrading a 32-bit instance of SQL Server 2000 from the 32-bit subsystem (WOW64) of a 64-bit server to SQL Server 2005 (64-bit) on the X64 platform is not supported. However, you can upgrade a 32-bit instance of SQL Server to SQL Server 2005 on the WOW64 of a 64-bit server as noted in the table above. You can also backup or detach databases from a 32-bit instance of SQL Server 2000, and restore or attach them to an instance of SQL Server 2005 (64-bit) if the databases are not published in replication. In this case, you must also recreate any logins and other user objects in master, msdb, and model system databases."
· You cannot restore system database backups to a different build of SQL Server "You cannot restore a backup of a system database (master, model, or msdb) on a server build that is different from the build on which the backup was originally performed."
· If the SQL Server versions are the same, even system databases can be restored between different platforms (x86/x64). However, you do sometimes have to make one update to the msdb database when you do this (because often the SQL Server install path has changed, such as using "program files (x86)" on an x64 system). For non-system databases the version you restore to doesn't have to be identical, generally you can restore a user database to a higher version and the platform (x86/x64) is irrelevant. Error message when you restore or attach an msdb database or when you change the syssubsystems table in SQL Server 2005: "Subsystem % could not be loaded"
So in this case you have two basic options if you must keep the same server and instance name:
1. Upgrade, reinstall, attach
a. Make sure all users, applications, and services are totally off the system for the entire duration of the downtime
b. Upgrade SQL 2000 SP4 32bit to SQL 2005 (or 2008) 32bit (NOT x64! - that is not a viable upgrade path)
c. Backup all databases
d. Detach the user databases (the detach does a checkpoint to ensure consistency)
e. Make copies of the mdf/ldf files for user and system dbs
f. Uninstall SQL Server 2005 32bit (to make the instance name available)
g. Install SQL Server 2005 x64 to the same instance name and at the EXACT same version as what was just uninstalled
h. Restore master, model, msdb
i. Attach the user databases
j. If needed, run the update from Error message when you restore or attach an msdb database or when you change the syssubsystems table in SQL Server 2005: "Subsystem % could not be loaded"
k. Apply the appropriate Service Pack and/or Cumulative Update
l. Take full backups
m. Allow users back in the system
2. Reinstall, attach, copy system db info
a. Make sure all users, applications, and services are totally off the system for the entire duration of the downtime
b. Backup all databases
c. Extract all relevant information to allow re-creation of system database information. This includes logins/passwords, configuration settings, replication settings, linked servers (including login mappings), custom error messages, extended stored procedures, MSDB jobs, DTS/SSIS packages stored in MSDB, proxies, any objects manually created in any system database. If you go this route let me know and I'll double check that this list is complete.
d. Detach the user databases (the detach does a checkpoint to ensure consistency)
e. Make copies of the mdf/ldf files for user and system dbs
f. Uninstall SQL Server 2000 32bit (to make the instance name available)
g. Install SQL Server 2005 x64 to the same instance name.
h. Attach the user databases
i. Apply all the system information you extracted above including sync'ing users to the new logins.
j. If needed, run the update from Error message when you restore or attach an msdb database or when you change the syssubsystems table in SQL Server 2005: "Subsystem % could not be loaded"
k. Apply the appropriate Service Pack and/or Cumulative Update
l. Take full backups
m. Allow users back in the system
Happy upgrading!