SQL Server Editions | Inside Microsoft SQL Server 2000

SQL Server is available in seven editions. Most are available on their own CD. The following descriptions are taken from SQL Server Books Online:

Enterprise Edition Used as a production database server. Supports all features available in SQL Server 2000 and scales to the performance levels required to support the largest Web sites and enterprise online transaction processing (OLTP) and data warehousing systems.
Standard Edition Used as a database server for a small workgroup or department.
Personal Edition Used by mobile users who spend some of their time disconnected from the network but run applications that require SQL Server data storage. Also used for stand-alone applications that require local SQL Server data storage on a client computer.
Developer Edition Used by programmers who develop applications that use SQL Server 2000 as their data store. This edition supports all the features of the Enterprise Edition, but it is licensed for use only as a development and test system, not a production server.
Desktop Engine The redistributable version of the SQL Server 2000 database engine, which independent software vendors can package with their applications.
Windows CE Edition Used as the data store on Microsoft Windows CE devices. Capable of replicating data with SQL Server 2000 Enterprise and Standard editions to keep Windows CE data synchronized with the primary database.
Enterprise Evaluation Edition A full-featured version available for free downloading from the Web. Intended only for use in evaluating the features of SQL Server; stops running 120 days after installation.

Table 4-1, taken from SQL Server Books Online, summarizes the main differences between the editions and indicates the database engine features that they support. You can read about other differences between the versions (in areas such as replication, analysis services, and data transformation services) in SQL Server Books Online. Most of this book will deal specifically with the Enterprise and Standard editions. Also, because the Developer Edition has the same feature support as the Enterprise Edition (only the license agreement is different), most of the discussion will also be relevant to the Developer Edition.

Any edition lets you install the full server and tools suite or just the tools. In fact, if you try to install an edition of SQL Server on an OS for which it is not supported, all you will be able to install are the client tools. (For example, if you try to install SQL Server Enterprise Edition on Windows 2000 Professional, you will get only the tools.)

Table 4-1. Features of the SQL Server editions.

Feature	Personal	Standard	Enterprise
Runs on Microsoft Windows NT 4 Server or Windows 2000 Server	Yes	Yes	Yes
Runs on Windows NT 4 Server, Enterprise Edition or Windows 2000 Advanced Server	Yes	Yes	Yes
AWE Support (Windows 2000 only)	No	No	Yes
SQL Server failover support	No	No	Yes
Supports Microsoft Search Service, full-text catalogs, and full-text indexes	Yes, except on Windows 98	Yes	Yes
Maximum database size	2 GB	1,048,516 TB	1,048,516 TB
Number of symmetric multiprocessing (SMP) CPUs	2 on all platforms except Windows 98, which supports only 1	4 on all platforms except Windows NT 4 Server, Enterprise Edition, which supports 8	32 on Windows 2000 Datacenter Server 8 on Windows NT 4 Server Enterprise Edition and Windows 2000 Advanced Server 4 on Windows NT 4 Server and Windows 2000 Server
Physical memory supported	2 GB	2 GB	64 GB on Windows 2000 Datacenter Server 8 GB on Windows 2000 Advanced Server 4 GB on Windows 2000 Server 3 GB on Windows NT 4 Server, Enterprise Edition 2 GB on Windows NT 4 Server

The Personal Edition has the same features as the Standard Edition, with only the following differences:

The Personal Edition cannot support more than two processors; the Standard Edition supports four processors on Windows 2000 Datacenter Server, Advanced Server and Server zand on Microsoft Windows NT 4 Server. It supports up to eight processors on Windows NT 4 Server, Enterprise Edition.
In the Personal Edition, the maximum size of a database is 2 GB. The maximum size in the Standard Edition is 1,048,516 TB.
The Personal Edition can run on Microsoft Windows 98; the Standard Edition cannot.
The Personal Edition is optimized for up to five concurrent users; the Standard Edition has practically no limit on users.

Because of the limitations of the operating system, if the Personal Edition is installed on a machine running Windows 98, it also does not support:

The Named Pipes and Banyan VINES server-side Net-Libraries. (The Windows 98 computer cannot accept incoming connections using these protocols, but the client tools on such a computer can connect to other SQL Servers on Windows NT and Windows 2000 using these protocols.)
AppleTalk Net-Libraries.
Windows Authentication for incoming server connections.
The server side of Multiprotocol Net-Library encryption.
Asynchronous I/O or scatter-gather I/O. (This includes true read-ahead scans. SQL Server issues what it thinks are read-ahead requests, but these must be translated to synchronous calls because Windows 98 doesn't support asynchronous I/O. You get the benefit of the larger I/Os that read-ahead provides, but they are synchronous.)
Any component that corresponds to Windows NT and Windows 2000 Services. (The SQL Server database engine and SQL Server Agent run as executable programs on Windows 98.)
Event logs. (SQL Server uses a SQL Server Profiler-4-based mechanism to launch alerts on Windows 98.)
SQL Server Performance Monitor.
The SQL Server Version Upgrade utility.

I'll discuss most of these capabilities and tools in more detail later in the book.

Embedded SQL Server

If you want a version of SQL Server that you can embed in your own applications and redistribute, take a look at Desktop Engine. Desktop Engine is the server component of SQL Server and includes basically the relational engine and the storage engine. This edition of SQL Server 2000 is available on any of the CDs for the other editions in the MSDE folder and also is available with Microsoft Office 10. Office 2000 users can download Desktop Engine, but you need a Service Release version of Office 2000 to access Desktop Engine data from an Office 2000 product.

None of the usual SQL Server tools is installed with Desktop Engine; you're expected to use the visual tools that come with Microsoft Visual Studio for creating objects. In addition, you can use all the usual tools that come with a full SQL Server installation, such as SQL Server Enterprise Manager and SQL Query Analyzer, to access the data stored in Desktop Engine.

Desktop Engine is also available as a set of Windows Installer merge modules, which independent software vendors (ISVs) can use to install Desktop Engine during their own setup process. These modules can be merged into the ISV setup program using available Windows Installer setup development tools. I won't discuss Desktop Engine further in this book; see the documentation for additional details.

NOTE
When you install Microsoft Access 2000, you have the option of installing Desktop Engine or the JET database engine for query processing. A whitepaper on the companion CD in the file Access Data Engine Options.doc describes the differences between these two choices.