Microsoft SQL Server 2000 Desktop Engine MSDE
July 2, 2004
This article will explain what the Microsoft Desktop Engine is, as well as when and why to use it.
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.
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 .
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 .
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
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.