Understanding SQL Server 2000 Desktop Engine (MSDE 2000)
Topic last updated -- January 2004
The SQL Server 2000 Desktop Engine (MSDE 2000) is a redistributable version of the SQL Server relational database engine. MSDE 2000 is designed to be distributed as a part of applications that require a local data store. Software developers can code the setup of their application to install an instance of MSDE 2000 and build a database to be used by the application. The application is then coded to perform any needed administration of that database and instance of MSDE 2000, and to use the data in the database to store application data.
The main component in MSDE 2000, the database engine, provides for the storage of data. MSDE 2000 includes a version of the SQL Server 2000 database engine tailored to run on a notebook or desktop client computer, operating as the local data store for an application supporting one user or a small workgroup of users. The database engine included in MSDE 2000 includes most of the functionality of the database engine included in the other editions of SQL Server 2000. But it does not include some features used in large production databases, such as parallel queries and indexed views. For more information about the database engine features supported by MSDE 2000, see Features Supported by the Editions of SQL Server 2000.
MSDE 2000 also includes parts of two SQL Server 2000 components that provide the capability to integrate MSDE 2000 databases into the data storage systems of a larger organization.
- Replication allows you to copy, distribute, and modify data across multiple data sources. MSDE 2000 can be a transactional replication subscriber in SQL Server 2000 replication topologies, and both a publisher and subscriber in snapshot and merge replication. For more information about replication, see Replication Overview.
- Data Transformation Services (DTS) is a set of tools and programming objects that let you extract, transform, and consolidate data from many data sources into one or more other data sources. DTS packages cannot be developed using instances of MSDE 2000, but packages developed using the other editions of SQL Server 2000 (such as Developer Edition) can be run on instances of MSDE 2000. For more information about DTS, see DTS Overview.
Developing applications to use MSDE 2000 is very similar to writing database applications for the other versions of SQL Server 2000. The most significant difference is that MSDE 2000 does not support the SQLXML functionality, the latest version of which is available as the SQL Server Web Services Toolkit. For more information, see Programming SQL Server 2000 Desktop Engine (MSDE 2000) Applications.
The SQL Server 2000 Desktop Engine does not include the SQL Server 2000 graphical management tools, such as SQL Server Enterprise Manager or SQL Server Query Analyzer. Instances of MSDE 2000 are typically administered by:
- Coding the application distributing MSDE 2000 to perform database administration tasks that are typical of the application environment.
- Using the SQL Server 2000 command prompt utilities included with MSDE 2000. For example, you could use the osql command prompt utility to run Transact-SQL statements or scripts.
- You can manage instances of MSDE 2000 using the graphical tools included with another edition of SQL Server only if you acquired MSDE through SQL Server 2000 (Developer Edition, Standard Edition, or Enterprise Edition), and if you are using MSDE in conjunction with a properly licensed copy of SQL Server 2000.
For more information about administering MSDE 2000, see Administering SQL Server 2000 Desktop Engine (MSDE 2000).
Because MSDE 2000 uses SQL Server 2000 technology, most of the documentation for MSDE 2000 is included in SQL Server 2000 Books Online. For information on which parts of the SQL Server documentation applies to MSDE 2000, see SQL Server 2000 Desktop Engine (MSDE 2000) Documentation.
The SQL Server 2000 Desktop Engine (MSDE 2000) uses a different setup than the editions of SQL Server 2000. The MSDE 2000 setup uses Windows Installer technology and is designed to be included in an application setup program. While the MSDE 2000 setup program can be run by itself, it is a command prompt utility that has no user interface. For more information about running the MSDE 2000 setup and incorporating it in the setup of an application, see Distributing SQL Server Applications Overview.
MSDE 2000 is available from several locations:
- MSDE 2000 is distributed with several Microsoft development applications. For information on how to obtain MSDE 2000, see this Web page. For more information on the Microsoft products that include MSDE 2000, see this Web page.
- You can use the MSDE 2000 files in SQL Server 2000 Service Pack 3a (SP3a) or later to install instances of MSDE 2000, provided you are covered by a license to install MSDE 2000. The latest MSDE 2000 service packs are available from this Web page. For more information about MSDE 2000 licensing, see this Web page.
- There is an early version of MSDE 2000 on SQL Server 2000 CDs. Do not use these files to install new instances of MSDE 2000. Instead, use the files from the latest SQL Server 2000 service pack.
SQL Server 2000 Desktop Engine (MSDE 2000) Architecture
SQL Server 2000 Desktop Engine (MSDE 2000) shares the same fundamental database architecture as the editions of SQL Server 2000:
- The database engine operates as a service or process that is separate from any application using MSDE 2000. Up to 16 copies of the database engine from either MSDE 2000 or any other version of SQL Server can be running at the same time on the same computer. Each copy is called an instance. Each instance is installed, started, stopped, configured, and patched independently of the other instances. For more information about instances, see Multiple Instances of SQL Server.
- Each instance of the database engine has one or more user databases that hold the data managed by that instance. SQL Server 2000 is a relational database, which means that the data it stores is organized into relations (commonly called tables). Each table has columns that define the characteristics of the object represented by the table, such as name, size, and price, and rows that represent on occurrence of the object type represented by the table. For more information about tables, see Data Types and Table Structures.
- Each database has other objects besides tables that provide additional functionality. Views allow you to tailor how different classes of users see the data in a database. Stored procedures allow you to program logic in the database. Triggers, rules, constraints, and defaults enforce business rules when users insert, update, or delete data. For more information about these database objects, see Database Architecture.
- Applications are coded to connect to an instance of the database engine and then access the data in one or more of the databases managed by the instance. The application runs as a separate process from the database engine. It must use the SQL Server client communications components to send requests for data to the instance of the database engine and retrieve any results or messages. For more information about this communications mechanism, see Communication Components.