Robert Carter, MSDN Technical Writer
Microsoft Corporation
June 2, 1999
Concurrent with Paul Maritz’s keynote address at TechEd 1999, Microsoft released the Microsoft® Data Engine (MSDE) for download by registered users of Microsoft Visual Studio® 6.0 Enterprise or Professional editions. MSDE is also available from any version of Microsoft Office 2000 that includes Microsoft Access.
In this article, I’ll give an overview of what MSDE is, why you might care, put MSDE in perspective relative to Microsoft Jet and Microsoft SQL Server™ 7.0, and present a little sample we developed that we’ll be using this week at the MSDN booth at TechEd.
MSDE is a data engine that is 100 percent compatible with SQL Server 7.0. Like Microsoft Access, you can use MSDE on your desktop machine. With Access 2000, you can use the Access user interface and either MSDE or an updated version of Jet (Jet is installed by default) as your engine. This section introduces MSDE and compares MSDE with Microsoft Jet.
I know the question you’re already asking. Why another data engine? Access, especially with its famed ease of use, is great for development work. When you’re ready to move to production, or your Access-driven Web site is getting too popular, you can upsize to SQL Server. In fact, a little cottage industry in SQL Server upsizing is emerging (in addition to the Access 97 Upsizing Wizard you can download).
Well, MSDE eliminates the need to take that upsizing step. If your code and your databases run on MSDE, they’ll run on SQL Server 7.0. It’s as simple as that.
What’s more, you can distribute MSDE royalty-free if you use Microsoft Office Developer 2000 or Visual Studio 6.0 to demonstrate sample versions of your applications to prospective clients. You can bundle MSDE with sample databases and code in a single package and make them available for download from the Web, CD-ROM, or DVD.
Jet is the database engine that ships with Access, and has been the main engine for this system for the past few years. With Access 2000, Microsoft released version 4.0 of the Jet database engine, which now features row-level locking.
The fundamental difference between Jet and MSDE is in their architecture. Jet is a file-server database management system. All the database information is contained in a single .MDB file. MSDE, like SQL Server, runs as a client-server service, much like a Web server. Although you create a file when you create an MSDE database, that file is really a project file that holds information about the structure of the database that the MSDE engine uses to manage your database.
However, you’re probably more concerned about whether to use MSDE or Jet in your applications. If you’ve already got a bevy of Jet applications going, and don’t want to support another database engine, by all means stick with your existing system. If you find yourself wanting many of the special database features we discuss below, maybe MSDE is worth considering.
The performance picture offers some interesting tradeoffs. If you don’t plan on migrating your MSDE application to SQL Server, Jet will probably support more concurrent users than MSDE. MSDE runs best in an environment of 5 or fewer concurrent users. Performance will degrade as you scale up the number of users. Strictly speaking, MSDE’s performance is a function of server load, not number of users. If the people accessing your MSDE database focus exclusively on a small set of queries or records, performance will degrade more rapidly than if load were spread out among different data elements or functions.
Access can technically support up to 255 users concurrently, but real-world installations should be limited to 20 or fewer users—10 or fewer if you want to play it safe. Database size for MSDE is limited to 2 gigabytes (GB). While Jet is also limited to a 2-GB file size, you can link multiple files together to extend the size limit accordingly.
If you want to step up to an enterprise-level or high-traffic solution, however, MSDE is the ticket. MSDE, like SQL Server, supports more industrial-strength database features such as transaction-logging, stored procedures, triggers, distributed transactions, dynamic backup and restore, and automatic recovery. And if you envision your Web, intranet, or distributed application becoming successful enough to face a lot of traffic a lot of the time, MSDE provides a more seamless upsize path to the big kahuna data engine SQL Server 7.0.
In a bold attempt to kill two birds with one stone, my manager and I built our own mini-project that uses MSDE. We firmly believe samples are good things to have with articles. We also wanted to prototype some ideas for gathering reader feedback we are considering for MSDN. Voila. Synergy, even.
Even better, the selling points I mentioned for MSDE suit our needs perfectly. If we go ahead and implement our prototype, we’ll need it to run on SQL Server (Jet wasn’t designed to handle tens of thousands of hits per day). And because neither Michael nor I could be considered database whizzes, we don’t want to inadvertently use some features of Jet that would be tricky to upsize to SQL Server. Besides, I really like the Access interface, and MSDE lets me stay with it.
So let me show you what we did.
Jet is the default engine installed with Access, so before I could start playing with the MSDE engine, I had to install it. Sadly, there’s no entry for “MSDE” or “data engines” (or even “Jet,” for that matter) in the Index. If you search using the term “MSDE” with the Office Assistant (I like Links, even if she’s a cat), you get five options returned. To get the MSDE information, select the “Work with a Microsoft Access Project” entry. In the help screen that appears, select “Learn about Microsoft Data Engine.” “Installing MSDE” is one of the hyperlinked entries.
Alternately, you could blow off that entire exercise and click on the Setupsql.exe (not Sqlstp.exe) file in the \Sql\x86\Setup folder from the first CD in your Microsoft Office 2000 package. And don’t fret about the fact that the folders and files say “SQL” rather than MSDE. You’re installing MSDE. Trust me.
Once installed, you create an MSDE database by creating a Project. From the Access File menu, select New. In the dialog box that appears, select Project (New Database).
Figure 1. Selecting an MSDE database
Another dialog box will pop up asking you where in the world this new database will reside. Select your preferred database from the drop-down menu (if you’re on a network, you’ll see all the servers you can access).
Figure 2. Assigning your new database to a service
I chose the database I just finished installing, and kept the name MSDE suggested for me. At the end, you’ll be greeted by a variation of the venerable Access database dialog box. You’ll notice a few more options, and a slightly different layout, compared with previous versions of Access.
Figure 3. The new database view
Now, if you’re like me and attract every error known to man or woman, you will not be able to see your local MSDE service the first time you try to connect to it. MSDE, unlike Jet, uses Windows NT® Challenge/Response authentication, so you may need to change your access permissions to allow Access to see MSDE.
So let’s do that. Open the Internet Information Server management console. Select Programs from the Start menu, followed by Windows NT Option Pack 4.0 (or wherever your Web server administrative tools are stored), Personal Web Server (or Internet Information Server) and, finally, Internet Service Manager.
After the Internet Service Manager appears, double-click the Internet Information Server folder if you don’t see your Web server. Once you do, right-click your Web server and select Properties. In the Master Properties section of the dialog box that appears, press the Edit button next to the drop-down menu (make sure the WWW Service is selected).
Figure 4. Activating the Properties dialog box of a Web server
Select the Directory Security tab. In the dialog box that appears, make sure that the Allow Anonymous Access checkbox is checked, and then select the Edit button. In the (final) dialog box that appears next, enter the same user name and password you use when you log on to your computer in the morning.
Figure 5. Preparing to edit the anonymous access account
Whew. Sorry for all the knees-bent-running-about. But if you’re new to NT-based database services like MSDE and SQL Server, I might have just saved you a lot of head-scratching. Back to our regularly-scheduled article...
Once you’ve completed registering your local MSDE service, you’re back at the database dialog box familiar to Access users (albeit with a different look).
Figure 6. Default database view in Access
Let’s create a table. Double-click the “Create table in Design view” icon. A blank table will appear. The figure below shows the table I created to house the data from our Web-based form.
Figure 7. Design view of Responses table
Check out all the categories you can play with, especially when compared with the old Design view of Access 97 or 2000 using the Jet database engine. Four columns are devoted to data types, and the remainder deal with how MSDE handles the fields. We’ll talk about a couple of those features in the next section.
After tinkering with Access and my Web page a little, I discovered a couple of neat features I immediately applied to our little Web project. The first isn’t anything new to Jet database engine users. I wanted to automatically create an index for my database, so I created a field named “id” as an integer. I checked the Identity checkbox, set the Identity Seed value to 1, and the Identity Increment to 1. With these settings, the first time I create a record, the “id” field will be assigned a value of 1; the next time, “id” will be assigned 2, and so on. A similar feature—AutoNumber—is available with Jet, although as far as I know Jet does not give you the ability to assign the numbering scheme.
The next feature I stumbled on was the Default value column. Several of the form elements I include on my Web form are checkboxes. The fields that store the state of the checkbox when the form is submitted are of datatype smallint. In the event visitors fill out my form and don’t check some of those checkboxes, when the record is created MSDE will assign the unchecked checkboxes the value of 0. Nice.
I should let you know that I chose the smallint instead of the bit datatype against the advice of the help documents. Although logically a bit datatype would be more appropriate, I couldn’t get it to work with either of the methods I describe below.
The form we created had four form elements: checkboxes, radio buttons, text boxes, and a submit button. When a visitor fills out our form and presses the Submit button, we want our database to automatically create a new record that contains all the data on the form.
Because we had already created the form, I chose to implement my solution using Active Server Pages and ActiveX® Data Objects technologies. Simply put, I call my ASP page using the ACTION attribute of the FORM tag.
<FORM NAME="articlequest" ACTION="submitanswers.asp" METHOD=POST>
When the user clicks the Submit button, submitanswers.asp is called. submitanswers.asp uses ADO and Microsoft Visual Basic® Scripting Edition to cycle through all the form elements that were passed, assign them to a like-named field in our table, and then update the table with an update query.
sql = "SELECT * FROM responses "
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
rs.AddNew
For Each Item in Request.Form
rs(Item).Value=Request.Form(Item)
Next
rs.Update
For this to work, I had to make sure all my form elements had the appropriate NAME and VALUE attributes. The names had to match the names of the fields in my table, and the values had to be of the same type. Thus, my INPUT element declaration would look like this:
<TD COLSPAN=2><INPUT TYPE=checkbox NAME="q1a" VALUE="1">MSDN Online Start Page</INPUT></TD>The definitive collection of articles, white papers, and code samples for MSDE is available from the MSDE section of the Visual Studio site, http://msdn.microsoft.com/vstudio/msde/default.asp.
Overall, in my first encounter with MSDE I was impressed. Although there were a couple of distractions I got bogged down in, the power and flexibility MSDE offers are impressive. Were I charged with building an application that had to eventually face up to hundreds or thousands of users a day, it would probably be my development and testing choice, especially given my Access development bias.