Article ID: 321518
Article Last Modified on 3/9/2007
APPLIES TO
- Microsoft SQL Server 2000 Desktop Engine (Windows)
This article was previously published under Q321518
Table of Contents
- SUMMARY
- MORE INFORMATION
- Getting started
- SQL Server 2000 Desktop Engine (MSDE 2000)
- Microsoft Knowledge Base "How to" articles
- How to obtain and install SQL Server 2000 Desktop Engine (MSDE 2000)
- How to use Microsoft Access with MSDE 2000
- How to use MSDE 2000 functionality
- How to use OSQL to manage MSDE 2000
- How to implement security and authentication with MSDE 2000
- How to back up your MSDE 2000 database
- How to deploy your MSDE 2000 solution
- How to use replication with MSDE 2000
- How to upsize MSDE 2000 to SQL Server
- Frequently asked questions (FAQ)
- Troubleshooting tips
- Product support services
SUMMARY
Until recently, Microsoft offered the following two means of database management:
- Microsoft Jet (the Microsoft Access database engine that is included with both Microsoft Access and Microsoft Visual Studio)
- Microsoft SQL Server
With the release of SQL Server Desktop Engine (MSDE 2000), a SQL Server-compatible database engine, there is a new option to create desktop and shared database solutions. With MSDE 2000, developers can 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 help you to build robust MSDE 2000 solutions.
back to the top
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:
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:
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:
324998 How to obtain and install SQL Server 2000 Desktop Engine (MSDE 2000)
290627 Microsoft SQL Server 2000 Desktop Engine is not installed by Office XP Setup
301413 "Configuring SQL Server 2000 Desktop Engine" white paper is available in Download Center
317328 How to troubleshoot a SQL Server Desktop Engine 2000 installation and upgrade
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:
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:
325017 How to use Access 2000 Upsizing Wizard
330468 How to use the Upsizing Wizard in Access 2002
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:
308312 How to use Application roles with Access projects and SQL Server 2000 Desktop Edition
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.
299297 How to deploy an Access 2002 project that includes the Microsoft SQL Server 2000 Desktop Engine
For additional reference material on Microsoft Access Data Projects, see the following book that can be obtained from Microsoft Press:
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:
325020 Migrating DAO/ODBCDirect to ADO to use MSDE
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:
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:
325003 How to manage the SQL Server Desktop Engine (MSDE 2000) by using the Osql utility
For additional information about database maintenance, see the following White Paper:
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:
325022 MSDE security and authentication
176377 Accessing SQL Server with Integrated Security from ASP
285097 How to change the default login authentication mode to SQL while installing SQL Server 2000 Desktop Engine by using Windows Installer
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:
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:
241397 How to back up a Microsoft Data Engine database with Transact-SQL
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:
325016 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, 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:
Using SQL Server 2000 Desktop Engine Merge Modules
Integrating MSDE 2000 with your Applications
Embedding MSDE 2000 Setup into the Setup of Custom Applications
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
299795 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 about using callback functions, click the following article number to view the article in the Microsoft Knowledge Base:
315463 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 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:
282477 Windows Installer resources
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:
317328 How to troubleshoot a SQL Server Desktop Engine 2000 installation and upgrade
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:
324992 How to use replication with SQL Server 2000 Desktop Engine (MSDE 2000)
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:
325023 Upsize SQL Server 2000 Desktop Engine to SQL Server
322620 How to upgrade SQL Server 6.5 and 7.0 to SQL Server 2000 (white paper)
Frequently asked questions (FAQ)
For answers to common questions about MSDE 2000, visit the following Microsoft Web site:
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:
317328 How to troubleshoot a SQL Server Desktop Engine 2000 installation and upgrade
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:
290211 How to obtain the latest SQL Server 2000 service pack
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:
315721 Can only apply SQL Server 2000 Desktop Engine 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 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:
REFERENCES
For additional information, visit the following Microsoft Web sites:
Keywords: kbinfo kbjet KB321518
