SQL Server 2000 Databases on the Desktop
Topic last updated -- January 2004
The same Microsoft® SQL Server™ 2000 database engine that supports thousands of concurrent users can also be installed on laptop or desktop computers running client operating system versions, such as Microsoft Windows® 98, Microsoft Windows Millennium Edition, Microsoft Windows NT® Workstation, Windows 2000 Professional, or Windows XP. 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 (MSDE 2000)
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 files that can be included in the application setup.
For more information about Desktop Engine, see Understanding SQL Server 2000 Desktop Engine (MSDE 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 eight operations are executed concurrently. For more information about the workload governor, see The SQL Server 2000 Workload Governor.
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 (MSDE 2000) 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.