MSDE vs. SQL Server

When Microsoft first introduced MSDE 1.0, it was called Microsoft Data Engine and it shared the same code base as SQL Server 7.0. With the release of SQL Server 2000, Microsoft introduced MSDE 2000 and called it Microsoft SQL Server 2000 Desktop Engine. This article provides information on MSDE 2000 as it compares to SQL Server.

MSDE 2000 is a redistributable version of SQL Server 2000. It is a database engine provided by Microsoft that is based on the core SQL Server technology and supports single- and dual-processor desktop computers. In other words, MSDE 2000 is a scaled down copy of SQL Server. MSDE 2000 was introduced to provide application developers a database engine that is more powerful than the Jet engine and at the same time expandable to SQL Server. It is ideal for client applications requiring an embedded database and websites serving up to 25 concurrent users.

The common technology base shared between SQL Server and MSDE 2000 enables developers to build applications that can scale seamlessly from desktop solutions to multiprocessor enterprise clusters.

As an alternative to the Jet engine, MSDE 2000 provides a cost-effective option for developers who need a database server that can be easily distributed and installed. Because it is fully compatible with SQL Server, developers can easily target both SQL Server and MSDE 2000 with the same application code base. This provides a seamless upgrade path from MSDE 2000 to SQL Server if an application grows beyond the storage and scalability limits of MSDE 2000.

MSDE Licensing

MSDE is not for sale as a separate product. It is available for royalty-free redistribution by vendors under certain MSDE licensing conditions. You don’t need to buy Client Access License if your application uses MSDE as a back-end. Check Microsoft’s website for details on MSDE licensing.

Managing MSDE Databases - SQL Server Enterprise Manager

Unlike SQL Server, MSDE does not have its own tools for database design and management. MSDE ships with the OSQL utility only.

MSDE Database Limitations

MSDE is intended for single user or small workgroup environments. The following are some of the MSDE limitations in comparison with SQL Server:

• No Enterprise Manager
• No Query Analyzer
• No Index Tuning Wizard
• Only 2GB RAM
• Only 2GB database size limit
• Only 2 CPUs
• Only five concurrent batch workloads or 25 concurrent connections for websites
• No Database Server Failover Support
• No Full-text search
• No SQL Server Profiler
• No Import and Export Wizards
• No OLAP
• No English Query
• No SQL Books Online
• No Full or Bulk-Logged recovery model support (only simple)

(This list covers most of the MSDE limitations. There may be a few omitted.)

MSDE Requirements - Hardware

Processor:

Intel Pentium 166 MHz or higher processor

Memory:

Windows XP: 128 megabytes (MB) of RAM
Windows 2000: 64 MB of RAM
All other operating systems: 32 MB of RAM

Hard disk:

44 MB of available hard disk space

MSDE Requirements - Operating Systems

Windows Server 2003, Standard Edition
Windows Server 2003, Enterprise Edition
Windows Server 2003, Datacenter Edition
Windows Server 2003, Web Edition
Windows 2000 Server
Windows 2000 Advanced Server
Windows 2000 Datacenter Server
Windows NT Server 4.0 with Service Pack 5 (SP5) or later
Windows NT Server 4.0, Enterprise Edition, with SP5 or later
Windows NT Workstation 4.0 with SP5 or later
Windows XP Professional
Windows XP Home Edition
Windows 2000 Professional
Windows Millennium Edition
Windows 98

Microsoft Windows 98 does not provide support for the following features:

• Microsoft Windows NT authentication
• Fiber mode scheduling
• Asynchronous I/O
• Read ahead
• Performance Monitor counters
• Scatter/Gather I/O
• Named pipes server network library
• Appletalk server or client network library
• Banyan Vines server network library

Remote MSDE Administration

MSDE 2000 can be administered with third-party MSDE tools both locally and remotely. However, it cannot be remotely administered in a multi-server environment where transactions happen across servers.