When to choose MSDE 2000 as the database engine for your application (321518)

MORE INFORMATION

Getting started

Whether you create a new application that accesses a database, or you migrate an existing database application to a new database engine, it is important to consider each technology. For an overview of the features and functionality of the Jet, MSDE 2000, and SQL Server 2000 engines, and how they may best suit the requirements of your project, see the following white paper. The "How to choose a database engine" topic in this white paper discusses the features and benefits of Jet, MSDE 2000, and SQL Server 2000 engines to help you to determine which option best meets your current and future information management needs.

The following white paper helps you to determine which data engine, Jet or MSDE 2000, to use with Microsoft Access:
back to the top

SQL Server 2000 Desktop Engine (MSDE 2000)

MSDE 2000 is a data engine that is built and based on core SQL Server technology. With support for single- and dual-processor desktop computers, MSDE 2000 is a reliable storage engine and query processor for desktop extensions of enterprise applications. With the common technology base that is shared between SQL Server and MSDE 2000, developers can build applications that can scale seamlessly from portable computers to multiprocessor clusters. For an overview of MSDE 2000, see the following white paper:
back to the top

Microsoft Knowledge Base "How to" articles

"How to" articles provide step-by-step instructions to perform specific tasks.

How to obtain and install SQL Server 2000 Desktop Engine (MSDE 2000)

In addition to SQL Server 2000, MSDE 2000 is distributed on the CD with all editions of Microsoft Office that include Microsoft Access 2002. It is also available to registered users of Microsoft Visual Studio .Net. Registered users of Visual Studio .NET can obtain the latest version of MSDE 2000 by contacting Microsoft Developer Support at 1-800-936-5800. Several Microsoft product licenses convey the right to use and redistribute MSDE 2000. For information about product licenses or subscription services, visit the following Microsoft Web sites:

For more information about obtaining and installing MSDE 2000, click the following article numbers to view the articles in the Microsoft Knowledge Base:

back to the top

How to use Microsoft Access with MSDE 2000

The Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is an alternative to Microsoft Jet for data storage and data management. MSDE 2000 is a client/server alternative to the Microsoft Jet file server database engine in Microsoft Access 2002. With MSDE 2000, software developers can embed data storage in their custom applications. For more information about using Microsoft Access 2002 with MSDE 2000 databases, see the following white paper, which discusses creating and deploying an Access 2002 Project and MSDE 2000 Database, on the Microsoft Developer Network (MSDN) Web site:
back to the top

The Microsoft Access Upsizing Wizard
The Upsizing Wizard is an add-on tool that simplifes moving an Access database to the SQL Server environment. It is available from a variety of sources, including Microsoft Office 2000 or later, and also from the Microsoft Developer Network. For more information about how to use the Upsizing Wizard, click the following article number to view the article in the Microsoft Knowledge Base: Upsizing an Access 97 database to MSDE 2000
Microsoft recommends that you upsize an Access 97 database to MSDE 2000 by using Access 2002 to load your .mdb file, and then to use the Access 2002 Upsizing Wizard to migrate to MSDE 2000.

back to the top

Using Access data projects with MSDE 2000
With the release of Microsoft Access 2000, a new technology has been introduced, Access projects (.adp files), with which you can develop true client/server applications in the Access environment. Creating these project files differs from the traditional file-server development that Access developers have typically used, such as developing a database application with the Microsoft Jet database engine, and saving all the database objects in a single .mdb file. Like an .mdb file, an Access project file also allows you to develop a database application in the Access environment. However, instead of using Jet, you natively use either Microsoft SQL Server or the new SQL Server 2000 Desktop Engine (MSDE 2000) as the server database. For more information about Access Projects (ADPs), see the following MSDN white papers.

The following white paper lists the best sources of detailed information about Microsoft Access projects (.adp files), which allow you to develop client/server database applications in Access by using either Microsoft SQL Server, or Microsoft Data Engine (MSDE) as the server database.

The following white paper provides an introduction to Developing Client/Server Solutions with Access 2000 Projects, an online book that explains in detail how to create and work with Microsoft Access project (.adp) files.
For more information about the capabilities, the limitations, and the workarounds for using Microsoft SQL Server Application roles in a Microsoft Access project, click the following article number to view the article in the Microsoft Knowledge Base: The following article provides the code that you must use to find the server, to start the server if it is not started already, to attach the database to the server, and to connect the project to the newly attached database. The code is specific to use in a project. However, much of the code can be used by any Visual Basic for Applications (VBA) application.
For additional reference material on Microsoft Access Data Projects, see the following book that can be obtained from Microsoft Press: back to the top

How to Use MSDE 2000 functionality

How to Connect to an MSDE 2000 Database

ActiveX Data Objects (ADO) represents the best of the existing Microsoft data access programming models. If you are familiar with Data Access Objects (DAO) or Remote Data Objects (RDO), you recognize the interfaces and can work with them very quickly. ADO is designed as an easy-to-use application-level interface to newest and most powerful data access paradigm of Microsoft. For more information about migration assistance from DAO/ODBC Direct to ADO, and for code samples, click the following article number to view the article in the Microsoft Knowledge Base: For additional information, visit the following Microsoft Web site:

For code examples to learn how to use the ADO objects, methods, properties, and events, visit the following MSDN Web site: Note MDAC 2.6 and later include a new version of the DBNETLIB protocol that allows connections to SQL Server 6.5, SQL Server 7.0, or SQL Server 2000. This new driver also supports connecting to SQL Server 2000 named instances. Users who have an earlier version of MDAC, such as MDAC 2.5, will have problems connecting to SQL Server 2000, including SQL Server Desktop Engine, on any platform where SQL Server was installed with a named instance. For additional information about this topic, see the following white paper, which describes how to install the desktop engine, and also discusses connectivity and the importance of installing MDAC 2.6 or later on client systems that access the engine: To download the latest MDAC components, visit the following Microsoft Web site:

back to the top

How to use OSQL to manage MSDE 2000

SQL Server 2000 Desktop Engine (MSDE 2000) does not have its own user interface because it is primarily designed to run in the background. Users interact with MSDE 2000 through the application in which it is embedded. The OSQL Utility is the only tool that is included with MSDE 2000. OSQL allows you to enter Transact-SQL statements, system procedures, and script files for maintaining an MSDE 2000 database. For more information, click the following article number to view the article in the Microsoft Knowledge Base: For additional information about database maintenance, see the following White Paper:back to the top

How to implement security and authentication with MSDE 2000

System administrators and developers must be aware of the implications of not securing their databases. The Microsoft Security Team is advising MSDE/SQL Server administrators to comply with our best practices for using MSDE/SQL Server securely in their environments.
For an overview of SQL Server 2000 Desktop Engine (MSDE 2000) Security and Authentication and some helpful tips on how to make your data more secure, see the following documentation:

For more information about managing SQL server, visit the following MSDN Web site:For regular security updates, Microsoft recommends that you visit the following Microsoft Web site:
back to the top

How to back up your MSDE 2000 database

If you have Access 2000 or later, you can use the backup command in the Database Utilities menu of an Access project to back up an MSDE 2000 database. If SQL Client Tools are installed, you can use SQL Enterprise Manager. However, if you only have MSDE 2000 installed, the TSQL Backup Database command can be executed with the OSQL.exe to back up an MSDE 2000 database. For more information about using this command, click the following article number to view the article in the Microsoft Knowledge Base: back to the top

How to deploy your MSDE 2000 solution

For more information about general guidelines and "How to" steps for deploying MSDE 2000, and a Visual Basic Solution using the Package and Deployment Wizard, click the following article number to view the article in the Microsoft Knowledge Base: MSDE 2000 is available as a set of Windows Installer merge modules, that independent software vendors (ISVs) can use to install an instance of SQL Server 2000 Desktop Engine during their own setup process. You can merge these merge modules into the ISV setup program by using available Windows Installer setup development tools. You can embed MSDE 2000 merge modules (.msm files) into a Windows Installer-based setup application by creating your own .msi file. For information about Merge Modules and consuming MSDE 2000 into Custom Applications, visit the following Microsoft Web sites: For more information, click the following article number to view the article in the Microsoft Knowledge Base: The MSDE 2000 installation package permits you to use a callback function to track progress, or to perform custom actions during the setup. For more information about using callback functions, click the following article number to view the article in the Microsoft Knowledge Base: Windows Installer is an installation and configuration service that reduces the total cost of ownership. The installer is included with Microsoft Windows 2000, Windows XP, Windows Server 2003, and Windows Millennium Edition (Windows Me). The installer is also provided as a service pack to Windows 95, Windows 98, and Microsoft Windows NT, version 4.0. With the installer, customers can better address corporate deployment, and provide a standard format for component management. The Windows Installer supports advertisement of applications and features according to the operating system. For more information about the Windows Installer, click the following article number to view the article in the Microsoft Knowledge Base: For more information about MSDE 2000 Setup and upgrading MSDE 2000 to Service Pack Level, click the following article number to view the article in the Microsoft Knowledge Base:
back to the top

How to use replication with MSDE 2000

Microsoft SQL Server 2000 replication is a set of solutions that allow you to copy, distribute, and potentially modify data across your enterprise. SQL Server 2000 includes several methods and options for replication design, implementation, monitoring, and administration to give you the functionality and flexibility that you must have to distribute data and maintain data consistency. For additional information about replication, visit the following MSDN Web site: For more information, click the following article number to view the article in the Microsoft Knowledge Base: back to the top

How to upsize MSDE 2000 to SQL Server

Applications may grow beyond the MSDE 2000 2-GB data limit, or the user base of the application may grow beyond the workload limitation in SQL Server Desktop Engine. If this occurs, Microsoft recommends that you migrate the MSDE 2000 solution to a SQL Server-based solution. You can upgrade SQL Server 2000 Desktop Engine (MSDE 2000) to SQL Server 2000 Personal and Standard Editions. For more information about migrating from MSDE 2000 and upgrading to SQL Server, click the following article numbers to view the articles in the Microsoft Knowledge Base: back to the top

Frequently asked questions (FAQ)

For answers to common questions about MSDE 2000, visit the following Microsoft Web site:

back to the top

Troubleshooting tips

For more information about troubleshooting the initial installation, and about upgrade and distribution installations of MSDE 2000, click the following article number to view the article in the Microsoft Knowledge Base: Service Packs:
Microsoft distributes bug fixes, and keeps products current, with service packs. Service Packs include updates, and may also include system administration tools, drivers, and additional components, which are conveniently bundled to download easily. Service packs are cumulative; each new service pack contains all the fixes that are in previous service packs, and any new fixes. You do not have to install a previous service pack before you install the latest one. For example, you do not have to install SQL Server 2000 Service Pack 1 (SP1) before you install SQL Server 2000 Service Pack 2 (SP2). For more information about obtaining the most recent service pack, click the following article number to view the article in the Microsoft Knowledge Base: Note To apply SQL Server 2000 Desktop Engine (MSDE 2000) Service Packs to instances of MSDE 2000 other than the default, you must apply the service pack from the MSDE 2000 Refresh CD. To obtain the Refresh CD, contact Microsoft Developer Support at 1-800-936-5800. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
back to the top

Product support services

The Microsoft MSDE 2000 Advisory Service provides guidance and assistance to IT professionals, who use MSDE 2000 in their solutions, with an opportunity to work closely with Microsoft to ensure the success of MSDE 2000. This service includes design development and deployment guidance, and assistance to ensure a successful implementation. For more information, contact Microsoft Advisory Services at 1-800-936-5200.

Collaborate with others who use Microsoft products, including Microsoft Most Valuable Professionals (MVPs) through the Public MSDE 2000 Newsgroup:
Examine online support and troubleshooting at the following MSDN Web site:back to the top