Microsoft Data Engine
This column appeared in the November 1999 edition of
EXE. It discusses MSDE, a scaled-down version of SQL Server.
Last month I wrote about using SQL-DMO to control SQL Server
from Visual Basic. Staying within the general vicinity of SQL Server I’m
now going to discuss the Microsoft Data Engine (MSDE). Up to now the majority
of Visual Basic developers have used Jet for small database systems, and a
full-blown relational database product such as SQL Server or Oracle for the
larger departmental/enterprise systems. To date all versions of Microsoft
Access have been based around Jet. Now it sometimes happens that applications
that were designed and implemented for relatively small user bases are seen to
be much more useful than was originally predicted, and so the number of
potential users suddenly increases beyond the practical ability of the database
engine. When this happens it is necessary to scale the database implementation
from Access up to, as far as Microsoft is concerned, SQL Server. To facilitate
this process an upsizing wizard is provided to ease the transition, but it
doesn’t overcome the fact that there are some differences between Access
and SQL Server that could require a fair bit of manual intervention to
complete.
MSDE is designed for use in cases where you would previously
have used Jet, but this time you won’t have to face any conversion issues
if you need to scale up the application. MSDE is, in fact, a significant subset
of the actual SQL Server product. This means that any application can be scaled
upwards from a small workgroup to an enterprise-wide scenario simply by moving
the database over to the full-blown SQL Server product. No modifications are
required, in fact if you are happy to use the same machine then you just need
to install SQL Server; the setup program will detect the existing MSDE
installation and incorporate the databases into its own environment. The first
run of this setup program will, however, just perform the necessary conversion.
You will find that you need to run the setup program a second time to install
the various tools.
Installation
MSDE requires about 55MB of disk space, and it is recommended that the target
machine has a minimum of 64MB of RAM. It will run on Windows NT 4 if service
pack 4 or higher has been installed, and of course it will run on Windows 2000.
It will also run on Windows 95 and Windows 98, but some advanced features (such
as read ahead buffering and performance monitoring) will be disabled due to the
lack of underlying support from the operating system.
If you are an MSDN subscriber then, at the time of writing at
least, disk 7 of the Office Test Platform and Development Tools collection will
give you the installation files. Alternately it can also be found on disk 1 of
all versions of the Office 2000 product that include Access; run the
setupsql.exe application from the \sql\x86 directory. It also can be downloaded
by registered Visual Studio users from the http://msdn.microsoft.com site. When
you run these install programs (the MSDN subscriber disk version will
auto-start) you will find that the SQL Server Developer Edition will actually
be loaded instead. Microsoft recommend that developers use this environment to
work against, presumably because of the associated tools, and then detach the
database when you have finished. This is achieved by running the sp_detach_db
stored procedure from Query Analyser.
Once you come to put together the setup program for your
application you will need to include the MSDE runtime application along with
your other files. Depending upon your target environment this is either called
msdex86.exe or msdealpha.exe. This executable needs to be run from the command
line, along with various parameters to provide such features as “silent
running”. If you use InstallShield to create your setup programs then a
suitable unattended response file is provided. However, if you use the Package
and Deployment Wizard then you will need to modify the provided Visual Studio
setup1 project files because in it’s native form PDW does not cater for
issuing command line instructions such as this.
One interesting feature is that an existing SQL Server 7.0
installation can also be scaled down to an MSDE version, which can be useful in
such circumstances as when you are creating a demo version of your system.
Relationship with SQL Server
MSDE is SQL Server 7.0, but in a scaled down version. This is
most visibly reinforced by the fact that the SQL Server service pack (which is
now available for version 7.0, by the way) can be applied to an MSDE
installation too. There are, however, some deliberate differences in the
implementation. MSDE can only support databases up to 2GB, although it does
work happily with multiple databases; SQL Server on the other hand will support
databases of up to 1TB. Another difference is the number of users that each
implementation is targeted for: SQL Server will cater for hundreds of users,
whereas MSDE (and specifically the Developer Edition of SQL Server) is tuned
– some might say nobbled – for up to five users. The big difference
here, of course, is the cost. Any licensed user of either Visual Studio or the
relevant versions of Office 2000 can redistribute an MSDE-based application,
whereas a licence for SQL Server 7.0 can cost hundreds of pounds depending upon
the configuration selected. Note that the same OLE DB and ODBC drivers can be
used for both technologies. Finally, it has been limited to only make use of up
to two processors regardless of the number that are actually available.
Although there are some deliberate features found within SQL
Server that are excluded from MSDE, multiple transaction processes across
different servers for example, it is possible for an MSDE application to
communicate with a separate SQL Server. In order to make this work the MSDE
machine will need to have a per seat SQL Server CAL (Client Access License).
Comparing Jet to MSDE
MSDE is a good choice for new applications that are being
targeted at machines with a typical corporate user specification. As with its
grown up version all database operations are performed on the server side,
giving a performance improvement over Jet. The only time that Jet really scores
over MSDE is if you are using machines with somewhat older specifications (and
therefore less resources). Jet is designed for a smaller memory footprint and
so will score over MSDE under these circumstances. There is also a difference
in Microsoft’s recommendations for the numbers of users that can be
supported by each technology. As I said, MSDE is tuned for up to five users.
Jet, on the other hand, can technically support up to 255 users, although in
practice this figure falls to about 20 or less, depending upon the complexity
of the system, the network throughput, and the speed of the hardware in
general.
Another difference is that of stored procedures. Jet databases
use QueryDef objects, whereas MSDE and SQL Server use compiled stored
procedures. Stored procedures are based upon Transact-SQL statements and are
capable of encapsulating more complex business logic than QueryDefs. Harking
back to the issue of the ease of upgradability, QueryDefs must still be
manually recoded as stored procedures after the Access Upsizing Wizard has been
run.
By the way, I don’t want to give the impression that Jet
has now been retired. On the contrary Jet is still the default database engine
within Access 2000. The primary benefit of MSDE is that it offers a complete
and direct scalability from a basic workgroup up to an enterprise-wide
installation, whereas Jet doesn’t.
Creating Databases and Tables
Databases can be created in one of three ways, depending upon
what you feel the most comfortable with:
-
Using the SQL Server 7.0 Enterprise
manager. Select the databases folder, click on the Action menu,
and choose New Database. The database can be created by completing
the resulting form. This approach gives you full control over
the parameters of the new database, such as the initial size,
growth amount, and location.
-
Using Access 2000. The default type of database is that of a Jet-style MDB.
However, choosing a “Project” type from the File, New dialog will
create the required MSDE database. This options displays an additional form
that asks which SQL Server service will host the database. The upshot of this
is that the task of creating the database is actually passed to SQL Server
anyway. Creating a database in this way will give you no control over its
initial properties, other than the underlying SQL Server name. By this I mean
that the database at the SQL Server level can be different than the name of the
Access Project. The new database will initially be the same size as the model
database, will grow by 10%, will be unrestricted in size, and will reside in
the SQL Server installation’s Data folder. These options can subsequently
be altered from the Enterprise Manager.
-
From Visual Basic code. This task can be achieved by using the SQL-DMO library,
which works equally well for SQL Server and MSDE. Listing 1 shows a database
being created in this manner.
So anyway…
So what do I think of MSDE? Well, speaking as somebody who is
generally more at home with SQL Server than Jet I’m frankly delighted, but
then I would be. The ability to include more sophisticated features such as
stored procedures and triggers into the full range of applications that I
develop is something that I find most appealing. I do know, however, that there
are many developers out there who prefer the cosiness of a standalone MDB file
and who will regard the installation onto a client machine of a cut-down
version of SQL Server as something of an overkill for some applications, and
they would probably be right. However, Jet is still there and will be for a
while yet I would guess. I also know that developers who are itching to get
their hands on SQL Server 7.0 but are still tied by their company to using
version 6.5 will spot the opportunity to get some real experience with the
product while not necessarily going against the corporate platform. One
important difference however will be the increased need for training. Jet is
really a component technology which you can largely pick it up as you go along.
SQL Server, on the other hand, is much more sophisticated and really does
benefit from a formal training course.
The fact that MSDE is free to registered
users of Visual Studio or Office 2000 isn’t quite as generous
as it sounds. Jet is better able to support more concurrent users
than MSDE, so this particular ceiling has been lowered. One can
sense the canny minds of the marketing arm of Microsoft at work
here because the decision as to which product to use when scaling
up an application will now always be SQL Server, whereas before
a redevelopment might well have revolved around whatever tool
the relational database teams used as their standard. However
I think that it offers more than it restricts and so I will undoubtedly
be using it where relevant. At the end of the day Microsoft have
now delivered a consistent and compatible database environment
for applications of all sizes.
