James Fitzgerald
Clarity Consulting, Inc.
May 1999
Summary: Discusses the benefits of using the Microsoft Data Engine (MSDE) in creating desktop and shared database solutions. Covers options for accessing data within MSDE, including Microsoft's ActiveX® Data Objects (ADO) and Data Access Objects (DAO) technologies. (26 printed pages)
Introduction
What Technologies are Involved?
How to Choose a Database Engine
Installing MSDE
Creating MSDE Solutions
Data Access Options in MSDE
Migrating from MSDE to SQL Server 7.0
Comparison Reference: Jet, MSDE, and SQL Server Technologies
Conclusion
Other Resources
Developers creating applications are faced with a choice: with so many database options available, which technology should they choose? Many factors need to be considered before selecting a database. For example, how many users will the application need to support? Will all of the users access the system at the same time? What are the costs involved? Will the technology allow the database to grow if the application requirements change over time?
Until recently, Microsoft offered two means of database management: Microsoft Jet (the Microsoft® Access database engine that is shipped with both Access and Microsoft Visual Studio®) and Microsoft SQL Server™. While each technology has its strengths, it was previously difficult for developers to select a single database system that would meet both the long term and short term needs of an application from desktop to data center. Jet-based solutions are a good choice for desktop or shared applications with their ease of use and low cost. However, developers find that as their systems grow, many code changes are required before their Jet applications can be migrated to the performance and scalability of 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 Visual Studio developers to build freely distributable desktop and shared database solutions that easily migrate to SQL Server 7.0 when the solution must scale.
This document is intended for developers who are comfortable developing applications with the Jet database engine as well as for those who are new to the Microsoft SQL Server database development environment. This paper provides all the information needed to evaluate MSDE as a database engine for desktop and shared database solutions, including installing the MSDE service and migrating existing solutions that use Jet to MSDE.
This document discusses the benefits of using the Microsoft Data Engine (MSDE) in creating desktop and shared database solutions. We walk through the migration of an existing sample Access database—using the Jet engine—to an Access and MSDE client-server database application and install a complete version of a client-server Access application. We discuss various options for accessing data within MSDE databases, including Microsoft's ActiveX Data Objects (ADO) and Data Access Objects (DAO) technologies. Finally, a reference section comparing embedded database technologies is included at the end of this document.
Before discussing the relative pros and cons of each database engine, it is important to understand their features and functionality. The following section briefly describes the three database technologies this paper examines.
A "database engine" is the core process that a database management system, such as Access or SQL Server, uses to store and maintain data. A database engine is often just a database management system without its graphical management tools. Engines typically serve two main purposes. The first is to store information, and the second is to process requests for stored information. Requests to retrieve or alter stored data are often called database queries.
The most common use for a database engine is in conjunction with standalone custom database applications created with tools such as Microsoft Visual Studio. In these scenarios, generic graphical database management tools are not needed since the custom application generally provides its own facilities for management of the underlying database. In addition, since database engines are easily distributable, custom application developers can include the engine as a part of the "setup" application.
The Microsoft Data Engine (MSDE) is a fully SQL Server-compatible database engine that can be used for desktop and shared database solutions built with either Visual Studio 6.0 or Access 2000. In addition, solutions built with MSDE for Visual Studio 6.0 or Office 2000 Developer can be distributed royalty-free. MSDE is based on SQL Server technology and is fully compatible with SQL Server 7.0. When used in Access 2000, MSDE is an attractive alternative to Jet—the default Access 2000 database engine. In addition, MSDE is available to Visual Studio 6.0 developers who wish to build custom applications with embedded databases that are SQL compliant. SQL compliance enables desktop and shared database solutions using MSDE to be scaled up to SQL Server without code modifications.
MSDE operates as a service. Services are applications that run as background processes. The behavior of services differs from that of other applications. For example, while most applications are executed only when a user launches the application from the Start menu, services such as MSDE are generally started and stopped by the operating system environment. Desktop and shared database solutions using MSDE require that the MSDE service be running. Services are typically used by other applications, not end-users. A service runs in the background and waits for processing requests. In the case of MSDE, these requests are for database operations. Services need not have a user interface, since services are used by other applications, not users. An Access 2000 project or a Visual Basic application can interact directly with a service. These applications serve as the user interface to MSDE.
In summary, the MSDE relational database engine exhibits the following characteristics and features:
Jet 4.0 is the default database engine used in Microsoft Access 2000, and it is also included with Visual Studio 6.0 tools. Jet is a file-based data management system: databases are contained in single database file, and read-write operations to the database are controlled by the client workstation. Jet can handle multiple users and is scaled for desktop and shared database solutions. Microsoft Access provides an easy-to-use interface to the Jet database engine. Developers can also use Visual Basic and other development tools to create custom front-end applications that use Jet as an embedded database.
SQL Server is a client-server database management system. Unlike file-based databases, client-server database engines manage read-write operations to the database. Because of this, client-server databases such as SQL Server can handle many more concurrent users and vastly greater amounts of data. SQL Server is designed to meet the most demanding database application requirements for the enterprise, including operational and decision support systems implemented today and in the future. SQL Server is offered in different editions based upon data, user, and hardware requirements.
SQL compliance is the level to which databases adhere to the ANSI Structured Query Language (ANSI-SQL) industry standards for database communication. MSDE and SQL Server both conform to Transact-SQL (T-SQL) language guidelines. Jet does not. Instead, Jet implements its own query language that is capable of utilizing several Visual Basic for Applications functions. Transact-SQL is a well-defined language for communicating with and programming a database server. Transact-SQL is a superset of ANSI-SQL, extending functionality to enable a server to be programmed with custom business logic. This custom business logic can be invoked from the client and executed on the server. Conformance with Transact-SQL allows MSDE objects to be fully compatible with SQL Server, an advantage over Jet.
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. Which one should you choose? You should consider MSDE as an alternative to Jet 4.0 for your desktop and shared database solutions. The following section discusses a variety of database application requirements seen in most development efforts and provides recommendations as to which database engine may best suit your project's requirements.
Table 1 shows a list of enterprise requirements and tells how each of the data engines handles these requirements. If your application has any of the needs listed in the left column (or might have any of these needs in the future), you will want to consider implementing MSDE as an alternative to Jet.
Table 1. Database Requirements
| Requirement | SQL Server and MSDE (use MSDE if these are future requirements) | Microsoft Access (Jet) |
| Scalability |
|
|
| Business Critical |
|
|
| Rapid Application Prototyping |
|
|
| Additional User Interfaces |
|
|
MSDE is an attractive option for upgrading existing Jet applications to improve scalability or for creating new desktop and shared database solutions that can be easily migrated to full SQL Server, as the solutions must need to scale.
MSDE data engines are fully compatible with SQL Server. Thus, tables, stored procedures, triggers, and any other database objects created in MSDE will operate without modification in a SQL Server database. In contrast many Jet objects, such as queries, cannot be migrated to SQL Server without modification. If you are considering migrating to SQL Server in the future, or it is likely that your database application will exceed the limitations of Jet, you should choose MSDE. Doing so will provide you with the simplest path to upsizing the database to SQL Server without changing a single line of code.
MSDE differs from Jet in that it is not file based and is not subject to the limits of a file-based database. File-based databases limit performance of applications because database processing occurs on the client machine. With server-based database systems like SQL Server and the MSDE technology, requests for information are processed on the server and only the resultant data is sent back to the client application. This scenario greatly reduces network traffic; it also allows users to access data and experience acceptable application performance without a high-end client workstation.
When multiple users are using a file-based database, each must open and read information from the same file. This can create a bottleneck, as each application user waits for their turn to read from, or write to, the database file. Server-based database systems are specifically tuned to shield client applications from this kind of problem, resulting in improved application performance.
When applications are distributed across a network, client-side processing begins to take a toll on the performance of file-based databases. When a shared file-based database is located on a network file server, each client application must "pull" the entire contents of the query set's source data to the client workstation, even if the request yields only a single row of summarized data. Server-based data engines perform all processing on the server and return only query results to the client. Applications that access the database can then perform well over low-bandwidth links.
MSDE is the best database engine choice if your database application is to be distributed over a network, accessed by multiple users simultaneously, or accessed over a slow link.
MSDE databases running on Windows NT and Windows 2000 platforms are capable of utilizing Windows 2000-integrated security to protect database objects. Jet databases are not. Both MSDE and Jet allow you to secure individual database objects such as tables or stored procedures.
If you want to leverage the security features of Windows 2000, MSDE is the right choice.
MSDE is available to any licensed customer of Office 2000 Professional, 2000 Premium edition, or any Visual Studio 6.0 Enterprise or Professional edition tool, including:
Developers that build desktop and shared database solutions using MSDE may distribute their applications royalty-free, provided that the developer of an MSDE solution is licensed for the following products:
Jet databases are also freely distributable as the data engine for solutions built with Microsoft Office and Visual Studio development tools. However, desktop and shared database solutions built with Jet do not offer the clearest path to SQL Server. If cost is a limitation with your development effort, MSDE is the best choice.
For desktop or shared database solutions that have growth potential, using MSDE as the data engine technology provides a low-cost solution for the near term, while maintaining a clear migration path for future growth. If and when organization needs change, scaling up to a full-size SQL Server solution can be done without changes to a single line of code. Additionally, by using the same SQL-compliant methods, developers can transition seamlessly between small MSDE systems and larger SQL Server systems using the same data access language.
Jet 4.0 can be a good choice for many database systems. This section outlines several situations where a Jet database would be the optimal solution.
With Jet, backup and distribution is easy because the database is contained in a single file. Other files are required to access Jet databases from applications such as Visual Basic or Microsoft Access, but these file dependencies are well defined and do not require a significant amount of space or processing power to operate. MSDE is more difficult to distribute and install than Jet. If you want to simplify database distribution and setup, Jet is a good choice.
Jet 4.0 databases can be distributed as part of solutions built with:
If cost is a factor in your project, Jet is a good choice.
Both MSDE and the Jet database are tuned for desktop and shared use. There are applications that may realistically never grow beyond a 2-gigabyte (GB) size or need to support more than twenty users. If you have an existing Jet application that probably won't grow much in size or be required to support a large user base, Jet will continue to be a good option.
Jet is fairly compact, which makes it a good choice for systems with limited resources. Of the three database-engine options outlined here, Jet uses the least amount of storage space and consumes the least amount of system memory. If you are using machines with limited disk space or system resources, Jet is the best choice.
Jet 4.0 maintains the greatest compatibility for systems developed with previous versions of Jet. You may have multiple versions of an existing Jet application (a difficult situation in and of itself!) and want to maintain a Jet/Access solution without adding SQL Server or MSDE into the mix. If you have a database application that is dependent on a function available in a pervious version of Jet, or a function that would require a great deal of effort to convert to a SQL-compliant database, Jet may be a better option than MSDE or the full SQL Server 7.0.
For desktop systems that will not grow in size or user base, Jet databases provide a low-cost, low-overhead solution that maintains the greatest backward compatibility with existing Jet solutions.
Why not develop all of your applications in MSDE or Jet? There are several situations where MSDE and Jet become impractical. Several of these cases are outlined in what follows.
MSDE has a data limit of 2 GB. Jet also has a data limit of 2 GB in each of its data files, but because Jet database files can be linked together, size is really only limited by disk space. However, managing multiple data files can become a difficult administrative task. If a database is currently larger than 2 GB, or has the potential to grow beyond this limit in the near future, SQL Server 7.0 is the best option.
MSDE is tuned for best optimum performance at five concurrent users or less. Microsoft strongly suggests that the number of concurrent users be five or less in order to enjoy the performance levels available with MSDE.
Jet can support up to 255 users from a technical and licensing standpoint, but is recommended for less than 20 total users. As the user base increases, performance degrades significantly with file-based databases.
SQL Server 7.0 is capable of supporting hundreds or thousands of concurrent users at the highest levels of enterprise-class performance. If an application requires more than five concurrent users, SQL Server is the best option.
If database requirements call for more than 2 GB and performance is an important factor, SQL Server is the best option. SQL Server provides the best performance and highest scalability in database technology.
MSDE is available for use by all licensed customers of Office 2000 Professional, Office 2000 Premium, and Office 2000 Developer editions and Visual Studio 6.0 Professional and Enterprise editions, including:
Solutions using MSDE for Visual Studio 6.0 or Office 2000 Developer are freely distributable subject to the terms of the end-user license agreement accompanying these products. Developers may distribute these solutions to their customers and end-users royalty-free to Microsoft.
MSDE solutions that interact with a central SQL Server 7.0 server require a per-seat client access license (CAL). The central SQL Server database must be running in per-seat mode. For more information on SQL Server 7.0 pricing and licensing, see the SQL Server site at www.microsoft.com/sql/70/gen/pricing.htm.
MSDE runs as a service on the machine where it is installed. It only needs to be installed once as the MSDE service can host multiple databases from different applications.
Note If any editions of SQL Server are installed on a machine, then MSDE is not required, since all editions of SQL Server are capable of hosting any databases that rely upon MSDE. Using SQL Server rather than MSDE to host an application's databases will not limit functionality. SQL Server supports all of the functionality of MSDE. MSDE does not, however, support all of the features of SQL Server. Certain features, such as rich text search for example, are not supported by MSDE. For more details on SQL Server features that are supported by MSDE, see the MSDE for Visual Studio 6.0 FAQ at http://msdn.microsoft.com/vstudio/msde/genfaq.asp.
The first step in installing MSDE is to review its operating requirements. A typical MSDE installation requires 55 megabytes (MB) of local hard drive space, and 64 MB of system memory is recommended.
MSDE is for use with Windows 98, Windows 95, Windows NT 4.0 with Service Pack 4 or later installed, and Windows 2000. MSDE runs on both the Intel and Alpha versions of Windows. Each platform has slightly different requirements, and certain features are available under Windows 2000 and Windows NT 4.0. The requirements are outlined below.
Certain MSDE features are not available under Windows 95 or Windows 98. These include integrated security options, advanced performance features, and support for certain network platforms. Check the MSDE for Visual Studio 6.0 Technical FAQ at http://msdn.microsoft.com/vstudio/msde/techfaq.asp for further details on limitations when installing on these platforms.
MSDE runs on both Workstation and Server editions of Windows NT 4.0. Service Pack 4 is required to run MSDE on any Windows NT 4.0 platform. MSDE runs on all editions of Windows 2000.
MSDE is distributed in all versions of Office that include Access, such as Office 2000 Professional, Office 2000 Developer, and Office 2000 Premium, and it is also available to registered users of any Visual Studio 6.0 Professional or Enterprise edition tool (such as Visual Basic 6.0 Enterprise). MSDE for Visual Studio 6.0 is available for download and ordering at http://msdn.microsoft.com/vstudio/msde/. Installation procedures vary depending upon which distribution you receive.
MSDE for Visual Studio 6.0 is intended for inclusion with custom applications. Visual Studio developers are encouraged to
SQL Server Developer edition is available to Visual Studio 6.0 developers who receive MSDE for Visual Studio 6.0, and is a full copy of the SQL Server 7.0 product licensed for the sole use of developing and testing MSDE solutions (see the end user license agreement for more details). SQL Server 7.0 Developer edition includes SQL Server management tools that are useful during database application development.
The redistributable MSDE engine included with MSDE for Visual Studio 6.0 does not have its own UI for developing MSDE databases. Visual Studio developers building MSDE solutions may use the Visual Studio 6.0 data tools, Access 2000, or SQL Server Enterprise Manager as the development environments.
Note Windows 95 and Windows 98 developers who have also licensed Visual Studio 6.0 may use SQL Server Desktop, included with the SQL Server Developer edition, to build MSDE solutions on the Windows 95 and Windows 98 development.
Although MSDE is not part of Microsoft Office 2000 or Microsoft Access 2000 standalone setup, you can install MSDE from the Office 2000 CD-ROM by running Setupsql.exe located in the \Sql\x86\Setup folder.
The Microsoft Office 2000 version of MSDE also contains additional help files that are located in the \Windows\Help folder, the SQL Server Enterprise Manager Help (entmgr.chm), and the Replication Wizard Help (replwiz.chm), which you can use with the SQL Server database utilities available from the Access Tools menu.
Once you install MSDE on your computer, on Microsoft Windows 95 or later, you may need to start the SQL Server Service Manager (double-click the MSSQLServer icon on the task bar and click Start/Continue). MSDE starts automatically on Windows NT 4.0 or later; you may want to automatically start MSDE on Windows 95 or later by selecting the Auto-Start service when OS starts check box on the SQL Server Service Manager window.
Access 2000 supports MSDE through new Access Project files. Projects in Access 2000 are different from linked databases. Project files allow for control of SQL Server and MSDE features that are not supported by linked tables in Jet databases.
For SQL Server users, Office 2000 solutions using MSDE databases can be identified in SQL Server's Enterprise Manager as the product "SQL Server Office" in the SQL Server Properties window.
Visual Studio 6.0 developers are encouraged to develop SQL Server-compatible applications using SQL Server Developer edition, and then use MSDE as part of the custom application distribution. MSDE for Visual Studio is installed through a command-line InstallShield installation program. This installation program is designed to allow deployment of MSDE solutions to customers and end-users. For example, consider a new, custom desktop or shared database solution created with Visual Basic 6.0 that uses MSDE. In order for the application to function, MSDE must be installed. In creating the installation program for the Visual Basic application, the developer includes an argument to execute the command line MSDE setup. Then, during installation of the custom application, MSDE is installed silently, without requiring any additional steps.
For detailed information on MSDE for Visual Studio installations, see How to Install the Microsoft Data Engine (MSDE) from the Command Line in the MSDE for Visual Studio 6.0 Technical FAQ at http://msdn.microsoft.com/vstudio/msde/techfaq.asp.
MSDE solutions are usually created in one of two ways: either a new database is created, or an existing Jet database is converted into an MSDE solution. The first part of this section will walk through the basics of migrating an existing Jet database to MSDE using Access 2000 and the Access Upsizing Wizard. It will also explain how to install a completed MSDE sample database. The second part of this section will walk through the creation of a new MSDE database solution using Access 2000.
Note While we use Access 2000 in our example, any tool capable of creating a SQL Server database, such as the SQL Server 7.0 Enterprise Manager or Microsoft Visual InterDev, can be used to create an MSDE solution. It is recommended that Visual Studio 6.0 developers use the included SQL Server Developer edition to create SQL Server-compatible databases, and then deploy the custom solution with the MSDE redistributable engine.
The following section walks through the steps involved in moving an Access Jet database application to an MSDE solution. We use the Northwind Traders sample database that is distributed with Access.
Access 2000 includes an upsizing wizard designed to convert Access Jet databases into SQL Server databases. Because MSDE is compatible with SQL Server 7.0, we can use the same migration wizard to convert a Jet database into an MSDE solution. If, at a later time, we need to convert the database to SQL Server 7.0, the MSDE solution can be converted directly.
It is possible to migrate to MSDE without updating an existing Access database file. Even so, it is always a good idea to back up an existing Access Jet database before using the migration wizard. The simplest way to accomplish this is to make a copy of the Access database file (.mdb). Back up this file to the appropriate backup media.
This section assumes that the developer has successfully installed MSDE on their desktop. If you have not installed MSDE, see the "Installing MSDE" section of this paper.
To create the client/server database:
Note The MSSQLServer service must be started on the target MSDE for the Upsizing Wizard to function. MSSQLServer services can be started and stopped with the SQL Server Service Manager distributed with MSDE.
Note If you choose triggers, the Upsizing Wizard will still create DRI relationships, but the foreign key constraints will be disabled. No foreign key checking will occur.
The upsizing wizard provides two options to migrate your Access application with your database. The first option is to link the new SQL Server tables to your existing application database. This will create links to the new MSDE database tables, and keep the existing Access tables. The existing Access tables are renamed with the suffix "_local."
In our migration of the Northwind database, we do not need to retain the local Access tables, so we will not choose this option.
Note If you choose to link to your existing application, it is important to back up your existing database file before attempting migration as this option will attempt to make updates to your existing database file.
The second option, which is the one we will use to convert Northwind, is to create a new Access client-server application. To do this, you need to specify a new name for the application different from the original name.
Next you need to specify data link properties. Here you can use a user name and password, or if you are working with Windows NT or Windows 2000, you can use Windows 2000 integrated security. In either case, you must specify a user that has CREATE DATABASE privileges on MSDE. Microsoft recommends using the Windows 2000 integrated security, if possible.
The Upsizing Wizard does not convert certain items. Other items are converted, but require verification after the wizard is complete. When using the Upsizing Wizard, it is important to review all changes made to tables, views, forms, and reports. It is equally important to verify the logic used in any stored procedures that are created, and to review any declared table relationships.
Specifically, the Upsizing Wizard uses the following rules when converting databases:
The following Access SQL syntax elements are not supported by MSDE and are not converted. The Upsizing Wizard removes these items from SQL statements:
For more information on converting Access items into SQL Server compatible objects see www.microsoft.com/sql/70/whpprs/migaccwp.htm.
Now that we have looked at the steps required to move an Access Jet database to an Access 2000 MSDE project, it is useful to examine a completed Project. Microsoft Access 2000 provides a complete sample Microsoft Access Project called Northwind Traders (NorthwindCS.adp) that is located in the \Program Files\Microsoft Office\Office\Samples folder. The Project is a good example of what a migrated Access database might look like.
Access can automatically create the completed Northwind MSDE database the first time you open the Northwind Access Project. The Startup form looks for MSDE running on your computer and if it finds it, asks if you want to create the NorthwindCS SQL Server database. If the Startup form doesn't find MSDE running on your computer, it prompts you to enter a server name, user name, and password so you can connect to an MSDE or SQL Server database on the network. In either case, if you answer "Yes," the Northwind sample Access project runs the SQL installation script (NorthwindCS.SQL) located in the \Program Files\Microsoft Office\Office\Samples folder. This SQL script is a sequence of SQL statements that creates the database, data structure, properties, views, and database diagrams, and loads the sample data. Once the script has finished running, the Northwind sample Access Project connects to the newly created database.
You may be creating a new database application and decide to use MSDE as an alternative to Jet. You can create an MSDE database solution using any tool that is capable of creating a SQL Server database. This includes SQL Server's administrative tools, WSQL, and Access 2000. With its wizards and extensive help files, Access 2000 is also a good tool for creating your first MSDE database.
To create an MSDE solution in Access 2000, you create a new Access Project file (.adp) rather than an Access database (.mdb). Microsoft Access Project files connect to MSDE or SQL Server databases through OLE DB component architecture. They do not contain any data. All data is stored in the database server.
This section explains the steps used to create a new database project in Access 2000.
To create MSDE databases using Access 2000:
Note The Database Wizard asks for the name of a SQL Server, but an MSDE Server can be used as well.
Note If you are using Windows NT or Windows 2000, and you would like to use integrated NT security, simply leave the username and password blank. The Database Wizard will attempt to connect to the database using your NT user account.
Follow these steps to create a new MSDE table, stored procedure, and trigger in Access 2000.
To create MSDE database objects using Access 2000:
To create new tables in Access:
To create stored procedures in Access:
To create a new trigger on an MSDE server table using Microsoft Access:
Note It is only possible to manage triggers through Access for SQL Server compatible database tables. The option does not appear for local Access tables.
The simplest way to enforce table relationships in an MSDE database is through the use of Declarative Referential Integrity (DRI). You can define DRI relations for your MSDE tables using an Access database diagram.
To define DRI relations:
In addition to Access, SQL Server tools can be used to create MSDE databases. These tools are included in the evaluation edition of SQL Server available to Visual Studio developers. For more information about creating MSDE database objects through SQL Server tools see the SQL Server Books Online.
There are a number of options for accessing data in an MSDE solution. This section looks at the three of these options.
Access client-server applications are created using Access Projects. These Projects use OLE DB to connect to MSDE databases. Microsoft Access front-ends can be converted into database projects using the Access 2000 Upsizing Wizard.
ActiveX Data Objects (ADO) is an ActiveX programming interface that wraps the OLE DB API. ADO provides data connectivity to Visual Basic applications as well as any application capable of controlling ActiveX in-process servers. ADO can be used to connect a custom Visual Basic application to an MSDE database, access data, and perform administrative functions in the database. For information on how to access MSDE from a Visual Basic application using ADO, see the detailed example available in the MSDE for Visual Studio 6.0 FAQ.
For more information on ADO, see www.microsoft.com/data/ado/prodinfo.htm.
Microsoft Data Access Objects (DAO) is an ActiveX programming interface that wraps the Jet database engine. DAO provides database connectivity to any program that is capable of controlling in-process ActiveX DLLs, such as Microsoft Visual Basic. Many of the data access options in DAO have been improved in ADO. Unless you are accessing a Jet database, you should use ADO.
For information on converting DAO applications to ADO, see
www.microsoft.com/data/ado/adotechinfo/dao2ado.htm.
Applications may grow beyond the MSDE 2-GB data limit. An application's user base may grow beyond the five concurrent user limit in MSDE. If this occurs, migrating the MSDE solution to SQL Server may be necessary. SQL Server is capable of handling hundreds of concurrent users and growing databases to sizes beyond one terabyte (TB). Migrating to SQL Server can fulfill scalability needs without necessitating costly and time-consuming changes to existing applications.
All editions of SQL Server 7.0 can be upgraded to and from an existing MSDE solution. The SQL Server installation program recognizes an MSDE installation and its accompanying data files and transaction logs. This allows MSDE to be upgraded directly to any edition of SQL Server by installing SQL Server.
Another way to upgrade an MSDE solution is to use SQL Server 7.0 Enterprise Manager from a SQL Server machine to connect to the MSDE. Once connected, data can be transferred from the MSDE installation to SQL Server. This is a good method when upgrading to SQL Server using a different machine.
Table 2 shows some of the features, limits, requirements, and upgrade paths of each of the SQL Server technologies.
Table 2. Operating Systems
| SQL Server 7.0 Enterprise |
SQL Server 7.0 |
Small Business Server (SBS) |
SQL Server Desk- top1,3 |
MSDE (Office 2000)3 |
MSDE for Visual Studio 6.03 |
|
| Windows NT Server |
Yes | Yes | Yes | Yes | Yes | Yes |
| Windows 2000 Server |
Yes | Yes | Yes | Yes | Yes | Yes |
| Windows 2000 Professional |
No | No | No | Yes | Yes | Yes |
| Windows NT Worksta- tion |
No | No | No | Yes | Yes | Yes |
| Windows 95 Windows 98 |
No | No | No | Yes2 | Yes2 | Yes2 |
Product Limits
| SQL Server 7.0 Enterprise |
SQL Server 7.0 |
Small Business Server (SBS) |
SQL Server Desk- top1,3 |
MSDE (Office 2000)3 |
MSDE for Visual Studio 6.03 |
|
| DB Storage Limit |
None | None | 10 GB | None | 2 GB | 2 GB |
| Suggested Concur- rent User Limit |
None | None | 30 | 5 | 5 | 5 |
| Processors | Up to 32 | Up to 4 | Up to 4 | 2 | 2 | 2 |
| Replica- tion |
Snapshot, Merge, & Transactional |
Snapshot, Merge, & Transactional |
Snapshot, Merge, & Transactional |
Snapshot, Merge, & Transactional (Subscriber Only) |
Snapshot, Merge, & Transactional (Subscriber Only) |
Merge & Transactional (Subscriber Only) |
Features
| SQL Server 7.0 Enterprise |
SQL Server 7.0 |
Small Business Server (SBS) |
SQL Server Desk- top1,3 |
MSDE (Office 2000)3 |
MSDE for Visual Studio 6.03 |
|
| Cluster- ing |
Yes | No | No | No | No | No |
| OLAP | Yes | Yes | No | No | No | No |
Licensing
| SQL Server 7.0 Enterprise |
SQL Server 7.0 |
Small Business Server (SBS) |
SQL Server Desk- top1,3 |
MSDE (Office 2000)3 |
MSDE for Visual Studio 6.03 |
|
| License | Server License plus CAL |
Server License plus CAL |
Server License plus CAL |
SQL Server; CAL only |
Freely redistrib- utable through Office 2000 Developer 4,5 |
Freely redistrib- utable through Visual Studio 6.04 |
1. SQL Server Desktop is only available as an install option with either SQL Server or SQL Server, Enterprise.
2. Features not available on Windows 95 and Windows 98 include:
Windows NT Authentication
Fiber Mode Scheduling
Asynch IO
Read Ahead
Performance Monitor
Scatter/Gather IO
Named Pipes Server Netlib
Appletalk Server or Client Netlib support
Banyan Vines Server Netlib support
Multiprotocol Server Side encryption support
3. SQL Server Desktop, MSDE only, default features
Autorun/Autoshrink DB
Autoclose unused DBs
4. CAL not required unless communicating with a SQLServer
5. (via Jet) requires either per seat or per server; (via MSDE) requires per seat CAL
This reference section provides information concerning technical considerations involved in selecting a database management system (DBMS). For comparison, information about Jet, MSDE, and SQL Server is included for each of the items discussed.
Capacity refers to the amount of data that a database engine is capable of managing. There are limits to the amount of data every database can contain.
A Microsoft Access Jet database file can contain up to 2 GB. However, because a Jet database can include linked tables in other files, its total size is limited only by available storage capacity.
There is a hard 2-GB limit on data in an MSDE. However, multiple MSDE databases (each with a 2-GB limit) be supported by a single MSDE server.
SQL Server can support over 1 TB of data.
Concurrency refers to the number of users that can simultaneously access the database. Each of the database technologies discussed in this paper is capable of handling two or more concurrent users. The following section describes how many users each technology can support at the same time.
In order to prevent multiple users from editing the same data during transactions, database engines typically implement some form of data locking. This section will also discuss the data locking capabilities of each database system.
Jet can support up to 255 concurrent users, but performance of the file-based architecture can prevent its use for many concurrent users. In general, it is best to use Jet for 10 or fewer concurrent users. Jet 4.0 now supports row-level locking.
MSDE is tuned for best optimum performance at five concurrent users or less. MSDE can support more than five concurrent users both from a technical and licensing standpoint, but Microsoft strongly suggests that the number of concurrent users be five or less in order to enjoy the performance levels available with MSDE. MSDE also supports row-level locking.
SQL Server can support hundreds of concurrent users. Like Jet and MSDE, SQL Server also supports row-level locking.
Backups are a vital part of any database system. Backups insure that data can be recovered in the event of an emergency.
Backing up a Jet database requires simply backing up the database file.
In MSDE, you can backup both data files and transaction logs. Data files contain the data in the database. Transaction logs contain the changes that have occurred to the database over a fixed period of time. A typical backup procedure for a large database would be to back up the transaction log every night and back up the data once a week. In the event of a failure, the most recent database backup is restored. Then, by applying the changes contained and all of the subsequent transaction log files, the database is brought back to the state in which the last nightly transaction log was backed up.
SQL Server handles backups in the same manner as described for MSDE.
Remote administration refers to a user's ability to make changes to a database without sitting at the database server. It's often convenient to administer database servers from remote locations whether the remote location is across the office or across the country. The following section describes remote administration options for each database technology.
Jet databases cannot be remotely administered in the conventional sense. Jet database files, however, can be shared across a network. The network file system, and not the database file, controls this centralized administration.
There are some limitations in remote administration of MSDE. If an MSDE is on a Windows 95 or Windows 98 platform, then the service cannot be started remotely. Once started locally, Windows 95 or Windows 98 MSDE can be administered remotely.
MSDE servers cannot be involved in a multi-server remote administration scenario where the remote administration transaction in question involves more than one MSDE or SQL Server. Thus, it is not possible to perform a transaction involving two remote servers at the same time, if one or more of the servers is an MSDE.
SQL Server supports full remote administration and multi-server administration.
Many systems require data to be replicated from one database to another. The following section describes the replication options with each database technology.
Entire Access Jet databases can be replicated with a simple file copy. Jet databases can also be replicated with any ODBC data source. To replicate data from one Access database to another, Jet databases tables can be linked together.
For more detailed information on Jet 4.0 replication, see the Access 2000 Help files.
A SQL Server client access license (CAL) is required to replicate with any SQL Server database.
SQL Server Desktop requires a CAL to replicate with other SQL Server databases.
Security is an important aspect in many database systems.
Jet databases can be secured with a password. Password security only applies to opening a database. Once a database is open, all of its objects are available to the user.
Additionally, Jet databases support user-level security. Under user-level security, users are required to identify themselves with a user id and then type a password when they start Microsoft Access. Permissions are granted to groups and users to regulate how they are allowed to work with each table, query, form, report, and macro in a database. The users defined in Jet databases are not the same as the integrated Windows 2000 security users.
Databases are useful for storing data and for performing logical operations on data as well. The following section describes some of the objects used to perform logical operations with each database technology.
Jet databases do not support stored procedures; instead, they contain QueryDef objects. Permanent QueryDef objects serve to increase performance by saving a version of a query that has been compiled, or rather, evaluated for use by the Jet database engine. By storing a compiled version of a query, the database engine can execute without having to re-evaluate the statement. The same functionality is accomplished in both the MSDE and SQL Server through the use of stored procedures.
Stored procedures exist as permanently compiled objects in MSDE. Precompiling reduces the overhead required for execution. Stored procedures can accept and return data. Stored procedures can also be used to group complex SQL statements. The contents of stored procedures may be hidden from applications.
Because a stored procedure is a group of Transact-SQL statements, they can contain much more complicated business logic.
Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. When SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied. All of the work is done on the server. This increase in server-side processing can increase performance.
SQL Server stored procedure functionality exactly matches that of MSDE. SQL Server and MSDE stored procedures are fully compatible.
There are defined standards for SQL statements, language, and methods. These standards allow interaction between different database systems. The following section describes how each technology complies with SQL standards.
Jet 4.0 databases are capable of processing ANSI SQL statements, but Jet is not fully compatible with Transact SQL standards. The differences in Transact SQL compliance prevents some Jet objects from directly converting into SQL Server objects.
MSDE is fully compliant with ANSI SQL and Transact SQL guidelines. MSDE is fully compatible with SQL Server 7.0.
SQL Server is fully compliant with ANSI SQL and Transact-SQL guidelines.
Data types are the primary tools for describing data in a database. The following section discusses the difference in how each technology defines its data.
Jet supports property values for columns that are not handled directly in SQL Server and MSDE. These properties include format, captions, and zero length fields.
Some of these features of Jet can be enforced in SQL Server and MSDE through the use of triggers.
In Jet:
Instead of zero-length strings, MSDE supports null values. Not equal to zero, null represents a lack of value. Null values usually indicate data that is unknown, not applicable, or to be added at a later time. The nullability of a column determines whether the rows in the table can contain a null value for that column. If null values are not allowed, the user entering data in the table must enter a value in the column or the table row cannot be accepted into the database.
MSDE is fully compatible with Access identifiers.
Like MSDE above, SQL Server supports the concept of null values. SQL Server 7.0 is also fully compatible with Access identifiers.
Defining relationships between data is an important part of maintaining the integrity of the information in a database.
Engine level cascading updates and deletes provide a method for keeping the relationships between data in different tables aligned. MSDE and SQL Server do not support cascading updates and deletes.
Declarative Referential Integrity (DRI) and Triggers can be used to enforce table relationships.
Declarative Referential Integrity (DRI) and Triggers can be used to enforce table relationships.
MSDE provides an excellent option for developers who need to build desktop or shared database solutions today but who anticipate future increases in system requirements. MSDE provides an inexpensive method for building client server database applications with the highest level of scalability providing an easy migration path to full SQL Server on the same code base. Cost, performance, and the highest level of compatibility with SQL Server makes MSDE an excellent choice for mobile solutions.
This paper identifies the factors involved in selecting a database engine and converting existing Jet-based applications to MSDE. But developers face other concerns as well, such as how to distribute MSDE with custom applications, and how to create MSDE tables through Visual Basic. These and other technical questions are addressed in the MSDE for Visual Studio 6.0 Technical Frequently Asked Questions (FAQ) available on the Microsoft web site at http://msdn.microsoft.com/vstudio/msde/techfaq.asp.
This paper does not explain how to build data-centric applications or how to administer an MSDE database. For more detailed information on building custom client-server database applications, see:
James Fitzgerald is a consultant with Clarity Consulting, Inc. Clarity is a Chicago-based consulting firm that specializes in the design and development of client/server information systems. James has been designing and building custom enterprise client-server database solutions for Fortune 500 clients for the past 5 years. He can be reached at jfitz@claritycon.com or on the Web at www.claritycon.com/.