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 95 (SS 7.0), Win 98, Win ME, |
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 |
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.
Supplied 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.