There’s not much reason to run 32bit at all anymore. All new hardware is 64bit, though you can install a 32bit OS on it. The differences in configuration and management, other than max memory allowed and how virtual address space is capped/divided, are not major but can be annoying if you don’t account for them.
There are three basic Windows platform architectures: 32-bit, x64, and Itanium (IA64). The x64 systems have something called Windows on Windows (WOW) which allows them to run 32-bit applications with 4GB of virtual address space per application (all of it user address space - there is no split between user and system as there is on true 32-bit). IA64 cannot execute any 32-bit instructions.
Advantages with 64-bit:
· For Analysis Services (AS) 64bit is the only way to use more than 3-4GB of virtual address space (because AS doesn’t have an AWE concept). The only way for AS to even get 4GB of virtual address space is to run the 32-bit version of AS on x64. But you might as well run the 64-bit version and get even more memory available to AS (if the extra physical memory is on the box.
· If you develop on the same platform you use on production you are less likely to run into differences in production. There are some items listed below that have not (as of SQL Server 2005) been ported to 64-bit and you wouldn’t want to develop things on a 32-bit production system that either didn’t work or worked differently in a 64-bit production environment. So everything from test to production should ideally be on the same platform.
· All other things being equal, more databases can be mirrored on 64-bit than on 32-bit.
· Eventually there won’t be a 32-bit version of SQL Server. This is not any sort of official announcement, just a statement of the obvious.
If you are using 32bit in development, there are few things that will change for 64-bit production. This is a pretty good list of changes, although I’m sure I didn’t cover absolutely everything.
· Configurations such as max server memory, mem to leave (not used on 64-bit), whether AWE is enabled, and AS’s Memory Conservation Threshold will need to be changed. However, that’s true of any difference in memory between boxes.
· AS has several options that may need to change when the memory increases significantly.
· Lock pages in memory is only supported for Enterprise Edition on 64bit, it’s supported for every edition in 32-bit (because it’s required for AWE). If you are on an x64 box you really, really want to have this enabled for the SQL account. Make sure you’re on at least SP2 of Windows 2003, you have the latest driver firmware, and if you’re on enterprise edition of SQL, set the lock pages in memory permission for the SQL startup account, and cap “max server memory”: 918483 How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483
· Drivers/Providers
1. If you code your SSIS, AS, 3rd party replication, or linked server queries/packages/connections to use a 32bit driver in development, you will have to make sure they use the equivalent 64-bit driver (if one exists) in production. Take a look at the SSIS properties Run64BitRuntime and PreCompile (they work with providers/drivers which follow the standard of the same ClassID for 32-bit and 64bit). Even in the WOW on x64, BIDS will only show the 32-bit drivers.
2. If you use IIS as a middle tier, the drivers installed on it must match the IIS server’s platform, regardless of the platform of the server it connects to. For example, an x64 IIS box connecting to 32-bit AS would have the x64 AS drivers and x64 msmdpump.dll installed.
3. Some drivers/providers such as Jet (used for Access and Excel) and SQL Server 2000 do not come in 64-bit versions.
4. To use Oracle drivers on x64 boxes where you also have 32-bit drivers/providers installed, you need to make sure you have Oracle driver versions which are patched to avoid problems with (x86) in path names. See Oracle bug 3807408.
5. The first ODBC driver for 64-bit will be in Windows 2008, at this time it doesn’t look like it will be made available for earlier versions.
· 32-bit features are installed on x64 (they cannot be installed on IA64) boxes in a directory with a folder called “Program Files (x86)”. This may break some applications (such as older Oracle drivers).
· You cannot run BI Development Studio (BIDS) on an Itanium (IA64) box. You can only run it on 32-bit or in the WOW on x64.
· SQL Mail is only supported on 32-bit. There may be some additional steps necessary for Database Mail to fully work on x64.
· Standard Edition 64-bit tools don’t support Query Designer/Editor, MDX Query Editor/Intellisense, Version Control Support, XMLA, SMO, WMO Configuration Provider, Master/Target Server, and the AS Slowly Changing Dimension Wizard.
· The SQL configuration options “affinity64 I/O mask” and “affinity64 mask” are only available on 64bit installations.
· Reporting Services 2005 32-bit Standard and Enterprise Edition are not supported on an x64 system’s WOW (though you can install 32bit Express or Workgroup in the WOW). Reporting Services 2000 is not supported on 64-bit at all.
· Reporting Services 2005 IA64 can be installed on an IA64 box but integrated Report Designer and Model Designer are not available.
Things that will NOT change
· Backups can be restored back and forth between 32-bit x64, and IA64with no problems.
· SSIS command line utilities (dtexec, dtutil, dtswizard) exist for 32-bit, x64, and IA64. You can optionally install the 32-bit runtime support for SQL 2000 DTS packages.
· Most Management Studio (SSMS) functionality is available in 64-bit.
· Replication works the same (you may need to consider driver issues) for the average scenario. If you replicate with CE/Mobile Edition or use Windows synchronization features of replication there may be some differences.
References
BOL Topics
· Features Supported by the Editions of SQL Server 2005
· Integration Services Considerations on 64-bit Computers
· Installing Reporting Services on 64-bit Computers