Choosing and Using MSDE 2000 as the Database Engine for Your Application
Maureen Damery Kirby
Microsoft Corporation
October, 2002
Summary: Until recently, Microsoft offered two means of database management: Microsoft Jet (the Microsoft Access database engine that is shipped with both Microsoft Access and Microsoft Visual Studio) and Microsoft SQL Server. With the release of the Microsoft Data Engine (MSDE), a SQL Server-compatible database engine, there is an exciting new option for creating desktop and shared database solutions. MSDE enables developers to build desktop and shared database solutions that easily migrate to SQL Server when the solution must scale. This article discusses the features and benefits of MSDE 2000 and provides links to useful information, including online documentation, Microsoft Knowledge Base articles, and Microsoft white papers, to enable you to build robust MSDE 2000 solutions. (9 printed pages)
Applies to:
MSDE 2000
Contents
Introduction
SQL Server Desktop Engine (MSDE 2000)
Obtain and Install SQL Server 2000 Desktop Engine (MSDE 2000)
Use Microsoft Access With MSDE 2000
Connect to an MSDE 2000 Database
Use OSQL To Manage MSDE 2000
Implement Security and Authentication with MSDE 2000
Back Up Your MSDE 2000 Database
Deploy MSDE 2000 Solutions
Use Replication with MSDE 2000
Upsize MSDE 2000 to SQL Server
Frequently Asked Questions (FAQ)
Troubleshooting Tips
Product Support Services
References
Introduction
Whether you are creating a new application that will access a database or migrating 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 your project's requirements, please refer to the following White Papers:
Microsoft Data Engine (MSDE) for Microsoft Visual Studio 6.0: An Alternative to Jet for Building Desktop and Shared Solutions
The "How to Choose a Database Engine" topic in this whitepaper discusses the features and benefits of Jet, MSDE 2000 and SQL Server 2000 engines to assist you in determining which option would best meet your current and future information management needs.
Microsoft Access 2000: Choosing between MSDE 2000 and Jet
This purpose of this document is to assist you in determining which data engine, Jet or MSDE 2000, to use with Microsoft Access.
SQL Server Desktop Engine (MSDE 2000)
The SQL Server 2000 Desktop Engine (MSDE 2000) is a data engine 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. The common technology base shared between SQL Server and MSDE 2000 enables developers to build applications that can scale seamlessly from portable computers to multiprocessor clusters. For an overview of MSDE 2000, please refer to SQL Server 2000 Desktop Engine (MSDE 2000).
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 can obtain the latest version of MSDE 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, please refer to the Appropriate Uses of MSDE.
For additional information about obtaining and installing MSDE 2000, please refer to:
Q324998 How to Obtain and Install SQL Server 2000 Desktop Engine (MSDE 2000)
Q290627 Microsoft SQL Server 2000 Desktop Engine Is Not Automatically Installed by Office XP Setup
Q301413 Configuring SQL Server 2000 Desktop Engine
Q317328 How to troubleshoot MSDE 2000 Setup
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. You can think of MSDE 2000 as a client/server alternative to the Microsoft Jet file server database engine in Microsoft Access 2002 which enables software developers to embed data storage within their custom applications. For more information on using Microsoft Access 2002 with MSDE 2000 databases, please refer to the technical article Using Microsoft Access 2002 with MSDE 2000, which discusses creating and deploying an Access 2002 Project and MSDE 2000 Database.
The Microsoft Access Upsizing Wizard
The Upsizing Wizard is an add-on tool that can greatly simplify moving an Access database into the SQL Server environment. It is available from a variety of sources, including Microsoft Office 2000 or later and the Microsoft Developer Network. For an introduction to and demonstration of using the Microsoft Access Upsizing Wizard as well as information on what to check prior to upsizing, design considerations, suggested troubleshooting techniques for common upsizing issues and additional resources for information, please refer to Q325017 How to Use the Microsoft Access Upsizing Wizard.
Upsizing an Access 97 Database to MSDE 2000
The recommended manner in which to upsize an Access 97 database to MSDE2000 is to use Access 2002 to load your mdb file and then use the Access 2002 Upsizing Wizard to migrate to MSDE 2000.
Using Access Data Projects With MSDE 2000
With the release of Microsoft Access 2000, an exciting new technology has been introduced: Access projects (.adp files), which allow you to develop true client/server applications from within 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, but instead of using Jet, you're natively using either Microsoft SQL Server or the new SQL Server 2000 Desktop Engine (MSDE 2000) as the back-end database. For more information on Access Projects (ADP's), refer to the following MSDN and Knowledge Base articles:
Introduction to Microsoft Access 2000 Projects and the MSDE 2000
This document 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 back-end database.
Developing Client/Server Solutions with Microsoft Access 2000 Projects
This document 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.
Q308312: How to Use Application Roles with Access Projects and the SQL Server 2000 Desktop Engine (MSDE 2000)
This article explains the capabilities, limitations and workarounds for using Microsoft SQL Server Application roles in a Microsoft Access project.
Q299297: How to Deploy an ADP That Includes the MSDE 2000
This 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, please refer to the book Microsoft Access Projects with Microsoft SQL Server, which can be obtained from Microsoft Press.
Connect to an MSDE 2000 Database
The 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 will recognize the interfaces and will be able to work with them very quickly. ADO is designed as an easy-to-use application level interface to Microsoft's newest and most powerful data access paradigm. For migration assistance from DAO/ODBC Direct to ADO, code samples and additional information, please refer to Q325020 Migrating DAO/ODBCDirect to ADO To Use MSDE.
For code examples to learn how to use the ADO objects, methods, properties, and events, please refer to ADO Code Examples.
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 on this topic, please refer to the white paper Configuring SQL Server 2000 Desktop Engine. This document describes how to install the desktop engine and discusses connectivity as well as the importance of installing MDAC 2.6 or later on client systems that will be accessing the engine.
To download the latest MDAC components, visit our Universal Data Access site.
Use OSQL To Manage MSDE 2000
SQL Server 2000 Desktop Engine (MSDE 2000) does not have its own user interface as 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 which 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 additional information on the OSQL Utility as well as common script samples, please refer to Q325003 How to Manage the SQL Server Desktop Engine (MSDE 2000) by Using the Osql Utility.
For additional information on database maintenance, please refer to the following technical article: Creating and Maintaining MSDE 2000 Databases.
Implement Security and Authentication With MSDE 2000
System administrators and developers need to 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 utilizing MSDE/SQL Server securely within 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, please refer to the following documentation:
SQL Server 2000 Security White Paper
Q325022 MSDE 2000 Security and Authentication
Q176377 Accessing Sql Server with Integrated Security from ASP
Managing Microsoft SQL Server Security with Microsoft Access
Microsoft recommends visiting our Security Website regularly for updates.
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 sample code on using this command, please refer to the following Knowledge Base article:
Q241397 How To Back Up an MSDE 2000 Database with T-SQL
Deploy MSDE 2000 Solutions
For general guidelines and How-To steps for deploying MSDE 2000 along with a Visual Basic Solution using the Package and Deployment Wizard, please refer to the following Knowledge Base article:
Q325016 How To Deploy Your MSDE 2000 Solution With The Package and Deployment Wizard
MSDE 2000 is available as a set of Windows Installer merge modules, which can be used by independent software vendors (ISVs) to install an instance of the SQL Server 2000 Desktop Engine during their own setup process. These merge modules can be merged into the ISV setup program using available Windows Installer setup development tools. MSDE 2000 merge modules (.msm files) can be embedded 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, please refer to the following documentation:
Using SQL Server 2000 Desktop Engine Merge Modules
Integrating MSDE 2000 with your Applications
Embedding MSDE 2000 Setup into the Setup of Custom Applications
Q299795 How to Author MSDE 2000 Setup Packages with Microsoft Visual Studio Installer 1.1
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 on using these Callback Functions, please refer to Q315463 How To Implement a SQL Server 2000 Desktop Engine Callback Function and Example.
Windows Installer is an installation and configuration service that reduces the total cost of ownership. The installer ships 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. The installer enables customers to 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 additional information regarding the Windows Installer, please refer to Q282477 Windows Installer Resources.
For general information on MSDE 2000 Setup and upgrading MSDE 2000 to Service Pack Level, please refer to Q317328 How to troubleshoot MSDE 2000 Setup.
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 needed for distributing data and maintaining data consistency. For additional information on Replication, please refer to:
SQL Server Replication Overview
Q324992 How To Use Replication With MSDE 2000
Upsize MSDE 2000 to SQL Server
Applications may grow beyond the MSDE 2000 2-GB data limit or the application's user base may grow beyond the workload limitation in SQL Server Desktop Engine. If this occurs, Microsoft recommends migrating the MSDE 2000 solution to a SQL Server based solution. SQL Server 2000 Desktop Engine (MSDE 2000) can be upgraded to SQL Server 2000 Personal and Standard Editions. For information on migrating from MSDE 2000 and upgrading to SQL Server, please refer to:
Q325023 Upsizing SQL Server 2000 Desktop Engine (MSDE 2000) to SQL Server
Q322620 How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
Frequently Asked Questions (FAQ)
For answers to common questions about MSDE 2000, please refer to Appropriate Uses of MSDE 2000.
Troubleshooting Tips
For detailed information on troubleshooting the initial as well as upgrade and distribution installations of MSDE 2000, please refer to Q317328 How to troubleshoot MSDE 2000 Setup.
Service Packs:
Service packs are the means by which Microsoft distributes bug fixes and keep a product current. Service Packs include updates and may include system administration tools, drivers, and additional components, which are conveniently bundled for easy downloading. Service packs are cumulative; each new service pack contains all the fixes that are in previous service packs, as well as any new fixes. You do not need to install a previous service pack before you install the latest one. For example, you do not need to install SQL Server 2000 Service Pack 1 (SP1) before you install SQL Server 2000 Service Pack 2 (SP2). For information on obtaining the most recent Service Pack, please refer to Q290211 How to Obtain the Latest SQL Server 2000 Service Pack.
NOTE: In order 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, please contact Microsoft Developer Support at 1-800-936-5800. For additional information regarding this topic, please refer to Q315721 Can Only Apply SQL Server 2000 Desktop Engine (MSDE 2000) Service Pack 2 Download on the Internet to Instances Installed from Sqlrun01.msi.
Product Support Services
The Microsoft MSDE 2000 Advisory Service provides guidance and assistance to IT professionals who are adopting MSDE 2000 into their solutions an opportunity to work closely with Microsoft to ensure its success. This service consists of design, development and deployment guidance and assistance to ensure a successful implementation. For more information, please contact Microsoft Advisory Services at 1-800-936-5200.
Collaborate with others who use our products, including Microsoft Most Valuable Professionals (MVPs) via our public MSDE 2000 newsgroup, microsoft.public.sqlserver.msde, or examine our online support and troubleshooting resource options for developers.
References
WEBCAST: Microsoft Data Engine: Deploying and Troubleshooting MSDE 2000