DOC

SQL Server 2000 Enterprise Edition (64-bit)

By Kenneth Grant,2014-05-15 12:43
15 views 0
SQL Server 2000 Enterprise Edition (64-bit)

SQL Server 2000 Enterprise Edition (64-bit):

    Advantages of a 64-Bit Environment

March 2004

Abstract: Microsoft? SQL Server? 2000 Enterprise Edition (64-bit) offers dramatic

    improvements in memory availability and parallel processing performance compared with SQL Server software running in a 32-bit environment. This paper describes the capabilities of the 64-bit solution, highlights differences from the 32-bit environment, and discusses some of the applications and usage scenarios that can benefit from running on a SQL Server platform optimized for a 64-bit environment. The paper is intended to help the reader identify potential applications for Microsoft SQL Server 2000 (64-bit) and set appropriate expectations for deployment and performance.

    The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

    This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

    Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

    ? 2004 Microsoft Corporation. All rights reserved.

    Microsoft and MSDN are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

    The names of actual companies and products mentioned herein may be the trademarks of their respective owners

     ii

    Table of Contents

    Introduction ..................................................................................................... 1

    Audience ........................................................................................................ 1 Overview: Database Performance and Scalability ............................................ 1 SQL Server 2000 (64-bit) ................................................................................. 2

    Requirements ................................................................................................. 2

    SQL Server 2000 (64-bit) Components .............................................................. 3

    Advantages of the 64-bit Architecture ................................................................ 3 Comparing SQL Server 2000 (32-bit) with AWE and 64-bit .............................. 4

    AWE: An Overview .......................................................................................... 4

    Limitations of AWE .......................................................................................... 5 Other Differences in SQL Server 2000 (64-bit)................................................. 6 Considerations for Choosing SQL Server 2000 (64-bit) .................................... 7

    Relational Database Performance Factors ........................................................... 8

    Analysis Services Considerations ....................................................................... 9

    Server Consolidation Considerations ................................................................ 10

    Alternatives to 64-bit Architecture ................................................................... 10

    Limitations of SQL Server 2000 (64-bit) ........................................................... 11 SQL Server 2000 (64-bit) Deployment Considerations ....................................11

    Integration ................................................................................................... 11

    Migration ..................................................................................................... 12

    Compatibility Issues ...................................................................................... 12 Summary.........................................................................................................13 Appendix A: Case Studies ................................................................................14

    Information Resources, Inc. (IRI) .................................................................... 14

    JetBlue Airways............................................................................................. 15

    Liberty Medical ............................................................................................. 16

    123MultiMedia .............................................................................................. 16 Appendix B: Sources for Additional Information .............................................17

     iii

    Introduction

    Microsoft? SQL Server? 2000 Enterprise Edition (64-bit) offers dramatic improvements

    in memory availability and parallel processing performance compared with SQL Server

    software running in a 32-bit environment. This paper describes the capabilities of the

    64-bit solution, highlights differences from the 32-bit environment, and discusses some

    of the applications and usage scenarios that can benefit from running on a SQL Server

    platform optimized for a 64-bit environment. The paper is intended to help the reader

    identify potential applications for Microsoft SQL Server 2000 (64-bit) and set

    appropriate expectations for deployment and performance.

    Audience

    This paper is intended for a technical audience of IT professionals, database

    administrators and system architects interested in the performance and scalability

    offered by Microsoft SQL Server 2000 Enterprise Edition (64-bit). This document

    assumes that you have an understanding of databases in general and Microsoft SQL

    Server 2000 in particular.

    Overview: Database Performance and

    Scalability

    To make timely and informed business decisions in dynamic and competitive

    environments, organizations must store and analyze massive amounts of business data.

    Always a critical part of the IT infrastructure, databases are at the heart of a number of

    converging trends in IT:

    ? Applications and data are growing in complexity and size. Applications running on

    32-bit platforms may be reaching the limits of the platform, specifically regarding

    the number of processors and addressable memory.

    ? High-end DBMS capabilities have become a business necessity as organizations

    gather and analyze data from numerous databases, and serve that data to growing

    numbers of business users.

    ? Organizations are consolidating servers to simplify critical data center operations.

    Consolidation can reduce management complexity and cost while reducing physical

    space requirements in the data center.

    In response to these trends, Microsoft has developed a 64-bit version of its powerful

    SQL Server 2000 database, optimized for the 64-bit Intel? Itanium? 2 processor.

     1

    By leveraging the tremendous scalability and memory addressability of the 64-bit architecture, SQL Server 2000 (64-bit) can host increasingly large and complex database and analysis applications, and support server consolidation on large-scale SMP systems with many processors. Applications can benefit from massive in-memory caching of data as well as larger data structures for procedure cache, sort space, lock memory and connection memory.

    By eliminating platform and processing bottlenecks, the 64-bit platform offers a new level of scalability for business applications at an affordable cost.

    SQL Server 2000 (64-bit)

    SQL Server 2000 (64-bit) is optimized to run on servers using Intel Itanium 2 processors and Microsoft Windows Server? 2003, offering exceptional performance and

    scalability. SQL Server 2000 (64-bit) is based on the successful 32-bit version of SQL Server 2000, providing an easy migration path for existing applications and capitalizing in existing tools and expertise.

    The primary differences between the 64-bit and 32-bit versions of SQL Server 2000 are derived from the benefits of the underlying 64-bit architecture. These include: ? Larger direct-addressable memory space; SQL Server 2000 (64-bit) is not bound by

    the memory limits of 32-bit systems, so more memory is available for performing

    complex queries or supporting essential database operations. ? Enhanced parallelism, providing better linear scalability and support for up to 64

    processors.

    ? Improved BUS architecture, accelerating performance by moving more data

    between cache and processors.

    By take advantage of these architectural advantages, SQL Server 2000 (64-bit) can handle large and complex query workloads, consolidate many database applications, and otherwise scale to meet the increasing processing and performance demands of today's IT environment.

    Requirements

    SQL Server 2000 (64-bit) is currently supported on IA64 servers from a number of hardware vendors including HP, Unisys, IBM, Dell and NEC, in 4-way to 64-way configurations.

    SQL Server 2000 (64-bit) does not have a separate hardware compatibility list (HCL). Use hardware that is otherwise certified for use with the Microsoft Windows Server 2003 64-bit operating system and that meets the following minimum requirements:

     2

    Hardware Minimum requirements Computer Intel? Itanium? 2 processors with 64-bit

    CPU

    Memory (RAM) Enterprise Edition: 1 GB

    Additional memory may be required,

    depending on configuration and operating

    system requirements. Hard disk space SQL Server database components: 190 to

    320 MB

    Analysis Services: 230 MB

    Books Online only: 40 MB

    Actual requirements will vary based on

    your system configuration and the

    applications and features you choose to

    install

    Pointing device Microsoft Mouse or compatible CD-ROM drive Required

Table 1 Hardware Requirements

    SQL Server 2000 (64-bit) requires a 64-bit version of the Windows Server 2003

    operating system.

    SQL Server 2000 (64-bit) Components

    The components of SQL Server 2000 Enterprise Edition (64-bit) include:

    ? 64-bit database server

    ? 64-bit server agent

    ? 64-bit analysis server (OLAP and data mining)

    These 64-bit components are code-compatible with the 32-bit version of SQL Server

    2000, making it easy to integrate a 64-bit server with other SQL Server 2000 database

    servers.

    Advantages of the 64-bit Architecture

    The enhanced scalability and performance of SQL Server 2000 (64-bit) is made possible

    by a number of architectural features of the Itanium 64 bit architecture. The key

    architectural benefits are discussed briefly as follows.

    Memory addressability

    32-bit systems can generally address only a 4G address space. (Exceptions

    available with Address Windowing Extensions, or AWE, on 32-bit platforms are

    described in a following section.) Windows Server 2003 running on the Intel Itanium

    64-bit architecture supports up to 1,024 Terabytes of physical memory and 512

    Gigabytes of addressable memory.

     3

Enhanced parallel processing support

    The Intel Itanium chips include a number of features that enhance parallel

    processing performance. The Itanium 2 features a wider system bus, more registers

    and Explicitly Parallel Instruction Computing (EPIC) technology, which enables a

    processor to execute multiple (up to 6) instructions simultaneously. Larger numbers of processors

    Improvements in parallel processing enable the 64-bit architecture to support larger

    numbers of processors (up to 64). With a larger number of processors, SQL Server

    can support more processes, applications, and users in a single system.

    Server platforms offering 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 leveraging both large amounts of memory and the

    superior scaling of 64-bit processors in a 64-bit architecture. Enhanced bus architecture

    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 broadband connections offer over dial-up

    connections.

    For a more detailed discussion of the Itanium architecture, see

    http://www.intel.com/ebusiness/pdf/prod/itanium/wp022404.pdf.

    Comparing SQL Server 2000 (32-bit) with

    AWE and 64-bit

    Microsoft SQL Server 2000 Enterprise Edition (32-bit) uses the Microsoft Windows 2000

    Address Windowing Extensions (AWE) API to support very large amounts of physical

    memory. For some applications, using AWE may be a viable alternative to upgrading to

    the 64-bit platform.

    AWE: An Overview

    Standard 32-bit systems can map at most 4 GB of memory, limiting the addressable

    memory space for Windows 2000 systems to 4GB. With 2GB reserved for the operating

    system, only 2GB of memory remains for the application (in this case, SQL Server

    2000). You can increase this amount to 3 GB by setting a 3GB switch in a Windows

    boot.ini file.

    AWE is a set of memory management extensions to the Microsoft Win32? API that

    allows applications to address memory beyond the 4GB limitation. Using AWE,

    applications can acquire physical memory as nonpaged memory, and then dynamically

    map views of the nonpaged memory to the 32-bit address space. AWE enables

    memory-intensive applications, such as large database systems, to address large

    amounts of information. More information on AWE can be found on the MSDN page on

    the Microsoft Web site.

     4

    Using the AWE extension enables a SQL Server Enterprise Edition instance to address significantly more memory than standard implementations:

    ? Up to 32 GB of physical memory on Windows Server 2003, Enterprise Edition using

    AWE

    ? Up to 64 GB of memory using Windows Server 2003, Datacenter Edition using AWE

    Although using AWE imposes some overhead and adds initialization time, it remains an important strategy for many memory-intensive database applications.

    Limitations of AWE

    Using SQL Server 2000 (32-bit) with AWE has some limitations. The additional memory addressability is available only to the relational database engine, not to other engines

    such as Analysis Services. The benefits are limited to those operations that can directly access database pages stored in the expanded buffer cache operations such as

    building indexes on large tables, or simple queries from cache. AWE does not enhance other memory-intensive operations using the stored procedure cache or log cache. Although its benefits are significant, the potential performance enhancements from AWE do not apply to the following operations or components:

    ? Analysis Services

    ? Procedure Cache for compiled plans

    ? Cursors

    ? ODBC parameter caching

    ? Sort area

    ? Index creation, including full-text indexing ? Hash memory

    ? Per-connection memory

    ? Lock memory

    In contrast, SQL Server 2000 (64-bit) makes extended memory available to all database processes and operations. Using this 64-bit version on Itanium 2 hardware, a SQL Server instance can address up to 512 GB, the current maximum memory supported by Windows Server 2003. (There is a theoretical addressable limit of 18 Exabytes.) This memory is available to all components of SQL Server, and to all operations within the database engine.

    As a result, SQL Server 2000 (64-bit) enhances performance of a wide range of memory-intensive database applications.

     5

    Other Differences in SQL Server 2000

    (64-bit)

    Although memory management is the most significant difference between the 64-bit

    and 32-bit versions of SQL Server 2000, there are other differences that you should

    consider when choosing which architecture to deploy.

    SMP processor support

    Previously, with the 32-bit version of SQL Server, symmetric multiprocessing (SMP)

    support was limited to 32 processors. With SQL Server 2000 (64-bit) Enterprise

    Edition this limit has been raised to 64 processors with Windows Server 2003

    Datacenter edition.

    Availability

    High availability is critical for many high-end applications. SQL Server 2000 (64-bit)

    on Windows Server 2003 supports an 8-node failover cluster. This is an increase

    from the 4-node cluster supported by Windows 2000 Datacenter Server Installation enhancements

    The 64-bit version of SQL Server is installed using the Windows Installer Service,

    enabling the "Add or Remove Programs" option in the Control Panel. The setup

    process displays a tree of features to be installed, which can be deselected as

    needed.

    Meta data repository

    The 64-bit version of Analysis Services uses SQL Server instead of Jet (.MDB) for its

    meta data repository. The repository databases are created within SQL Server when

    Analysis Services is installed.

    Replication

    The Replication features of SQL Server 2000 (64-bit) are nearly identical to the 32-

    bit version, with the following features being supported:

    ? Snapshot Replication

    ? Transactional Replication

    ? Merge Replication

    ? Immediately updating subscribers

    ? Queued updating subscribers However, there are a few special cases:

    ? As the Microsoft Jet engine is not supported, Microsoft Jet push subscriptions for

    merge replication are not supported.

    ? Unless the subscriber provides a 64-bit ODBC or OLE DB driver, transactional or

    snapshot push subscriptions for ODBC or OLE DB subscribers are not supported. ? Because of the unavailability of 64-bit Data Transformation Services (DTS),

    transformable push subscriptions are not supported.

    In addition, the 64-bit versions of Windows Server 2003, Enterprise Edition and

    Windows Server 2003, Datacenter Edition do not include certain features that are

    included in the 32-bit versions of those operating systems.

     6

These features are not included because of underlying differences between the 32-bit

    and 64-bit architectures or because the features are not appropriate in the

    environments where the 64-bit versions of Windows Server 2003, Enterprise Edition

    and Windows Server 2003, Datacenter Edition are deployed.

    For a complete list of differences, see "Differences Between 64-bit and 32-bit Releases 1(64-bit)" in SQL Server 2000 (64-bit) Books Online and "Features unavailable on 64-bit 2versions of the Windows Server 2003 family".

    Considerations for Choosing SQL Server 2000

    (64-bit)

    Although it offers significant scalability and performance for many applications, not

    every application will benefit from the 64-bit version of SQL server. This section is

    designed to help you determine if and when it is appropriate to either use existing SQL

    Server instances or develop new applications using the 64-bit architecture.

    The improved memory and parallel processing capabilities of SQL Server 2000 (64bit)

    are compelling in a number of different usage scenarios, including:

    ? Improving performance for memory-constrained relational applications. ? Creating or accelerating large OLAP systems with rapid response time requirements. ? Consolidating multiple Windows-based databases and applications to fewer, larger

    systems. By hosting multiple databases on a single 64-bit system, you simplify

    management, improve storage utilization, and generally improve operational

    efficiency.

    ? "Scaling up" current applications experiencing significant growth; migrating existing

    database servers that are meeting platform limitations doesn't affect the other tiers

    of multitiered applications.

    ? Replacing UNIX systems/applications. The 64-bit platform offers a powerful

    alternative to UNIX systems for high-end database servers.

    The following sections list some of the factors you should consider when evaluating

    specific applications for the 64-bit platform.

1 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sql64/in_introduction64_7xo2.asp

    2http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/windowsserver2003/prod

    docs/entserver/unsupported64BitFeatures.asp

     7

Report this document

For any questions or suggestions please email
cust-service@docsford.com