Beta V Corporation


Developer Education Series


    MSDE—The missing piece

Microsoft Data Engine arrives to fill a long-awaited void in the database architect's toolkit


MSDE—The missing piece

Microsoft Data Engine arrives to fill a long-awaited void in the database architect's toolkit

Introduction

This article discusses another one of the new FLAs (Four-letter-acronyms) created by Microsoft's DAAT (Data Access Acronym Team). Actually, MSDE (which stands for Microsoft Data Engine) is more than just an acronym. It's an important new tool for database development—the one that you and I have been pleading for. 

A quest for the right tools

Up until now, developers responsible for designing and implementing database solutions have quested for the tools needed to correctly prototype databases destined to be implemented on larger, shared systems. As you well know, a prototype database consists of more than just tables and the data they contain. A database management system must also include end users and the permissions assigned to them as well as indexes, referential integrity constraints, business and data integrity rules and much, much more. While a prototype database might have fewer data rows than the production version, it should behave pretty much like the real thing architecturally. If it doesn't, it's like learning to ride a 1600cc Harley Davidson motorcycle by practicing on your kid's bike.

Developers also want solutions that permit them to run a single application against databases ported on both Windows 9x and Windows NT. That is, they want an application that their users can run when connected to the home-office server or to their own version of the database on their laptop. This obviously reduces complexity of the code required to access the data. It also permits development of applications that can easily scale from a couple of users to several hundred or more.

At first, Microsoft Access and the Microsoft Jet Database Engine (Jet) seemed to be the solution to both of these problems. However, many found (sometimes after months of work) that Jet is not really capable of adequately simulating SQL Server. All too often the best features of Jet and SQL Server had to be left behind to build this "universal" solution. To circumvent this limitation, developers went to great lengths to write a single application that attached to SQL Server tables and accessed a local .MDB store directly using Data Access Objects (DAO). To be able to leverage a single code interface, some developers chose to use Remote Data Objects (RDO) or ActiveX Data Objects (ADO) to access both their Jet .MDB database and SQL Server via ODBC—often with disappointing results. Thankfully, this is all in the past.

MSDE Arrives:

Today, developers have another choice. SQL Server is implemented so it can run on Windows 95 or 98 (or Windows 2000) platforms. SQL Server 2000 expands the list to Windows ME but drops Windows 95 support. In reality, it's a little more complicated than that. Because the SQL Server team chose to package MSDE separately, there are actually several “versions” (SKUs) of SQL Server that run on Windows 9x platforms: the SQL Server "Desktop" edition, the SQL Server “Personal” edition and the Microsoft Data Engine (MSDE). When compared to the SQL Server "Standard" edition, they differ in a number of ways that I'll try to explain here.  There is also a “Developer” edition of SQL Server that ships with Visual Studio Enterprise Edition. It’s basically the Standard edition but with a “develop and test only” end-user license agreement (EULA). The Personal Edition is a SKU that comes with the needed tools used to create, manage and support databases. Think of the Personal Edition as MSDE with SQL Enterprise Manager and the other GUI tools.

Feature

MSDE

SQL Server 7.0 / 2K Desktop

SQL Server Standard (or Developer)

Platforms

Win 95 (SS 7.0), Win 98, Win ME,
Win NT WS/Server,
Windows 2000

Win 95 (SS 7.0), Win 98, Win ME,
Win NT WS/Server,
Windows 2000

Windows NT Server (no NT WS, ME or Windows 9x)

Core SQL Server 7.0 functions

Supported

Supported

Supported

OLAP, EQ, FTS

Not implemented

Not implemented

Fully supported

Connectivity

TCP/IP only

TCP/IP only

All protocols

User Limits

Best performance with fewer than 6 users.

Best performance with fewer than 6 users.

None

Database Limits

2 GB database

Multi-terabyte

Multi-terabyte

Supplied via

(7.0 version Web download
Free CD to Visual Studio 6.0 Enterprise (and Professional) Edition tool customers
Office 2000, MSDN.

Included with SQL Server Standard and Enterprise Edition as an installable option,

SQL Server Standard Edition SKU

Tools

None, Engine Only — SQL DMO samples only

All

All

Distribution

Freely distributable

Licensed distribution

Licensed distribution

 

Platforms: Note that the SQL Server Standard Edition no longer runs on NT Workstation. You'll have to install NT Server to host it from now on. However, both the MSDE and the Desktop editions run fine on any of the Windows or NT-based platforms—including Windows 2000. SQL Server 2000 drops support for Windows 95, but adds support for Windows ME.

Core SQL Server functions: Yes, all three versions of SQL Server support the same core SQL Server operations in the same way. That means you'll be able to create and access tables, indexes, stored procedures, rules, triggers, defaults, and all of the other core functions on MSDE or the Desktop version just as you can on the Standard edition. This also means you can really prototype on MSDE or the Desktop edition and expect your design and applications to easily port (when necessary) to the NT-based version.  

OLAP, EQ and FTS support. The ability to analyze and report on data from many sources is necessary for organizations of all sizes. A popular technology for use with data warehouses—online analytical processing (OLAP) is now included in SQL Server OLAP Services, a component of SQL Server 7.0—but these services, along with the English Query (EQ) and Full Text Search (FTS) are not a part of the core MSDE functionality.  That’s because they are implemented with external, separate components.

Connectivity Windows 9x does not support named pipes. This means that you'll have to use TCP/IP protocol to connect to the Windows 9x versions of SQL Server—including MSDE. This also means that you won't be able to depend on integrated (domain-managed) security techniques to get connected.

User/Database Limitations Both the MSDE and Desktop editions include a performance governor. MSDE is designed to function fully and quickly for systems of less than about five users. Yes, you can connect far more than five users to the engine, but as more simultaneous operations are attempted, MSDE purposefully degrades performance. In addition, MSDE cannot create a database larger than 2 GB (but the Desktop edition can). Again, this implementation is designed for prototyping   to keep it in the realm of single/shared solutions, and to provide for smaller database implementations than supported by the NT-based version. See the section below (MSDE Governing), which discusses the technology used to hobble performance and how you can see if the governor has kicked in.

MSDE for Visual Studio 6.0Supplied via: You get MSDE (SQL Server 7.0) when you install (but it doesn’t install automatically) Microsoft Access 2000, Microsoft Office 2000 or if you are a registered owner of Microsoft Visual Studio Professional or Enterprise Editions (or any of their component languages), via the Web (http://msdn.microsoft.com/vstudio/msde/). MSDE Universal and Select customers will also get SQL Server 2000 along with MSDE in the mail. You can also order a 7.0 CD from the same site that includes SQL Server Developer Edition. This is basically the SQL Server Standard edition with a EULA that limits use to development and testing only. But remember that the Developer and Standard editions both require NT Server to run.

Tools: MSDE does not include any tools to help you create databases—it’s just an engine. However, it does include some SQL DMO samples to help you get started. The Personal Edition contains the GUI tools. Once the database is created, SQL Server Standard, Developer or Enterprise Editions includes lots of tools that can be used to add tables, views and stored procedures. The Data View window in Visual Basic 6.0 will take over from here. You can create stored procedures, views and, using the database diagram window, you can create indexes and sophisticated referential integrity constraints. No, you'll have to fall back on the SQL DMO samples to setup new users, groups and assign their permissions.  (Sounds like a revenue opportunity to me…

Distribution: Only the MSDE version is freely distributable as long as your application uses Microsoft—Access, Office, or Visual Studio tools—just like Jet. Read the fine print (RTFP) in your End-User License Agreement (EULA).

 

I thought it was strange that the samples did not include Visual Basic code to perform these basic DBMS operations. To make it easier on you developers, I wrote a sample Visual Basic application that creates and deletes databases on a selected MSDE server using appropriate TSQL statements. I also added a section of code that let’s you execute any TSQL statement you choose—just like ISQL. The code for this application is attached to this article. I’m writing a new article on SQLDMO that should appear in June 2001 in SQL Server Magazine.

 

Tip: MSDE (SQL Server 7.0) installs a different version of ADO than you might have installed on your system—it installs ADO 2.1. SP1. However, the latest Visual Basic SP3, IE5a, BackOffice 4.5 and Office 2000 should sync you up with Microsoft Data Access Components 2.1a (GA). Keep in mind that the original MDAC 2.1 was shipped with SQL Sever 7.0 and SQL Server 6.5 SP5, and a small incremented version was shipped with IE 5.0 (this was MDAC 2.1 without drivers or providers). The SQL Server 2000 version installs ADO 2.6.

MSDE Limitations

Remember that while MSDE databases are limited to 2 Gigabytes of data, this limit is per database, not per server, so a single MSDE server can support multiple MSDE databases, each containing up to the 2 Gigabyte limit. If you anticipate that your database is currently or will grow beyond 2 Gigabytes, upgrading to SQL Server Desktop or Standard edition will remove this limit.

SQL Server Desktop has no limit on data, but requires a SQL Server CAL (Client Access License). MSDE is tuned for desktop and shared solutions where there are less than 5 concurrent users accessing the database at any one time. SQL Server Desktop also shares this optimization at five concurrent users or less. If your solution needs to support more than this number of concurrent users, we highly recommend you migrate to SQL Server standard edition running on NT Server or SQL Server Enterprise editions for optimal performance at this higher level of scalability.

MSDE databases cannot request services from an NT-based SQL Server without a "per seat" SQL Server CAL (Client Access License). Check the SQL Server site for more information on SQL Server licensing. This means that in order for a SQL Server standard edition to replicate with an MSDE version, it must be setup to run in "Per Seat" licensing mode.

Because of limitations within Windows 95 and 98, MSDE cannot use Windows NT integrated security features. Also, on Windows 95 and Windows 98, MSDE cannot be started remotely or support some third party network protocols.

Once MSDE is installed, it initializes certain registry settings that can interfere with subsequent SQL Server setup operations. "HKEY_LOCAL_MACHINE \Software\ Microsoft\MSSQLServer\Setup" contains entries for SQLDataRoot and SQLDataPath post installation. After MSDE is installed, subsequent, SQL Server setup attempts can fail because the setup routine believes these registry entries point to a previous installation that does not exist, or an installation of a newer version of SQL Server. To avoid this problem the SQL Data Root and SQL Path registry entries may need to be cleared before a subsequent SQL Server installation is attempted.

MSDE Governing

The SQL Server team came up with new technology to limit the performance of their “free” versions of SQL Server so developers would not be tempted to use MSDE in place of the unbridled versions. They named this technology “Target Benchmark Users” or TBU for short. TBU keeps all MSDE versions in check. Of course, the Standard and Enterprise editions do not activate TBU governing.

Unlike the SQL Server 6.5 thread-based governing used in the past, TBU is not prone to the blocking or other operational artifacts that troubled earlier versions. TBU is “delay-based”. That is, if the number of concurrent threads is greater than the TBU setting, a variable delay is induced to cap performance. The length of time (in milliseconds) to delay the current operation is calculated based on the number of concurrent worker threads. As more operations (threads) are started, more delay occurs. MSDE’s TBU limit is 8. That means that after 8 (plus 6 to account for system threads) active threads are started, a delay is added to each operation—and this delay gets increasingly longer for each additional thread that’s started. TBU imposes a deterministic, gradual throttle on performance. This provides a more natural, less intrusive way to limit performance without unwanted side effects.

TBU does not limit the number of user connections. That is, (almost) any number of connections can be established to MSDE—limited only by your license and system (RAM) resources. However, each of these connections can execute an operation and enable threads—but only when they are “active”. In other words, if a connection is dormant (the operator is at lunch or simply not doing anything), it’s not running a thread on the server and TBU governing is not impacted. You can also execute multiple operations from a single “user”. For example, a single application can establish multiple connections—each creating its own thread. This means a single user can consume all of the TBU threads all by itself. The Visual Basic IDE also makes connections on its own. For example, the Data View window or the Data Environment Designer both create one or more connections at design time. When you use a data source control such as the ADODC or one generated by the Data Object Wizard, additional connections (perhaps several) can be opened and threads started. Clearly, a single application can exhaust the limited number of threads made available by MSDE.

If you suspect a performance problem when using MSDE, you can use a number of techniques to determine if the TBU governor is causing the delay. The simplest way is to count the number of concurrent users—SELECT COUNT (*) FROM SYSPROCESSES WHERE STATUS <> ‘sleeping’ will do the trick. If this returns a value less than 14 (8 + 6), then the delay is likely caused by other factors—not the governor.

Using DBCC

You can also use the DBCC CONCURRENCYVIOLATION[1] utility to display and set parameters related to the governor. In MSDE (Desktop) or Personal Editions of SQL Server, a tally can be maintained of each time the governor kicks in to slow down the server. According to BOL, the governor kicks in when more than 5 “batches” are being executed at the same time. The DBCC utility can be used as follows to control how these counters work:

DBCC CONCURRENCYVIOLATION [ ( DISPLAY | RESET | STARTLOG | STOPLOG ) ]

DISPLAY (default)

Displays the current values of the concurrency violation counters. The counters record how many times more than 5 batches were executed at the same time since logging was started or the counters were last reset. DISPLAY is the default if no option is specified.

RESET

Sets all the concurrency violation counters to zero.

STARTLOG

Enables logging the concurrency violation counters in the SQL Server event log once a minute whenever there are more than 5 concurrent batches.

STOPLOG

Stops the periodic logging of the concurrency violation counters in the SQL Server event log.

 

When you run DBCC CONCURRENCYVIOLATION and the counter values are all 0, you don’t have any usage violations. A non-zero value means how many times you have concurrent worker threads exceeding TBU. The violation number means how many more concurrent threads you have exceeding TBU. If periodic logging of the concurrency violation counters is enabled, DBCC CONCURRENCYVIOLATION returns this result set (message):

Concurrency violations since 2000-02-02 11:03:17.20
1   2   3    4    5   6    7   8   9   10-100   >100
5   3   1    0    0   0    0   0   0      0       0

Concurrency violations will be written to the SQL Server error log.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This message means logging started on February 2nd 2000 about 11 AM. Since then, there were 5 times when the number of concurrent batches were 1 over the limit, 3 times when the number was 2 over the limit and 1 time that the number was 3 over the limit (of 5 batches).

For more detailed information on DBCC, see SQL Server 2000 Books Online (BOL).

MSDE Similarities

You'll discover that MSDE starts, walks and runs just like the SQL Server that you are used to using in virtually all ways. You'll find that you access it using the same DAO, RDO or ADO or ODBC code you used before. MSDE returns the same errors, the same result sets and has the same performance (up to a point) as the "real" SQL Server. You can access the same stored procedures, the same views and have the same triggers fire when rows change. You can use the SQL Server profiler or query optimizer on it—assuming you have these tools from the standard edition. All of this is because MSDE shares most of the same binary code as the NT versions SQL Server. This means you can prototype and easily scale to larger server-based systems without having to reengineer your code.

MSDE Differences

When connecting to MSDE watch out for netlib issues. Consider that Windows 9x platforms (including ME) don’t support named pipes so you’ll have to specify TCP/IP (sockets) client libraries.  

MSDE and Access 2000

If you get a copy of Access 2000 (A2K) you’ll find that it too knows how to use MSDE as its data store. There could be an entire article on its best use, but let’s just include a few pointers here. First, it’s not at all clear how to use MSDE from A2K. You’ll still have to install it manually (as described below). Next, when it comes time to create a new MSDE-based database, A2K will want you to create a new “Project” (File/New/Project (new database) to create a new MSDE database. You can also request A2K to create a new project that points to an existing SQL Server database using the same dialogs. This process creates a new .ADP file that contains all of the Access-specific forms, reports and macros associated with your Access project. No, your data is not kept here—that’s stored in a SQL Server file-based database created for you by MSDE.

A2K also includes dialogs (based on the same tools used in Visual Studio) to create and modify tables, relationships and indexes. However, A2K also includes ways to assign permissions to these objects.

Installing MSDE

Tip: You can't install MSDE simply by clicking on a setup program. You have to run a DOS-based command line and specifically tell the setup program (described below) exactly where the installation response file resides. Yes, I have seen a neat new application that captures this information for you and makes it a lot easier. Watch this space for more details when they become available.

Even if you get Office 2000 or Visual Studio, you still need to install MSDE separately. Assuming you don’t have MSDE installed and you don't have SQL Server Desktop edition installed, you'll really want to follow through this next part, which discusses how to take what you get from the Web or Office 2000 and get it installed on your system.

Start by reading http://msdn.microsoft.com/vstudio/msde/installing.asp. This set of FAQs will walk you through most of the tough parts. Basically, this site has tips on installing and deploying MSDE on your system and on the target platforms where your application runs against an MSDE-based database. Most of the points are summarized and clarified here.

MSDE installs by running an MSDOS command line program MSDEX86 (there's also an Alpha version). That is, you have to run a batch file or interactively work from an MSDOS prompt to pass the needed arguments to the installation routine. This is designed to run quietly so your target users need not know that MSDE is installing. The installation process is driven from an ASCII ".iss" file. To make things simple, the setup toolkit comes with a sample "unattend.iss". This file is rather long and intimidating, but hopefully there won't be too many options you'll want or need to adjust.  Keep in mind that the installation process runs quietly in the background for quite some time—even when you don't ask for "silent" mode. I have heard reports of installations taking upwards of 15 minutes. Your mileage may vary.

Shown below are a couple of the options that make sense to alter:

szDir & szDataDir

The installation response file contains a hard-coded parameter for the SQL Server 7.0  root and SQL Server 7.0 data paths. The default .iss file assumes that your SQL data and root path are both located at “C:\MSSQL7” but it is possible use other locations—just change the szDir and szDataDir entries in the unattend.iss file. If, for example, your local hard drive is not assigned drive letter C, you would need to change your “.iss” file to reflect your drive mappings as shown below:

szDir=D:\MSSQL7

szDataDir=D:\MSSQL7

Tip: These settings make their way into the system registry during MSDE installation and can conflict with future SQL Server Desktop or Standard edition installations. When installing SQL Server on a machine after MSDE has been installed, the SQL Data SQL Root registry entries may need to be cleared before a subsequent SQL Server installation is attempted.

Command Line Switches

*   "-a"

Append any following arguments to the internal command line.

Tip: Without the "-a" the installer won't build the internal "setup.iss" file. Just include it in the command line.

*   "-f1"

Include the response file indicated immediately following this argument. No, you don't surround the string with "<>" like the error message says. You need the quotes.

MSDEX86.exe -a -f1 "unattend.iss"

The fully qualified path must always be included. For example, fully qualified path would be required if you wanted to install MSDE from the distribution CD, but you wanted to use a custom response file saved on a local drive:

MSDEX86.exe -a -f1 "C:\MyFolder\MyCustom.iss"

*   "-s"

Run in silent mode. This option is useful when adding MSDE to an installation script and you want the MSDE setup to occur without any user interaction. This can be confusing when installing MSDE. When installing in silent mode, it is difficult to determine the progress of an installation. The installation starts and stops services that are not visible to the user. Note: even when silent mode is not used, there are periods during the normal installation where no progress bar (or anything else) is displayed. Even the MSDOS prompt says that the installation .EXE is done—it's not.

*   "-SMS"

Prevent network connections from closing before the installation is complete. This is a case sensitive switch, and must be uppercase. This switch is effective for installations originating from Windows NT and Windows 2000 servers over a network. Specifically, it forces the process calling the installation program to wait for the MSDE setup to complete before continuing or exiting.

Operating MSDEX86 (or MSDEAlpha.exe)

To start the MSDE installation program, start an MSDOS session and change the current directory to the path containing both MSDEX86.exe and unattend.iss. Next, type the following at the MSDOS command prompt or from the Start Run dialog:

    MSDEX86.exe -a -f1 "unattend.iss"

You must use a fully qualified path in the command line if the current directory does not contain the .iss file. For example, you can use this different fully qualified path to help the installation routine find the file, such as:

    MSDEX86.exe -a -f1 "C:\temp\ unattend.iss"

A status bar is displayed during part of the installation, but setup continues for a few minutes (depending on the power of your hardware) after the status bar is no longer displayed. On my system it seemed like the MSDEX86 did not reset the hourglass cursor when it was done—but this could have been a fluke. It also consumed about 60 MB of disk space—I installed on drive C:

MSDE installs the SQL Server Service Manager and creates a shortcut under an MSDE program group. This is the only user interface included with MSDE for Visual Studio. The Service Manager is used to start and stop MSDE. The Service Manager also offers the option of having MSDE (MSSQLServer) service start when the operating system starts. By setting MSDE to auto-start, you avoid the need to manually start the database server before using an embedded MSDE application. If you don't opt to have the server start automatically at Startup time, you will have to remember to start it manually before you can access the MSDE server.

MSDE database installed from Visual Studio can be identified in SQL Server’s Enterprise Manager as the product “SQL Server MSDE” in the SQL Server Properties window.

Reviewing the Installation Log File

The MSDE for Visual Studio setup creates a log file if it ever gets started. This file, "setup.log," is located in the windows directory. The following information describes the codes that are recorded in the log file:

If the ResultCode is not 0 or the Completed value is not 1, then the setup has failed.

Possible ResultCode values

Code

Result

  0

Success.

 -1

General error.*

 -2

Invalid mode.

 -3

Required data not found in the Setup.iss file.

 -4

Not enough memory available.

-5

File does not exist.

-6

Cannot write to the response file.

-7

Unable to write to the log file.

-8

Invalid path to the InstallShield Silent response file.

-9

Not a valid list type (string or number).

-10

Data type is invalid.

-11

Unknown error occurred during setup.

-12

Dialog boxes are out of order.

-51

Cannot create the specified folder.

-52

Cannot access the specified file or folder.

-53

Invalid option selected.

 

Note: If the installation is successful, but a reboot is required, the log file will record a return value of –1, (General Error).

How do I install MSDE from Office 2000?

Although MSDE is not part of the Microsoft Office 2000 or Microsoft Access 2000 standalone setup, you can install MSDE from the 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 might 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.

For more information

Check out (http://msdn.microsoft.com/vstudio/msde/) and SQL Server books online as well as the Office 2000 documentation and help files.

 



[1] This DBCC argument used to be called “USAGEGOVERNOR”. Apparently, this has changed for SQL Server 2000.