About SQL Server 2000 Desktop Engine

Some of the content in this topic may not be applicable to some languages.

 Note   The previous version of SQL Server 2000 Desktop Engine was called Microsoft Data Engine (MSDE).

ShowWhat is SQL Server 2000 Desktop Engine?

Microsoft SQL Server 2000 Desktop Engine is a technology that provides local data storage in a format compatible with Microsoft SQL Server 2000. You can also use SQL Server 2000 Desktop Engine as a remote data storage solution. Think of SQL Server 2000 Desktop Engine as a client/server data engine alternative to the file server Microsoft Jet database engine (Microsoft Jet database engine: The part of the Access database system that retrieves and stores data in user and system databases. It can be thought of as a data manager upon which database systems, such as Access, are built.). SQL Server 2000 Desktop Engine runs under Microsoft Windows 2000 or later. It is designed and optimized for use on smaller computer systems, such as a single-user computer or small workgroup server.

Because SQL Server 2000 Desktop Engine is based on the same data engine as SQL Server, most Microsoft Access projects (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.) or client/server applications run on either version. However unlike SQL Server, SQL Server 2000 Desktop Engine has a 2 gigabyte database size limit, and when using transactional replication, can't be a replication publisher (although it can act as a replication subscriber).

 Note   Do not confuse SQL Server 2000 Desktop Engine with SQL Server 2000 Personal Edition, which includes a full set of management tools and most of the functionality of SQL Server Standard Edition, but is optimized for personal use and is a separate product.

ShowWhen to use SQL Server 2000 Desktop Engine

Consider using SQL Server 2000 Desktop Engine as a desktop database alternative to a Microsoft Access database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) in the following ways:

  • As a small workgroup server database. You can develop your applications by using an Access project connected to SQL Server 2000 Desktop Engine, if you anticipate that your workgroup and its business requirements may grow over time until they eventually need the full functionality of SQL Server 2000 running on a larger network server. In general, SQL Server 2000 Desktop Engine can handle a user workload similar to the Microsoft Jet database engine (Microsoft Jet database engine: The part of the Access database system that retrieves and stores data in user and system databases. It can be thought of as a data manager upon which database systems, such as Access, are built.) of an Access database. However, SQL Server 2000 Desktop Engine has a limit of five simultaneous active query batches (also called threads). Subsequent query batches are queued by SQL Server 2000 Desktop Engine and executed when the next batch is available. If you want to see how many times you have reached this limit in a SQL Server 2000 database, you can execute the DBCC CONCURRENCYVIOLATION command. For more information on workloads, batches, and DBCC, see the SQL Server documentation.
  • As a replication subscriber in an SQL Server replicated database environment. For example, you may have mobile users working remotely from the main office who access and update SQL Server 2000 Desktop Engine on laptops or disconnected computers, but then periodically connect to the Master SQL Server database to reconcile changes.
  • To easily develop and test an Access project or client/server application on a personal computer or workstation, and then modify the Access project connection information to connect to an SQL Server database on a remote server for final testing and production.
  • As the local subscription database for offline data.

ShowAbout using SQL Server 2000 Desktop Engine database utilities from Access

Microsoft SQL Server 2000 Desktop Engine offers the basic database engine features of SQL Server 2000 but does not include a user interface, management tools, analysis capabilities, merge replication support, client access licenses, developer libraries, or SQL Server Books Online. It also limits database size and user workload. It has the smallest footprint of any edition of SQL Server 2000 and is thus an ideal embedded or offline data store.

From a Microsoft Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.), you can perform some common administrative tasks on a Microsoft SQL Server database.

Command Description
Back Up SQL Database Creates a backup file (.dat) of the current SQL Server database.
Restore SQL Database Restores a SQL Server database from a backup database file (.dat).
Drop SQL Database Deletes a SQL Server database.
Copy Database File Copies a SQL Server database 7.0 or later database to another SQL Server.
Transfer Database Transfers a SQL Server Database version 7.0 or later database file (.mdf).

These commands are visible from the Database Utilities command on the Tools menu when you install Microsoft Office 2003, but only if you have installed SQL Server 2000 Desktop Engine on your computer.

 
 
Applies to:
Access 2003