Featured Database Articles

Microsoft SQL Server 2000 Desktop Engine MSDE

This article will explain what the Microsoft Desktop Engine is, as well as when and why to
use it.

Introduction

Microsoft application developers have often been faced with
the decision of using a database engine they liked, versus an engine that was
affordable to ship with the completed product. In many cases, the price point
wins, and the database standards are changed to accommodate the lower priced
database. However, with the release of the Microsoft SQL Server 2000 Desktop
Engine (MSDE), a true transactional database server can be shipped royalty
free. Think of MSDE as one more version of SQL Server. Now we have Developer,
Enterprise, Standard and Desktop Engine versions. Each version has different
requirements and features, but your database code can run unchanged on all
versions.

Who Should Use MSDE?

The Microsoft Desktop Engine is targeted towards SQL Server developers,
with low volume, or mobile applications. In the past, these applications may
have been developed on MS Access or Dbase, because of the royalty free or
inexpensive distribution rights offered. In some cases, a developer may have
two versions of the same product. One full version developed on SQL Server,
and another, light version on Access. Because all MSDE objects are the same as
their SQL Standard Server version counter parts, you can now have a light
version, and full version, sharing identical database code.

Benefits

MSDE offers many of the same features as SQL Server,
including support for Replication, DTS, OSQL and BCP. And of course, probably
the most compelling benefit of MSDE, royalty - free distribution.

Requirements and Restrictions

There are a number of requirements to be met in order for Desktop
Engine to be a good fit for your application.

1. Interface: The MSDE does not include any user interface.
It works the same way as the other version SQL Server. Interaction with the
Desktop Engine must be done through an application developed in Visual Studio,
or some other similar development environment. There is no Access style GUI.

2. Management Tools: SQL Server tools are not shipped with
the MSDE. Enterprise Manager and Query Analyzer are not included. Any tools
must be developed or obtained from a third party.

3. Redistribution: In order to redistribute MSDE, you must own a
Microsoft product that conveys the appropriate rights. This product list
includes SQL Server 2000, Visual Studio Dot Net, ASP.NET Web Matrix Tool,
Microsoft Office XP Developer Edition or a subscription to MSDN. For a
complete list, see the Microsoft web page "Rights to Use and Redistribute
MSDE 2000" at http://www.microsoft.com/sql/msde/howtobuy/msderights.asp
.

Installation

Unlike most Microsoft applications, you cannot double click
the MSDE setup.exe file to install. Doing so will start the Windows Installer,
but the install may error out and fail. Instead, change to a command prompt,
and run setup with the appropriate switches. If you want MSDE to run in mixed
mode, accepting both SQL and Windows security, then the switch "SECURITYMODE=SQL"
is required. When running in mixed mode, a blank sa password is not allowed,
the switch "SAPWD="AStrongPassword" must be included.
Optionally, an instance name can be specified using INSTANCENAME="InstanceName."
If omitted, a default SQL instance is created. If MSDE should accept
connections from other machines on the network, there is a switch for enabling
or disabling network protocols. "DISABLENETWORKPROTOCOLS=n", with a
value of either 1 for No, or 0 for yes. Default data and target directories
can be specified using DATADIR="data_folder_path" and TARGETDIR="executable_folder_path".
A typical install may look like:

setup sapwd="myPWD" DISABLENETWORKPROTOCOLS=0 securitymode=sql

Service Pack

After the database installation completes, apply the
MSDE service pack. The pack should be installed from the command line like the
database engine. If network protocols were disabled on the database install,
enable them now to allow the service pack to install. Use the same DISABLENETWORKPROTOCOLS switch. In our case, we are
upgrading an existing MSDE installation, so the switch "/upgradesp sqlrun"
is required. We will also specify a login and password to be used for upgrade with
"UPGRADEUSER" and "UPGRADEPWD." In this example, the
complete command will be: