Chapter 3 - Choosing an Edition of SQL Server 2000

Introduction

Microsoft® SQL Server™ 2000 is more than a relational database management system; it is a complete database and analysis product that meets the scalability and reliability requirements of the most demanding enterprises. It is appropriate for a broad range of solution types, including e-commerce, data warehousing, and line-of-business applications.

Of course, SQL Server 2000 contains many features that help businesses manage and analyze data, but one "feature" that might not be so obvious is the selection of SQL Server 2000 editions. There are seven different editions to choose from. That might seem like a lot of different products to worry about, but understanding the differences and appropriate uses for these various editions is actually quite simple.

The different editions are designed to accommodate the unique performance, runtime, and price requirements of organizations and individuals. For example, your organization may require not only that its database and analysis solution run on the largest, most powerful computers in your company's data center, but also that this solution be able to "scale down" to desktops, laptops, and even devices like the Pocket PC. SQL Server 2000 achieves this goal while maintaining maximum application compatibility across platforms. Understanding these options allows organizations to make the most cost-effective and technically appropriate choice for their particular needs.

In this chapter, you'll learn more specifically about the differences among the various editions of SQL Server 2000, and how you can save time and money by choosing the right one for the job.

SQL Server 2000 Server Editions Explained

We will begin by making a statement that will go a long way toward helping you understand the different offerings in the SQL Server 2000 family:

SQL Server 2000 Enterprise Edition and SQL Server 2000 Standard Edition are the only editions of SQL Server 2000 that can be installed and used in live (deployed) server environments.

What does this mean? Simply, if you are building a database or analysis application that accesses SQL Server 2000, you must use SQL Server 2000 Standard or Enterprise editions. Other editions cannot be used in deployed, server solutions, due to technical and licensing restrictions. The terms "deployed" and "live" are intended to differentiate such a solution from one that is "under development" or "in testing." It is also worth noting that the Enterprise and Standard editions can only be installed on server operating systems, namely Microsoft Windows® 2000 Server, Windows 2000 Advanced Server, Windows 2000 Datacenter Server, Windows NT® Server 4.0, or Windows NT Server 4.0 Enterprise Edition (for more information, see https://www.microsoft.com/sql/).

So, the central choice most organizations must make is between two editions: Enterprise or Standard.

SQL Server 2000 Enterprise Edition

This edition is comprehensive: it includes the complete set of SQL Server database and analysis features and is uniquely characterized by several features that make it the most scalable and available edition of SQL Server 2000. It scales to the performance levels required to support the largest Web sites and enterprise online transaction processing (OLTP) and data warehousing systems. Its support for failover clustering also makes it ideal for any mission critical line-of-business application. Additionally, this edition includes several advanced analysis features that are not included in SQL Server 2000 Standard Edition.

There are four main areas in which the additional features of SQL Server 2000 Enterprise Edition are most evident:

• Scalability 

• Availability/uptime 

• Performance 

• Advanced analysis 

• For each of these areas, the following guidelines will help you decide whether to choose the Enterprise Edition or Standard edition. And, for a detailed list of features included in the various editions of SQL Server 2000, please see "Features Supported by the Editions of SQL Server 2000" in SQL Server 2000 Books Online. 

Scalability Requirements

Whether for data warehousing or for a transactional system, Enterprise Edition scales best. It supports more memory (up to 64 gigabytes [GB]) and processors (up to 32) than Standard Edition. Use Enterprise Edition for improved scalability if you:

• Need more than four CPUs in the database server. For more information, see "Maximum Capacity Specification" in SQL Server Books Online. 

• Need more than 2 GB of physical memory (RAM) in the database server. 

• Will be dividing database workload across independent servers (that is, "scaling out"). 

• Are using System Area Networking (SAN) connection technologies between servers (for example, Compaq ServerNet II or Giganet cLAN). 

Availability/Uptime

Enterprise Edition includes features that ensure the highest levels of availability for mission critical applications. Use Enterprise Edition for improved availability/uptime if you:

• Are installing a system that will use failover clustering to ensure that your applications stay up and running even when disaster strikes.

• Rely on log shipping to maintain a warm standby server. 

Performance

Other enhancements ensure that the performance of Enterprise Edition exceeds that of other editions. Because these features are optimizations for common operations, many—if not most—applications will realize performance improvements running atop SQL Server 2000 Enterprise Edition. Use Enterprise Edition for improved performance if you:

• Need to speed up an application by taking full advantage of symmetric multiprocessor (SMP) computers (that is, enhanced parallelism). 

• Need to improve performance of applications that frequently use queries that perform particular types of joins or aggregations, as in reporting applications (using indexed views). 

• Need to speed up transactional applications that do many database reads and full table scans. 

Advanced Analysis

In the area of data warehousing and OLAP, Enterprise Edition offers a variety of unique features including allowing OLAP cubes with very large dimensions to be created, stored, and analyzed. Use Enterprise Edition for your data warehousing and analysis tasks if you:

• Need to define OLAP partitions. 

• Will be creating and updating large cubes with very large dimensions. 

• Have extremely large dimensions that require relational OLAP (ROLAP) storage. 

• Need to update cubes quickly, in real time. 

• Need to link cubes or provide cube access over the Web. 

SQL Server 2000 Standard Edition

This is a more affordable option for small- and medium-sized organizations that do not require the advanced scalability, availability, performance, or analysis features of SQL Server 2000 Enterprise Edition. Standard Edition can be used on symmetric multiprocessing systems with up to 4 CPUs and 2 GB of RAM.

Standard Edition includes the core functionality needed for non-mission-critical e-commerce, data warehousing, and line-of-business solutions. For instance, all of the XML features present in Enterprise Edition are also included in Standard Edition. And while a handful of advanced OLAP features are reserved for Enterprise Edition, all data mining features and the core OLAP functionality are included in SQL Server 2000 Analysis Services in Standard Edition. Similarly, components that other database vendors charge for as separate add-on products for their highest-end editions are included in Standard Edition:

• Data Transformation Services 

• Replication (snapshot, transactional, and merge) 

• Full-Text Search 

• English Query 

• Stored procedure development and debugging tools 

• SQL Profiling and performance analysis tools 

Before choosing Standard Edition, make sure you review "Features Supported by the Editions of SQL Server 2000" in SQL Server Books Online.

SQL Server 2000 Editions for Special Uses

Besides the two server editions of SQL Server 2000, five editions exist for special uses. These are:

• SQL Server 2000 Personal Edition 

• SQL Server 2000 Developer Edition 

• SQL Server 2000 Evaluation Edition (also known as SQL Server 2000 Enterprise Evaluation Edition) 

• SQL Server 2000 Windows CE Edition 

• SQL Server 2000 Desktop Engine

SQL Server 2000 Personal Edition

SQL Server 2000 Personal Edition is ideal for mobile users who spend some of their time disconnected from the network but run applications that require SQL Server data storage, and for stand-alone applications that require local SQL Server data storage on a client computer.

This edition is functionally equivalent to the Standard Edition, with the following exceptions:

• It includes a concurrent workload governor that limits its scalability; performance degrades when more than five Transact-SQL batches are executed concurrently. 

• It can use a maximum of two processors in an SMP computer (only one processor if running Windows 98 or Windows Millennium Edition). 

• It cannot act as a transactional replication publisher (subscriber only). 

In addition to running on the server operating systems of the Microsoft Windows NT 4.0 and Windows 2000 operating system families, Personal Edition runs on non-server operating systems including Windows 2000 Professional, Windows NT Workstation 4.0, Windows Millennium Edition, and Windows 98. Full-Text Search and SQL Server 2000 Analysis Services (including OLAP, data mining, and data warehousing features) are included in Personal Edition but cannot be installed on Windows 98 or Windows Millennium Edition.

SQL Server 2000 Developer Edition

This edition allows developers to build any type of application on top of SQL Server. It includes all of the functionality of Enterprise Edition but with a special development and test Microsoft Software License Terms that prohibits production deployment (for complete details, see the SQL Server 2000 Developer Edition license terms at https://www.microsoft.com/sql/). For maximum flexibility during development, it will install to the aforementioned server operating systems as well as Windows 2000 Professional and Windows NT Workstation 4.0.

SQL Server 2000 Developer Edition is the only edition of SQL Server 2000 that gives the licensee the right to download and install SQL Server 2000 Windows CE Edition (SQL Server CE) from https://www.microsoft.com/sql. The Developer Edition licensee also has the right to redistribute SQL Server CE-based applications to an unlimited number of devices at no additional cost beyond the purchase price of SQL Server 2000 Developer Edition. Devices running SQL Server CE that access or otherwise use the resources of a SQL Server must be properly licensed. For more information, see https://www.microsoft.com/sql/.

SQL Server 2000 Developer Edition is the ideal choice for Independent Software Vendors (ISVs), consultants, system integrators, solution providers, and corporate developers developing and testing applications because it is cost effective, runs on a variety of platforms, and can be upgraded for production use to SQL Server 2000 Enterprise Edition.

SQL Server 2000 Evaluation Edition

SQL Server 2000 Evaluation Edition (also known as SQL Server 2000 Enterprise Evaluation Edition) is a time-limited version of SQL Server 2000 Enterprise Edition that is licensed for demonstration, testing, examination, and evaluation for a period of 120 days. This means that Evaluation Edition is not for production use; solutions must be deployed on SQL Server 2000 Enterprise or Standard Edition. Evaluation Edition will not function after the 120-day limit has been reached. The only cost for this edition is a minimal shipping and handling fee. The advantage of the Evaluation Edition is that you can evaluate the complete set of features in SQL Server 2000, which can be useful whether you are already familiar with SQL Server or are completely new to the product.

Like Developer Edition, Evaluation Edition will install to the aforementioned server operating systems as well as Windows 2000 Professional and Windows NT Workstation 4.0. This gives developers and database administrators additional flexibility in evaluating the product, as it can easily be installed on laptops or desktop systems running these non-server operating systems.

For more information about the Evaluation Edition, please go to https://www.microsoft.com/sql/.

SQL Server 2000 Windows CE Edition

Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) is the compact database for rapidly developing applications that extend enterprise data management capabilities to devices. SQL Server CE is a full-fledged member of the SQL Server 2000 family, with tools, application programming interfaces (APIs), and SQL grammar that developers and database administrators with existing SQL Server skills will recognize. SQL Server CE is the only edition of SQL Server 2000 that provides relational database management capabilities on Windows CE-based devices.

The SQL Server CE engine provides an essential set of relational database features—including an optimizing query processor and support for transactions and assorted data types—while it maintains a compact footprint that preserves system resources. Remote data access and merge replication, which work over Hypertext Transfer Protocol (HTTP) and support encryption, ensure that data from enterprise SQL Server databases is reliably delivered and that this data can be manipulated offline and synchronized later to the server. This makes SQL Server CE ideal for mobile and wireless scenarios.

SQL Server CE runs on devices that use Windows CE version 2.11 or later, including the Handheld PC Pro (H/PC Pro), Palm-size PC (P/PC), and Pocket PC. SQL Server CE can access SQL Server data in SQL Server version 6.5 or later, but can act as a merge replication subscriber only with SQL Server 2000 databases. SQL Server CE has a device footprint of approximately 1 megabyte (MB). Database size is currently limited to 2 GB. For more information on SQL Server CE (or to download SQL Server CE if you are a licensed SQL Server 2000 Developer Edition user) please see https://www.microsoft.com/sql/.

SQL Server 2000 Desktop Engine

SQL Server 2000 Desktop Engine is the successor to Microsoft Data Engine (MSDE) 1.0, which was based on SQL Server version 7.0. As such, SQL Server 2000 Desktop Engine is often referred to as MSDE 2000. The SQL Server 2000 Desktop Engine is not related to SQL Server 7.0 Desktop Edition.

Probably the most significant characteristic of the Desktop Engine is that it is a redistributable version of the SQL Server relational database engine. Third-party software developers can include it in their applications that use SQL Server to store data. The SQL Server 2000 Desktop Engine is made available as a set of Windows Installer merge modules that can be included in the application setup. The Desktop Engine is an ideal embedded or offline data store, because it is easy to install and has the smallest footprint of any edition of SQL Server 2000.

The SQL Server 2000 Desktop Engine does not include graphical management tools; the application distributing the engine is usually coded to perform any needed database administration. You can manage instances of the Desktop Engine from the SQL Server 2000 graphical tools if installed with another edition of SQL Server. Other items not included in the Desktop Engine include analysis capabilities (such as OLAP, data mining, and data warehousing features) and SQL Server Books Online. Desktop Engine also limits database size to 2 GB and employs the same concurrent workload throttle described above for Personal Edition.

Like all of the editions of SQL Server 2000 described so far in this chapter, the Desktop Engine supports multiple instance installations, lessening the likelihood that an instance installed by an application of one vendor will conflict with subsequent MSDE-based applications installed by applications by other vendors. Additionally, all of the APIs and most of the functionality of the other editions of SQL Server 2000 are supported. Desktop Engine includes the SQLServerAgent service for managing scheduled tasks. Although the Desktop Engine does not include the management tools or wizards, applications can fully administer an instance of the Desktop Engine using the SQL Server administration APIs, such as SQL-DMO, the DTS and Replication programming objects, or the general database APIs (such as ADO, OLE DB, and ODBC). Applications can use the general database APIs to access data in the Desktop Engine, and the Desktop Engine can participate alongside other editions of SQL Server 2000 in DTS transformations and replication plans (except operating as a transactional replication publisher).

Redistribution rights exist for the Desktop Engine and can be found in the license terms for the products that include it (see "Obtaining SQL Server 2000" later in this chapter). If the Desktop Engine is not used in a purely standalone manner—that is, if the Desktop Engine is connecting to or otherwise utilizing the resources of a SQL Server (even if this use occurs through middleware, across multiple computers, or through multiplexing, where no direct connection is present)—then it is acting as a client to a SQL Server server and must be properly licensed. In such a case, customers using the Processor licensing model on the server enjoy unlimited connections to the server, and thus require no further action for the Desktop Engine or any other clients. However, customers using the Server/Client Access License (CAL) licensing model in this scenario must have a CAL dedicated to any device using the Desktop Engine. For more information, see Chapter 4, "Choosing How to License SQL Server." You can also visit https://www.microsoft.com/sql/.

Obtaining SQL Server 2000

SQL Server 2000 Enterprise Edition and SQL Server 2000 Standard Edition are available through a variety of channels. Competitive and version upgrades to SQL Server 2000 may lower your cost considerably. Additionally, small business, enterprise, government, and academic discounts are available. For complete information on how to obtain SQL Server 2000, please visit https://www.microsoft.com/sql/.

SQL Server 2000 Personal Edition is not a separate product but rather a client component of SQL Server 2000 (included as part of Enterprise and Standard Editions), designed to bring SQL Server functionality to non-server hardware, including workstations and laptops. SQL Server Personal Edition can be installed on any client device to be used in conjunction with the server software. Licensing is governed by the same rules as Enterprise and Standard Edition licensing.

SQL Server 2000 Developer Edition can be ordered online directly from Microsoft at https://www.microsoft.com/products/info/default.aspx?View=22 or from an authorized reseller. SQL Server 2000 Developer Edition is also included in the MSDN Universal subscription ( https://msdn2.microsoft.com/subscriptions/default.aspx).

SQL Server 2000 Evaluation Edition can be ordered for a minimal fee from https://shop.microsoft.com/devtools/.

SQL Server 2000 Windows CE Edition is available for free download to licensed SQL Server 2000 Developer Edition users. Please see https://www.microsoft.com/sql/ for more information.

SQL Server 2000 Desktop Engine is included in a directory called "MSDE" at the root of the SQL Server 2000 Enterprise, Standard, and Developer Edition CD-ROMs. SQL Server 2000 Desktop Engine will also be included in upcoming Microsoft products, just as MSDE 1.0 shipped in products like Microsoft Visual Studio® 6.0 Enterprise Edition and Microsoft Office 2000 Developer.

Conclusion

Whether you are a developer, IT professional, or a database administrator, whether you are just developing and testing or are ready to deploy in production, there is a SQL Server 2000 edition for you and your organization. The variety of SQL Server 2000 editions ensures that you pay a fair price for the functionality you need. You will likely find that you use several editions of SQL Server 2000 on a single project: development and testing on Developer Edition, deployment to servers on Enterprise Edition, with replication subscriptions managed by Personal Edition running on laptops. And in the future, maybe you will extend your solution to devices, relying on SQL Server 2000 Windows CE Edition for reliable, high performance database management. The important thing is to consider up front what edition will work best for your organization; by making the right choice you will end up saving time and money.

You have also learned how to obtain the various editions of SQL Server, but you may have one lingering question: "What should I run SQL Server 2000 on?" To answer this question, you must consider both the hardware and the operating system. Your hardware vendor can help you pick an appropriate server computer for your solution, and may even have sizing tools available for download from its Web site. For the operating system, keep in mind several things. The deployable server edition of SQL Server 2000 requires a server operating system, and it is optimized for Windows 2000. Several features in SQL Server 2000 are dependent on features in the operating system and thus are unavailable when SQL Server 2000 is run atop Windows NT Server 4.0. For example, Microsoft Active Directory™ integration requires Windows 2000 Server or greater. Such issues need to be considered when you plan your SQL Server 2000 deployments.