
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

SQL Server 7.0 vs MSDE 1.0

Sergey A. Vartanyan
svartany@yahoo.com

Introduction
The comparison
Hardware and Software Requirements
MSDE Restrictions
Pricing and Licensing
Upgrading from MSDE to SQL Server
Creating and designing MSDE databases
Literature

Introduction

Often people in newsgroups ask about some comparison of MSDE and SQL Server 7.0. In this article, I want to describe the differences between the Microsoft Data Engine and SQL Server 7.0 in cost, licensing, hardware and software requirements and restrictions.

The comparison

MSDE stands for the Microsoft Data Engine. Microsoft Data Engine is a client/server data engine that is compatible with SQL Server 7.0, but has some restrictions and is intended for a single user or for a small workgroup with up to five computers. You can use MSDE as a back-end database in a workgroup with more than five computers, but it can be very slowly.

Hardware and Software Requirements

To install SQL Server 7.0, you should have the following hardware:

Alpha AXP, Intel or compatible platform.
Pentium 166 MHz or higher (Pentium Pro, Pentium II and compatible).
32MB RAM (minimum), 64MB RAM and more (recommended).
180MB (full installation), 170MB (typical), 65MB (minimum), 90MB (management tools only) plus 50MB for OLAP Services and 12MB for the English Query.

SQL Server 7.0 can work under Windows 95, Windows 95 OSR2, Windows 98, Windows NT Server/Workstation 4.0 with Service Pack 4 and Windows 2000 (all editions).

SQL Server 7.0 also requires Internet Explorer 4.01 with Service Pack 1 or later, or Internet Explorer 5.0 or later.

To install MSDE 1.0, you should have the following hardware:

Alpha AXP, Intel or compatible platform.

Pentium 166 MHz or higher (Pentium Pro, Pentium II and compatible).

32MB RAM.

71-79MB hard disk space.

The MSDE 1.0 works under Windows 95, Windows 95 OSR2, Windows 98, Windows NT Server/Workstation 4.0 with Service Pack 4 and Windows 2000 (all editions).

Unlike SQL Server 7.0, Microsoft Data Engine does not require Internet Explorer.

MSDE Restrictions

Because Microsoft Data Engine is intended for a single user or for a small workgroup with up to five computers, it has some restrictions in comparison with SQL Server 7.0.
There are:

Supports up to 2Gb RAM.
2GB database size limit.
Five concurrent users or less is recommended.
Supports up to 2 CPU on Windows NT or Windows 2000 box.
No publishing for transaction replication.
No Database Server Failover Support.
No Full-text search.
No GUI interface (there are no SQL Server Enterprise Manager, SQL Server Profiler, Query Analyzer, Database Upgrade Wizard, Index Tuning Wizard, Import and Export Wizards, and so on).
No OLAP.
No English Query.
No SQL Books Online.

Pricing and Licensing

Microsoft Data Engine does not sold as a separate product. MSDE is available to any licensed customer of Office 2000 Professional, Office 2000 Premium edition, or licensed customer of any Visual Studio 6.0 edition or any Professional or Enterprise edition of any Visual Studio 6.0 language tool (such as Visual Basic 6.0, Visual C++ 6.0, Visual InterDev 6.0, Visual J++ 6.0, or Visual FoxPro 6.0).

Microsoft SQL Server 7.0 does not sold now. Now, Microsoft promotes SQL Server 2000 as back-end database server, but if you have existing solutions on SQL Server 7.0, you may still acquire additional licenses by taking advantage of the SQL Server 2000 "downgrade rights". It enables to buy SQL Server 2000 licenses, but install and use the previous version for a reasonable period of time.

See this link about SQL Server 2000 Price and Licensing:
http://www.microsoft.com/sql/howtobuy/pricing/default.asp

Unlike SQL Server 7.0, MSDE solutions can be distributed royalty-free, i.e. you don't need to buy Client Access License, if your application uses Microsoft Data Engine as a back-end.

Upgrading from MSDE to SQL Server

MSDE data engines are the same as SQL Server 7.0 data engines. So, all database objects created in MSDE (such as tables, views, stored procedures and so on) will operate without any modifications in a SQL Server 7.0 database.

There are two ways to upgrade an MSDE solution to SQL Server 7.0:

You can upgrade an MSDE to SQL Server 7.0 by installing SQL Server. Just run SQL Server installation, and the installation program will recognize an MSDE installation and will accompany data files and transaction logs.

You can upgrade an MSDE to SQL Server 7.0 by using SQL Server 7.0 Enterprise Manager from a SQL Server machine to connect to the MSDE. Once connected, MSDE databases can be transferred to SQL Server (by using sp_detach_db and sp_attach_db stored procedures, by using backup/restore or by transfering data with DTS). This method is used when upgrading to SQL Server using a different machine.

When you upgrade MSDE to SQL Server 7.0, you can receive the following error:

You cannot install a version which is older (7.00.623) than the
version on your machine (7.00.677). Uninstall the older version.

This error occurs when you tried to upgrade MSDE that came with the Office 2000 developer edition. You should edit some registry entries, in this case.

You should amend

HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
to have a value of 7.00.623.

See this link for more details:
FIX: Upgrade to SQL Server Fails When MSDE Installed from MOD

Creating and designing MSDE databases

Microsoft Data Engine does not have its own GUI interface for database design. MSDE is shipped with only osql utility, but it's difficult to create and manage databases from the command prompt.

So, you can create and manage MSDE databases via the Access 2000 User Interface or via the Visual Studio development environment.

If you are a licensed customer of any Visual Studio 6.0 edition or any Professional or Enterprise edition of any Visual Studio 6.0 language tool (such as Visual Basic 6.0, Visual C++ 6.0, Visual InterDev 6.0, Visual J++ 6.0, or Visual FoxPro 6.0), you can have SQL Server 7.0 Developer Edition to manage MSDE databases.

The SQL Server 7.0 Developer Edition is shipped with MSDE for Visual Studio 6.0, and is provided on the CDs that can be ordered from the http://msdn.microsoft.com/vstudio/msde/

With SQL Server 7.0 Developer Edition, you can manage MSDE databases by using Enterprise Manager.

You can create you own administration tool for MSDE, because Microsoft provides SQL Distributed Management Objects (SQL-DMO) and SQL Namespace (SQL-NS) COM libraries with Microsoft Data Engine.
See this article by Danny J. Lesandrini:
Create Your Own Administration Tool for MSDE

Literature

1. SQL Server Books Online

2. This page is all about the MSDE.

3. MSDE for Visual Studio 6.0 General FAQ

4. An Alternative to Jet for Building Desktop and Shared Solutions

5. About Microsoft Data Engine (MSDE)

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc