Comparing MSDE 1.0 to SQL Server 7.0

July 11, 2001

Introduction
The comparison

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 terms of 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 it has some restrictions and is intended for single users or for small workgroups with up to five computers. You can use MSDE as a back-end database in a workgroup with more than five computers, but it tends to be very slow in such environments.

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 or compatible).
  • 32MB RAM (minimum), 64MB RAM or 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 or 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, the Microsoft Data Engine doesn't 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. These restrictions include:

  • Supports only up to 2Gb RAM.
  • 2GB database size limit.
  • Five concurrent users or less (recommended).
  • Supports up to 2 CPU on Windows NT or Windows 2000.
  • No publishing for transaction replication.
  • No Database Server Failover Support.
  • No Full-text search.
  • No GUI interface (i.e. 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 is 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 is no longer sold, as Microsoft now promotes SQL Server 2000 as its 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." These rights enable you to buy SQL Server 2000 licenses, but install and use the previous version (i.e. v7.x) for a reasonable period of time.

See this link about SQL Server 2000 Pricing 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.

Some useful tips

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 install accompanying 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 the MSDE that came with the Office 2000 developer edition. You'll need to 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 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 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 the following article by Danny J. Lesandrini for more information: Create Your Own Administration Tool for MSDE

Literature

  1. SQL Server Books Online

  2. This page is all about the MSDE. http://msdn.microsoft.com/vstudio/msde/

  3. MSDE for Visual Studio 6.0 General FAQ http://msdn.microsoft.com/vstudio/msde/genfaq.asp

  4. Microsoft Data Engine (MSDE) for Microsoft Visual Studio 6.0: An Alternative to Jet for Building Desktop and Shared Solutions http://msdn.microsoft.com/library/en-us/dnmsde/html/msdeforvs.asp

  5. About Microsoft Data Engine (MSDE) http://msdn.microsoft.com/library/en-us/off2000/html/acconAboutMSDE.asp

  6. Build Client/Server Databases With MSDE http://msdn.microsoft.com/library/en-us/dnvbpj99/html/rd1099.asp








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers