You can choose from several editions of SQL Server 2000. The edition you choose will depend on your database and data processing needs, as well as the Windows platform on which you want to install it.
For actual deployment of SQL Server in a live deployed server environment, you can only choose from two editions of SQL Server: Standard Edition and Enterprise Edition. In addition, SQL Server also is available for special uses in a Developer Edition, Personal Edition, Desktop Engine Edition, or Windows CE Edition.
This chapter will examine the different editions of SQL Server and discuss their features and capabilities. Using this information, you will be better able to choose which edition provides the appropriate solution.
The Standard Edition of SQL Server 2000 is the version intended for the masses?those running small- to medium-sized systems that don't require the performance, scalability, and availability provided by Enterprise Edition. The Standard Edition runs on any of the Windows 2000 or Windows NT 4.0 Server platforms with scalability limited to up to four processors and 2GB of memory. Standard Edition includes the following features:
Data Transformation Services
Full Text Search
Built-in XML support
SQL Profiler and performance analysis tools
Graphical DBA and Developer tools
Data mining tools
As shown in Table 2.1, the Standard Edition can be installed on any of the Windows NT 4.0 and Windows 2000 Server platforms.
The Standard Edition should meet the needs of most departmental and small- to mid-sized applications. However, if you need more scalability, availability, advanced performance features, or comprehensive analysis features, you will want to implement the Enterprise Edition of SQL Server 2000.
The Enterprise Edition of SQL Server 2000 is the most comprehensive and complete edition available. It provides the most scalability and availability of all editions and is intended for systems that require high performance and availability, such as large volume Web sites, data warehouses, and high throughput online transaction processing (OLTP) systems.
SQL Server 2000 Enterprise Edition supports up to 64GB of memory and up to 32 processors when it is installed on Windows 2000 Datacenter Server. It supports up to 8GB of memory and 8 processors when it is installed on Windows 2000 Advanced Server. To provide these capabilities, SQL Server 2000 Enterprise Edition can be installed only on Server versions of Windows 2000 and Windows NT.
Additionally, SQL Server 2000 Advanced Server provides performance enhancements such as parallel query, indexed views, and enhanced read-ahead scanning.
Which version is right for you? The next section explores the feature sets of Enterprise and Standard Editions so you can decide which one provides the features you need.
For deploying SQL Server 2000 in a server environment, either the Standard or the Enterprise Edition of SQL Server is a logical choice. To help decide between the two editions, Table 2.2 compares the major features supported by each edition.
|Feature||Enterprise Edition||Standard Edition|
|Distributed partitioned views||Yes||No|
|Parallel index creation||Yes||No|
|Failover cluster management||Yes||No|
|Indexed views||Yes||Yes (See Note)|
|Graphical DBA and development tools||Yes||Yes|
|Full Text Search||Yes||Yes|
|Integrated data mining||Yes||Yes|
|Multiple instance support||Yes||Yes|
|Data Transformation Services||Yes||Yes|
|Active Directory integration||Yes||Yes|
While all editions of SQL Server 2000 allow the creation of indexed views, only the Developer and Enterprise Editions of SQL Server 2000 will automatically consider using an indexed view in query plans. To force the optimizer to consider indexed views in other SQL Server editions, the NOEXPAND hint must be included in the query. For more information on using indexed views and how they are handled by the query optimizer, please see Chapters 27, "Creating and Managing Views in SQL Server," 34, "Indexes and Performance," and 35, "Understanding Query Optimization."
Another main difference between Enterprise and Standard Editions is scalability. Table 2.3 lists the differences in amount of memory supported by both editions depending on the platform on which it is installed.
|Operating System||Enterprise Edition||Standard Edition|
|Windows 2000 Datacenter||32||4|
|Windows 2000 Advanced Server||8||4|
|Windows 2000 Server||4||4|
|Windows NT 4.0 Server Enterprise Edition||8||8|
|Windows NT 4.0 Server||4||4|
The amount of memory supported by each edition on the various platforms is limited, as shown in Table 2.4.
|Operating System||Enterprise Edition||Standard Edition|
|Windows 2000 Datacenter||64GB||2GB|
|Windows 2000 Advanced Server||8GB||2GB|
|Windows 2000 Server||4GB||2GB|
|Windows NT 4.0 Server Enterprise Edition||3GB||2GB|
|Windows NT 4.0 Server||2GB||2GB|
The Standard and Enterprise Editions of SQL Server 2000 are intended for server-based deployment of applications. In addition, the following editions are available for other specialized uses:
Desktop Engine Edition
Windows CE Edition
The Developer Edition of SQL Server 2000 is a full-featured version intended for development and end-user testing only. It includes all features and functionality of Enterprise Edition, at a much lower cost, but the licensing agreement prohibits production deployment of databases using the Developer Edition.
To provide greater flexibility during development, the Developer Edition can be installed in any of the following environments:
Windows 2000 Datacenter
Windows 2000 Advanced Server
Windows 2000 Server
Windows NT 4.0 Server Enterprise Edition
Windows NT 4.0 Server
Windows 2000 Professional
Windows XP Professional
Windows XP Home Edition
Windows NT 4.0 Workstation
The Personal Edition of SQL Server 2000 is intended for users who are running applications that require a locally installed database, often on mobile systems, and who spend at least some of the time disconnected from the network. The Personal Edition, intended for use in a single-user environment, includes all the tools and features of the Standard Edition with the following exceptions:
It contains a workload governor that cripples performance when more than five Transact-SQL commands are executed simultaneously.
It is limited to using a maximum of two processors (only one processor on Windows 98 and Windows Me).
It can participate in replication but only as a subscriber.
The Personal Edition can be installed in any of the Windows 2000 or Windows NT 4.0 environments, Windows XP Professional and Home Edition, as well as Windows 98/Me. However, the Full Text Search and Analysis Services are not available under Windows 98/Me.
The Desktop Engine Edition of SQL Server 2000 is a small-footprint, redistributable, database-engine-only version of SQL Server intended for distribution with applications that require an embedded or offline data store. Software developers can include it as a module for Windows Installer so it is installed with the rest of the application.
The Desktop Engine is just that?a database engine only. No administrative tools are installed. Administration must be handled through the custom application, or with graphical tools installed with another edition of SQL Server 2000. The Desktop Edition supports the same feature set as the Personal Edition with the exception of Analysis Services. The maximum database size is limited to 2GB.
Like all other editions of SQL Server 2000, the Desktop Engine Edition supports installation of multiple instances to decrease potential conflicts between instances installed by other applications or vendor products.
The Desktop Engine Edition can be installed in any of the Windows 2000 or Windows NT 4.0 environments, Windows XP Professional and Home Edition, as well as Windows 98/Me.
The Windows CE Edition of SQL Server 2000 runs only on platforms that are running the Windows CE environment. The Windows CE Edition is a scaled-down version of SQL Server 2000 that provides Transact-SQL compatibility and a cost-based query optimizer. Developers who are familiar with SQL Server 2000 should feel comfortable developing for Windows CE Edition.
Windows CE Edition requires about a 1MB footprint on a Windows CE device, and has a maximum database size limit of 2GB. Windows CE Edition supports replication with SQL Server 2000 databases as a merge replication subscriber so that data can be accessed and manipulated offline and synchronized later with a Server version of SQL Server 2000.
At the time of this writing, Microsoft was in the process of beginning beta testing for a 64-bit version of SQL Server 2000, code-named "Liberty." The SQL Server 2000 64-bit beta is built to take advantage of hardware enhancements of the 64-bit Itanium platform from Intel. It will offer higher levels of single-system scalability for memory-intensive data applications, such as largescale e-commerce, data warehousing, and analytics. Currently, the planned hardware offerings support up to 64GB of physical linear memory for the 64-bit version of SQL Server 2000, with up to 4 terabytes of physical linear memory planned for future hardware.
The increased memory support of the 64-bit platform will benefit database applications with memory-sensitive workloads that require working data sets larger than 4GB to be loaded in memory. In addition, the 64-bit platform may result in improved I/O performance due to larger memory buffer pools. For low-end implementations with 4-processor servers or less, some performance improvement can be expected on a 64-bit implementation over a 32-bit implementation, but higher performance gains are expected for higher-scale implementations that utilize 8 to 32 processors.
The 64-bit version of SQL Server 2000 will include a 64-bit database server, a 64-bit server agent, and 64-bit analysis server for OLAP and data mining. The 64-bit version of SQL Server 2000 will require 64-bit hardware running the 64-bit version of Microsoft Windows .NET Server Beta3.