Today, 64-bit processors have become the standard for systems ranging from the most scalable servers to desktop PCs. The way to take full advantage of these systems is with 64-bit editions of Microsoft Windows products and Microsoft SQL server.

The 64-bit systems offer direct access to more virtual and physical memory than 32-bit systems and process more data per clock cycle, enabling more scalable, higher performing computing solutions. There are two 64-bit Windows platforms: x64-based and Itanium-based. X64 solutions are the direct descendants of x86 32-bit products, and are the natural choice for most server application deployments—small or large. Itanium-based systems offer alternative system designs and a processor architecture best suited to extremely large database and custom application solutions. Microsoft SQL Server 2005 Standard and Enterprise Editions both offer dramatic improvements in memory availability and parallel processing performance when compared to SQL Server software running in a 32-bit environment. We need to understand that Applications and data are growing in complexity and size. Many applications running on 32-bit platforms today are approaching or have exceeded the limits of the platform, specifically regarding the number of processors and addressable memory. Advanced DBMS capabilities, particularly in the areas of scalable architecture and high availability, have become a business necessity and are increasingly common as more organizations gather and analyze data from numerous data sources, and serve that data to growing numbers of business users and organizations are consolidating servers to simplify critical data center operations.

The primary differences between the 64-bit and 32-bit versions of SQL Server 2005 are derived from the benefits of the underlying 64-bit architecture. Some of these are:

• The 64-bit architecture offers a larger directly-addressable memory space. SQL Server 2005 (64-bit) is not bound by the memory limits of 32-bit systems. Therefore, more memory is available for performing complex queries and supporting essential database operations. A 32-bit system can directly address only a 4-GB address space. Additional memory is indirectly accessible by using Address Windowing Extensions (AWE) on 32-bit platforms Windows Server 2003 SP1 running on the Intel Itanium 64-bit architecture supports up to 1,024 gigabytes of both physical and addressable memory

• The 64-bit processor provides enhanced parallelism, thereby providing more linear scalability and support for up to 64 processors, and yielding stronger returns per processor as compared to 32-bit systems. Improvements in parallel processing and bus architectures enable 64-bit platforms to support larger numbers of processors (up to 64) while providing close to linear scalability with each additional processor. With a larger number of processors, SQL Server can support more processes, applications, and users in a single system. Server platforms that offer more than 32 CPUs are available exclusively on 64-bit architecture. The highest TPC-C benchmark figures for SQL Server have been achieved on 64-bit systems because these systems leverage both large amounts of memory and the superior scaling of 64-bit processors on the 64-bit architecture.

• The improved bus architecture enhances performance by moving more data between cache and processors in shorter periods. The bus architecture on current 64-bit chipsets is faster and wider than earlier generations. More data is passed to the cache and processor; this is somewhat analogous to the improvement that broadband connections offer over dial-up connections.

• A larger on-die cache allows for faster completion of user requests and more efficient use of processor time.

• On 32-bit systems, Analysis Services is limited to 3 GB of maximum physical memory. The 64-bit platform can help eliminate this limitation. It can also help to create or accelerate large online analytical processing (OLAP) systems with rapid response time requirements.

• The 64-bit platform facilitates the consolidation of multiple databases and applications to fewer servers. By hosting multiple databases on a single 64-bit system, you simplify management, improve storage utilization, and generally improve operational efficiency. Such consolidation efforts have limited capabilities on the 32-bit platform given the restrictions on memory availability and general absence of advanced hardware partitioning capabilities on 32-bit systems.

• The 64-bit platform allows you to “scale up” current applications that are experiencing significant growth; migrating existing database servers that are meeting platform limitations doesn’t affect the other tiers of multi-tiered applications.

• 64-bit servers can replace legacy UNIX or mainframe systems/applications. The 64-bit platform offers a powerful and reliable alternative to UNIX/mainframe systems for high-end database servers.

• Index creation operations benefit from the existence of larger addressable memory. This is because the entire sort can be done in memory. It is not necessary to write intermediate result sets to disk prior to performing the final write to disk of the actual index.

• Full-text indexing is restricted to either 2-GB or 3-GB workspaces for 32-bit computers. (In practice, since memory is also used for other processes, this restriction is notably lower than 2 or 3 GB.) This creates a potential bottleneck for creating or rebuilding very large Full-text indexes. Early deployments of very large full-text search systems can show orders of magnitude gain in speed when rebuilding indexes for these systems.

• Complex queries that use sorting or large hash joins or hash aggregates perform significantly better when sufficient directly addressable memory is available. Hash joins are very efficient, but can be memory intensive. When there is memory pressure on the system, hash joins can be removed from the cache in favor of memory-conserving, but slower, alternative query plans.

• Systems that have large numbers of active stored procedures will benefit through improved plan caching capacity. The 64-bit architecture can substantially reduce overall CPU utilization and latency by eliminating the need to evict procedures from cache and compile frequently.

• Server-side cursors, which are often heavy consumers of memory, can more readily be kept in memory, thereby resulting in better performance.

• Operations such as aggregation and sorting need to work with the entire datasets. These operations can benefit from the increased memory support provided by the 64-bit platform.

• SSIS components that perform row-by-row operations such as computing new columns, converting data, character conversions, and look-up joins to reference tables benefit from the enhanced performance provided by the 64-bit platform.

• SSIS tasks that perform aggregation, sorting, and fuzzy lookups, need to work with the entire dataset and benefit from the increased memory.

Migration

The migration of database from SQL Server 2000 (32-bit and 64-bit) and SQL Server 2005 (32-bit) to SQL Server 2005 (64-bit) is a straightforward task, as both share the same on-disk data and log file format. The mechanisms available include using detach and attach or backup and restore.

Detach/Attach

System-provided stored procedures are available to detach (sp_detach_db) the database from the 32-bit version, then attach (sp_attach_db) to the 64-bit instance. This can also be done in a GUI environment by using SQL Server Management Studio.

Backup/Restore

The same process for a standard backup and restore can be used to migrate data to the 64-bit platform, by simply restoring 32-bit backup files to the 64-bit platform. This can be performed by using Transact-SQL commands or via GUI tools. It is also possible to migrate data back to the 32-bit version for SQL Server 2005 databases. However, downgrading to SQL Server 7.0 from SQL Server 2005 (64-bit) is not supported. SQL Server 2005 can also perform the migration by upgrading an existing SQL Server 2000 64-bit database when performing an “in-place” install. Because SQL Server 2005 tools can be installed onto the 64-bit platform, it also possible to use the Copy Database Wizard (CDW) to bring a SQL Server 2000 database over from another server and attach it automatically. CDW can also script-out the SQL logins and passwords from the SQL Server 2000 installation and re-create them automatically on the destination 64-bit SQL Server. This also applies to any user-defined jobs that were related to the upgraded database.

Compatibility issues

SQL Server 2005 (64-bit) is fully compatible with SQL Server 2005 (32-bit), enabling easy integration in existing infrastructure while providing support for the extended 64-bit hardware. The product offers complete compatibility with the SQL Server 2000 (32-bit and 64-bit) database and transaction log files. Unlike SQL Server 2000 (64-bit), SQL Server 2005 management tools and client components are available on 64-bit either natively or in WOW. There are a number of enhancements and component changes with the 64-bit versions of SQL Server and Windows Server 2003. Components that used to be part of SQL Server set up, such as Microsoft Data Access Components (MDAC), Distributed Transaction Coordinator (DTC), and Microsoft Management Console (MMC), are now installed as core components as part of the Windows Server 2003 set up. This enables SQL Server 2005 (64-bit) to take advantage of these operating system resources.

Reporting Services considerations

SQL Server 2005 Reporting Services supports 64-bit processors, including the Intel Itanium2 processor and also the x64 architecture from AMD and Intel. On x64 systems, Reporting Services can run in both native 64-bit mode and also the 32-bit Windows on Windows (WOW) subsystem. In general, 64-bit systems running at the same processor speed will not increase the throughput of reports. Instead, the primary benefit is that users can view and export the output of larger reports. You might get better throughput on 64-bit machines at higher workloads, because contention