About MSDE

Loading title...

MSDE: The Microsoft Data Engine

What is MSDE

Micosoft's SQL Server database is an excellent mid-level enterprise database system, but it is expensive. The Microsoft Access database engine (known as the 'Jet' engine) is free, but is best used for simple database solutions with few users and with information that is not critical to the business. (Access itself is, of course, not a free product, but the Jet engine is included with Windows and can be downloaded from Microsoft's web site for no charge).

Microsoft released an intermediate product known as the Microsoft Data Engine (or MSDE) that is best described as SQL Server 'lite'. This article will explore why you might want to use MSDE and how to go about it.

Why use MSDE?

The simple answer is because MSDE is free, is more powerful than Access and is 100% compatible with SQL Server. Upgrading an MSDE database to SQL Server is a simple process of moving the database to the SQL Server computer and attaching the database to SQL Server. MSDE is therefore an excellent choice as a database engine for small and medium sized enterprise applications

MSDE has most of the strengths of SQL Server, including robust transaction logging and recoverability, on-line backups and security. MSDE includes the SQL Server data transformation and job scheduling tools for complex data manipulation.

MSDE will run on all the Windows Server platforms and the following Windows workstation platforms: 98, Me, NT Workstation, 2000 Pro, and XP Pro.

MSDE is an excellent choice for low to medium volume web sites and intranet applications.

Limitations of MSDE

The most obvious limitation of MSDE is that it includes no administatrative tools. MSDE can be administered from a SQL Server computer, if you have SQL Server in your organisation. If not, then you have a few alternatives:

create an Access data project and administer from there. You can only administer databases this way. Server level administration tasks, backups and permissions cannot be performed from Access. This is a very easy way to create and develop databases, especially if you are already familiar with Access.
the application can administer MSDE itself. If you are developing the application, then you can install MSDE, create or attach databases, set up permissions and so on as part of the application installation or configuration. MSDE includes the SQL Server DMO COM component.
you can administer MSDE from a variety of development tools, including Microsoft Visual Studio. Other tools that should be suitable (I haven't tested them myself) include products such as Cold Fusion and Macromedia UltraDev.
finally, you can use a tool such as Intexnet's SQL Admn, which can be downloaded from this site. (Coming soon!)

The other limitations of MSDE are:

MSDE is internally throttled to limit performance. This is only an issue if more than 5 users are connected at any one time. Many people have reported larger numbers of users with a minimal performance impact.
MSDE has some limitations regarding replication. For more information on SQL Server replication and MSDE, go to the Microsoft web site.

Installing MSDE

MSDE can be installed from Access 2000 or any version of Office 2000 that includes Access 2000. For more information on installing MSDE, see the Microsoft knowledge base article Q218812 .

MSDE is not automatically installed as part of Access or Office, but must be manually installed. Locate the folder \SQL\x86\Setup on the Office CD number 1 and double click Setupsql.exe to install MSDE.

Conclusions