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).

What 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. SQL Server 2000 Desktop Engine runs under Microsoft Windows 2000 or later and Windows 98 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 or client/server applications run on either version. However unlike SQL Server, SQL Server 2000 Desktop Engine has a 2 gigabyte database size limit, doesn't support Symmetrical Multiprocessing (SMP) on Windows 98 or later, 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.

When to use SQL Server 2000 Desktop Engine

Consider using SQL Server 2000 Desktop Engine as a desktop database alternative to a Microsoft Access database 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 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.

About 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, you can perform some common administrative tasks on a Microsoft SQL Server database.

Command	Description
Backup 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 XP, but only if you have installed SQL Server 2000 Desktop Engine on your computer.