Since the release of SQL Server 7.0 in 1998, SQL Server has been on the enterprise fast track, easily clearing the hurdles that hindered the adoption of earlier SQL Server versions in the enterprise. Support for a new scale-out technology called distributed partitioned views boosted SQL Server to the top of the TPC-C rankings for clustered database systems. (TPC-C is a standard benchmark for database systems, designed by the Transaction Performance Processing Council—or TPC—which consists of all the major database vendors. The TPC-C test measures transactions per minute—or tpmC.) SQL Server also broke into the TPC-C top 10 for nonclustered systems but fell short of the best scale-up database solutions by IBM and Oracle, which ran on more powerful hardware. Even so, SQL Server has continued to make significant gains in scalability, and the new SQL Server 2000 64-bit Enterprise Edition (formerly code-named Liberty) moves SQL Server even closer to the peaks of enterprise scalability. Let's look at SQL Server 64-bit's features and see when deploying this powerful new platform, expected to be available in April, makes sense.
Features and Requirements
Obviously, the primary requirements for running the 64-bit edition of SQL Server are 64-bit hardware and a 64-bit OS. For the OS, SQL Server 2000 64-bit Enterprise Edition requires the Windows Server 2003 64-bit Edition, which runs only on systems built on the new 64-bit Intel Itanium CPU. At the time of this writing, Hewlett-Packard (HP) and NEC produce 64-bit Itanium-based systems. HP currently produces a line of 64-bit workstations (models zx6000 and zx2000) and a line of 64-bit servers (models rx5670, rx2600, and rx9610). However, at least 20 more hardware vendors—including Unisys, Fujitsu Siemens, Dell, and IBM—expect to have Itanium-based servers available by midyear. You can learn more about the Intel Itanium chip and its Explicitly Parallel Instruction Computing (EPIC) architecture in the sidebar "Itanium Inside," page 23.
SQL Server 2000 64-bit Enterprise Edition provides a native 64-bit version of all major database server components, including a 64-bit version of the SQL Server database engine, a 64-bit SQL Server Agent for scheduling jobs, and a 64-bit analytics server. The most significant new feature in the SQL Server 64-bit Edition is the massive increase in addressable memory. Memory is typically the biggest hurdle to database scalability, and the 64-bit version of SQL Server delivers it in spades. It supports as much as 32TB of addressable memory and a nearly unlimited amount of virtual memory running on Windows Server 2003. Of course, hardware vendors have yet to actually produce any systems that support this much RAM. So far, Microsoft has tested the 64-bit version of SQL Server on systems that have as much as 256GB of RAM.
In addition to the new 64-bit database engine, the SQL Server 64-bit Edition also sports a new Windows Installer-based setup program. The new setup program provides an easier-to-use single feature tree, which lets you select the features to install from a list that's displayed on the left side of the screen, and includes an integrated option to install Analysis Services.
100 Percent Compatible
The 64-bit release is nearly 100 percent compatible with the 32-bit version of SQL Server. You don't have to change any of your existing client applications, which can connect seamlessly to either 32-bit or 64-bit SQL Server systems. Likewise, T-SQL is completely code-compatible. When Microsoft developers redesigned SQL Server for the 7.0 release, they built the SQL Server internals with 64-bit processing in mind. Consequently, all versions from 7.0 on support the same data types, and 64-bit SQL Server doesn't change the on-disk database file formats.
The fact that 64-bit SQL Server uses the same on-disk formats makes migrating to 64-bit SQL Server a snap. Essentially, you detach the database from the 32-bit server, reattach it to the 64-bit server, and you're migrated. Alternatively, you can migrate by using the Copy Database Wizard, which besides moving the database, copies your database logins to the new master database.
In addition to its compatibility with 32-bit versions of SQL Server and client applications, SQL Server 2000 64-bit Edition is also fully compatible with the 64-bit version of Windows Server 2003. The 64-bit version of SQL Server leverages many of the 64-bit components that come with Windows Server 2003 64-bit Edition, including Microsoft Data Access Components (MDAC) 2.7, Distributed Transaction Coordinator (DTC), HTML Help, and the Microsoft Management Console (MMC).
Where No SQL Server Has Gone Before
Some recent TPC-C scores featuring the 64-bit version of SQL Server certainly affirm that SQL Server 64-bit Edition is the fastest version of SQL Server. In September 2002, SQL Server set a new TPC-C high mark in the nonclustered systems category with a tpmC score of 308,620. This score nearly doubled the previous nonclustered best score for SQL Server, which came on a Unisys 32-processor Xeon-based server. The new TPC-C mark, achieved on a 32-way NEC TX7/
i9510 server with 1GHz Itanium 2 CPUs and 256GB of system memory, moved SQL Server up to fifth place overall in the nonclustered category. Then in February 2003, Microsoft recorded an even more impressive tpmC score of 433,107, moving SQL Server into second place overall in nonclustered results. This score was set using a 32-way NEC Express5800 server with Itanium 2.1GHz processors and 512GB of RAM.
The 64-bit version of SQL Server has also set several new TPC-C records for 4-way and 8-way systems. Figure 1 compares recent TPC-C scores on 4-way and 8-way systems. You can see that the 64-bit version of SQL Server has set the highest 4-way TPC-C score of any OS and any database platform, with a mark of 78,455 tpmC. This score easily exceeded the previous 4-way best of 55,439 tpmC and even topped previous scores set by 8-way systems. The system that set this TPC-C benchmark used SQL Server 2000 64-bit Enterprise Edition running on Windows Advanced Server Limited Edition (LE) 1.2 on top of the 4-way HP rx5670 1GHz Itanium 2 server with 48GB of RAM. SQL Server 64-bit also set a new 2-way score of 40,621 tpmC running on Windows Advanced Server LE 1.2 and a 1GHz HP rx2600 server with 12GB of RAM.
TPC-C scores aren't the only benchmarks that the 64-bit version of SQL Server running on Itanium 2 hardware has topped. SQL Server 2000 64-bit also produced the world's best 4-way system performance result on the SAP Sales and Distribution (SD) benchmark. The SAP SD benchmark simulates the maximum number of concurrent users a system can handle, performing a defined set of business transactions while maintaining acceptable response times. SQL Server 2000 64-bit Enterprise Edition supported 470 SD benchmark users while maintaining an average response time of 1.97 seconds.
Other systems will certainly beat these scores in the future. It's early in the Itanium chip's life cycle, and Intel will certainly make faster, more-efficient versions of the Itanium soon. Additionally, server hardware vendors will produce systems that support more RAM, which almost always improves database performance.
However, perhaps even more important will be the advances in software that runs on the Itanium chip. As I mention in the sidebar "Itanium Inside," performance on the Itanium's EPIC architecture is highly dependent on the compiler software, which optimizes the code and facilitates multiprocessing. The Microsoft-produced EPIC compilers are still in their first generation and are certain to improve in subsequent releases. With such improvements, SQL Server will likely soon hold both the top TPC-C clustered and nonclustered scores.
Important to Note
Although the 64-bit edition of SQL Server brings a lot of impressive new functionality to the table, a few notable features are missing. First, the management tools—Enterprise Manager, Query Analyzer, and the Data Transformation Services (DTS) Designer—and all the wizards are still 32-bit applications. However, this situation isn't much of a drawback. Although the management tools are all 32-bit, you can use them to manage 64-bit SQL Server installations with no loss of functionality.
A second limitation is more serious: There's no upgrade path from SQL Server 6.5 or SQL Server 7.0 to the SQL Server 2000 64-bit Edition; the 64-bit edition supports migration from SQL Server 2000 only. It also doesn't support English Query, and you can't use Windows Remote Installation Services (RIS) to deploy 64-bit SQL Server.
The pricing structure brings good news, however. You might think that since the SQL Server 64-bit Edition is targeted at the high end of the enterprise, Microsoft would charge a premium to license the new version. However, the 64-bit version of SQL Server 2000 is licensed for exactly the same price as the 32-bit version. Table 1 lists the versions of SQL Server 64-bit Edition that Microsoft plans to release in April and the available licensing models you can use to purchase each edition.
The 64-Bit Question
So the $64,000 question is, is the 64-bit edition of SQL Server right for you? Although most 32-bit SQL Server installations still have room to grow, the better performance offered by SQL Server 2000 64-bit Edition gives more headroom in very high-end scale-up scenarios.
According to Microsoft, SQL Server 2000 64-bit Edition could be a good fit if your application is I/O intensive and has a working set that's greater than 4GB or consumes a lot of CPU time issuing I/O requests. (The working set size is a SQL Server tuning parameter that refers to the physical memory that the system uses.) So, data warehousing and decision support systems, very large Web and e-commerce applications, server consolidation projects, global Web services platforms, and statistical modeling applications, among others, might benefit from the 64-bit platform. Even if your system has room to grow today, it's good to know that the 64-bit version of SQL Server can scale up enough to address even the largest enterprise's database requirements.