About 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. 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, 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.

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.

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.

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.