Microsoft SQL Server 2000 Desktop Engine MSDE

Friday Jul 2nd 2004 by Don Schlichting
Share:

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. With the release of the Microsoft SQL Server 2000 Desktop Engine (MSDE), a true transactional database server can be shipped royalty free.

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 .

4. Operating Systems: Windows ME, XP, 98, 2000, 2003, and NT are valid operating systems to house the Desktop Engine. The complete list of hardware and software requirements is located at http://www.microsoft.com/sql/msde/productinfo/sysreqs.asp .

5. Performance: MSDE is designed for low volume applications. There is a workload governor that will degrade performance if there are more than five concurrent batch workloads. As more workloads are added, the system will continue to slow. For a complete discussion of the workload governor, see http://msdn.microsoft.com/library/?url=/library/en-us/architec/8_ar_sa2_0ciq.asp?frame=true .

Downloads

To get started with the Desktop Engine, download the database from http://www.microsoft.com/sql/msde/downloads/download.asp . Next, download the service pack at http://www.microsoft.com/sql/downloads/2000/sp3.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:

setup /upgradesp sqlrun SECURITYMODE=SQL UPGRADEUSER=sa UPGRADEPWD=myPWD

Once control returns to the command prompt, reboot.

Management

Once the installation is complete, the SQL Server Service Manager will appear in the task bar showing the green SQL Server running symbol. However, that will be the only familiar SQL GUI. There will not be any applications or shortcuts on the menu to help manage MSDE. This seems to be why many of the questions on the MSDE newsgroup appear. Now that the Desktop Engine is installed, how do I use it? A common way to manage MSDE is from a remote machine running the full SQL Server product. In this example, we will assume a desktop with a full SQL Sever install wants to manage the Desktop Engine on some remote server machine. The first step in remote management is to ensure that TCP/IP is running the MSDE server. Navigate to the "Microsoft SQL Server\80\Tools\Binn" directory. From there, execute SVRNETCN.EXE. This will bring up the SQL Server Network Utility. Add TCP/IP to the enabled protocols.

Return to the desktop machine and launch SQL Server Enterprise Manager. We are going to add the MSDE server into the Enterprise Manager, just like any other SQL Server edition. Right click the "SQL Server Group," and select "New SQL Server Registration," then click Next. Enter in your instance name, in my case, the MSDE instance is called "test."

On the next screen, select your Authorization method, and next. Select a Group then finish. There should be a success message:

From here, expand the Group, and your MSDE server will appear. It now can be managed remotely like any other SQL Server version. Query analyzer will also function.

Local Management

There are many third party tools for local management of MSDE. Microsoft also offers a free web based admin tool "SQL Server Web Data Administrator" at http://www.microsoft.com/downloads/details.aspx?FamilyID=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en .

Conclusion

The Microsoft SQL Server Desktop Engine can be an effective, low cost replacement for Access based low volume applications. Remember, it's SQL Server, so either the user will have to install MSDE, or an install script will be needed. MSDE is not a flat file that can be shipped with your application like Access can. In spite of the added installation requirements, MSDE offers true transactions, with the same TSQL language used on every other SQL Server variation.

» See All Articles by Columnist Don Schlichting

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved