SQL Server 2000 Databases on the Desktop

The same Microsoft® SQL Server™ 2000 database engine that supports thousands of concurrent users can also be installed on laptop or desktop computers running either Microsoft Windows® 98, Microsoft Windows NT® Workstation, or Windows 2000 Professional. Two versions of SQL Server 2000 that run on these operating systems are:

SQL Server 2000 Personal Edition
An edition of SQL Server 2000 used on personal workstations or small workgroup servers. SQL Server 2000 Personal Edition includes the management tools, such as SQL Server Enterprise Manager, that come with both SQL Server 2000 Standard Edition and SQL Server 2000 Enterprise Edition.
SQL Server 2000 Desktop Engine
A redistributable version of the SQL Server relational database engine, which third-party software developers can include 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 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.

The SQL Server 2000 Desktop Engine includes support for all of the programming APIs and most of the functionality of the other editions of SQL Server 2000. It also 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). For more information about the features supported by the Desktop Engine, see Features Supported by the Editions of SQL Server 2000.

The database engine included in these two versions of SQL Server 2000 is tuned to support the workloads typical of a single user or a small workgroup. The database engine provides desktop users with essentially the same functionality and features as SQL Server 2000 Standard Edition and SQL Server 2000 Enterprise Edition; however, two exceptions are:

Certain features primarily used in large production databases, such as parallel statement processing and indexed views, are not supported. For more information about the features available in the various editions of SQL Server 2000, see Features Supported by the Editions of SQL Server 2000.
A concurrent workload governor limits the performance of the database engine in these two editions. The performance of individual Transact-SQL batches is decreased when more than five batches are executed concurrently. The amount each batch is slowed down depends on how many batches over the five-batch limit are executing concurrently, and the amount of data retrieved by the individual batches. As more batches are executed concurrently, and as more data is retrieved by each batch, the more the governor slows down the individual batches. You can use the DBCC CONCURRENCYVIOLATION statement to report how often the concurrent workload governor is activated. For more information, see DBCC CONCURRENCYVIOLATION.

The ease-of-use features of the database engine allow it to run in a laptop or desktop environment with minimal configuration tuning from the user. The database engine automatically configures itself to acquire or free resources, such as memory and disk space, as needed. This means that SQL Server 2000 Personal Edition and SQL Server 2000 Desktop Engine can be run on an end-user laptop or desktop computer without requiring the user or database administrator to constantly tune the database.

SQL Server 2000 Personal Edition and SQL Server 2000 Desktop Engine support the same programming model as SQL Server 2000 Standard Edition and SQL Server 2000 Enterprise Edition. Applications use the same APIs (ADO, OLE DB, ODBC, SQL-DMO, and so on) to access the data in all the editions of SQL Server 2000. The only difference is the set of features supported in the higher-level editions, such as failover clustering or federated database servers, although most of these features are administrative or scalability features that are transparent to most applications.

The database engine used in SQL Server 2000 supports optimizations that maximize performance in small laptop or desktop systems with small amounts of memory:

The internal data structures of the database, such as mixed extents, significantly reduce the size of small databases, or databases with many small tables.
When running at its default configuration settings, SQL Server configures itself dynamically to the current resource usage on the computer without the need for tuning commands from the user.
Many configuration options that had to be set manually in SQL Server version 6.5 or earlier have been replaced with internal logic in the database engine that configures these options automatically based on load.
It is no longer necessary to update distribution statistics manually; these are updated automatically.
Database files grow or shrink automatically depending on the amount of data.

SQL Server 2000 replication and the ability of the database engine to attach and detach databases offers good support for mobile and disconnected users with laptops. These users can periodically connect to a regional or departmental server to resynchronize their database information with the main database through replication. Alternatively, a database can be placed on a compact disc and sent to remote users, where they can simply attach it to their server to get the latest information.