x64 Windows - Upgrade from 32bit SQL Server to 64bit SQL Server

  • Article

Many people are now upgrading from 32bit to 64bit SQL Servers. Most of you have a match between your operating system and your SQL Server platform. For example, most of you install a 32bit SQL Server on 32bit Windows, and if you have the x64 platform of Windows, you usually install the x64 SQL Server. But what happens when you have a 32bit SQL Server on an x64 system and you want to change it to be al x64? Note that you cannot install 32bit SQL Server on IA64 so this scenario does not apply to Itanium systems. In the example below both the platform and the version of SQL Server are changing.

 

You have an instance of SQL Server 2000 32bit installed on Windows 2003 SP2 x64. This means SQL Server is "running in the WOW". WOW stands for Windows on Windows and means you have a 32bit application running inside a 64bit OS. This gives SQL Server a full 4GB of user addressable virtual memory space, which is more than any 32bit application can get on a 32bit OS without memory mapping (in SQL we do memory mapping of the buffer pool through "AWE"). However running in the WOW doesn't give you the full memory advantages you would get from running a true x64 application on an x64 OS. SQL Server 2000 was not released in an x64 "flavor", but once you upgrade to SQL Server 2000 SP4 Microsoft will support running it in the WOW. SP4 was required for this particular configuration even before we discontinued support for SP3. See 898042 Changes to SQL Server 2000 Service Pack 4 operating system support https://support.microsoft.com/default.aspx?scid=kb;EN-US;898042 Generally you should avoid installing 32bit applications on x64 systems whenever possible. Any recently purchased hardware will be x64 and putting a 32bit OS on it will throttle back its memory capabilities, so your best bet is going to be an x64 version of SQL Server on x64 Windows.

 

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

Comments

  • Anonymous
    July 22, 2009
    Nice Guide Wendy;Do you have a guide to upgrading a cluster? As you can't mix x64 and x86 operating systems in a cluster this adds such a level of complexity its difficult to know how the same rules you set out here do or do not apply...For example, we have an Active/Passive Win2003 x86 cluster, with SQL 2005 x86. We are keen to move to an x64 o/s platform, and also SQL 2008 x64, but are nervous about touching our db cluster in respect of the number of changes required and the severe amount of downtime this looks like it will create.
  • Anonymous
    July 22, 2009
    my concentration is not all there- my apologies, i did mean to write Cindy, not Wendy. :)
  • Anonymous
    November 14, 2011
    Complicated but well documented and well presented
  • Anonymous
    July 15, 2012
    Cindy follwed your stepd for option 1 above and everything was going good until after restoring the master database. I cannot launch the SQL service now as it is looking for the mssqlsystemresource.ldf and .mdf in the old installation directory. Exact error messages are:FCB::Open: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:Program Files (x86)Microsoft SQL ServerMSSQL.1MSSQLDATAmssqlsystemresource.mdf'. Diagnose and correct the operating system error, and retry the operation.FCB::Open failed: Could not open file C:Program Files (x86)Microsoft SQL ServerMSSQL.1MSSQLDATAmssqlsystemresource.mdf for file number 1. OS error: 2(The system cannot find the file specified.).FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:Program Files (x86)Microsoft SQL ServerMSSQL.1MSSQLDATAmssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.Any idea what can I do to get things pointed back to the correct installation directories?
  • Anonymous
    July 15, 2012
    If your directory structure is different for the new system you will need to follow the BOL steps for modifying the location.How to troubleshoot Error 17204 and 17207 in SQL Serversupport.microsoft.com/.../2015754Move System Databasesmsdn.microsoft.com/.../ms345408.aspx
  • Anonymous
    May 12, 2013
    Thank you very much!
  • Anonymous
    December 11, 2013
    I loved the information
  • Anonymous
    March 04, 2014
    Hi Everyone,Would like to ask for guidance to upgrade both the windows OS and SQL version and platform.Current configuration:Server: windows 2003 Enterprise R2 32 bitSQL: SQL 2005 Ent 32 bitUpgrade to:Server: Windows 2008 64 bitSQL: SQL 2008 standard 64 bitThanks & regards
  • Anonymous
    June 02, 2014
    What about SQL Server 2008 R2 32 bits to SQL Server 2008 R2 64 bits?
  • Anonymous
    September 08, 2014
    Cindy, you seem to be the expert on this. Need your help. I have a disk SQL 2005 Standard edition. Every time I load it on to a Win7X64 machine, it installs it as a X86 program. How do I use the same disk to install it as a X64 program. It never asks me the question, whether to install as 32bit or 64bit. Do I need to actually but a 64bit version SQL 2005?
  • Anonymous
    November 03, 2014
    Hi Cindy,I have the same kind of question/issue as JoyG, I´m trying to install the sql server 2005 64bit  onto a Windows 7 enterprise edition (64bit) machine with no results.whatsoever, It always gets installed the x86 distribution. Any ideas of how to do it?, or necessarily in order to install the 64 bit version of sql server 2005 i have to do it over a window server platform?Thanks,
  • Anonymous
    May 05, 2015
    The comment has been removed