Comparing MSDE 1.0 to SQL Server 7.0


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

Latest Articles