MSDE and SQL Server

A lot of people are confused about the differences between MSDE 2000 and SQL Server 2000, especially about how MSDE differs from SQL Server 2000 Personal Edition. Michael Otey counts seven important ways that MSDE differs from SQL Server 2000.

Michael Otey

March 16, 2003

2 Min Read
ITPro Today logo in a gray background | ITPro Today

Microsoft SQL Server Desktop Engine (MSDE) is a local database engine that's based on the same core database engine as SQL Server. Developing applications for MSDE is the same as developing applications for SQL Server, and this similarity causes confusion—especially about the differences between MSDE and SQL Server 2000 Personal Edition. Here are seven important differences between MSDE 2000 and SQL Server 2000.

7. MSDE Isn't Shipped Alone


Microsoft provides MSDE as part of the following products: all SQL Server 2000 editions, Visual Studio .NET, ASP.NET Web Matrix, Microsoft Office XP Developer Edition, and Microsoft Developer Network (MSDN) Universal and Enterprise editions.

6. MSDE Is Royalty Free


Although you have to pay for the product it comes with, MSDE is free. You don't need any Client Access Licenses (CALs) for applications that use the MSDE database, unless it's connecting to another SQL Server system and acting as a client.

5. MSDE Has Database Restrictions


MSDE databases are limited to 2GB each. However, this restriction isn't the maximum storage size that the MSDE engine can support. MSDE can support multiple databases as long as each database is smaller than 2GB.

4. MSDE Has Hardware Restrictions


Like SQL Server 2000 Personal Edition, MSDE supports systems with a maximum of two processors. In addition, MSDE can access a maximum of 2GB of RAM.

3. MSDE Has No Graphical Management Tools


The only tool that Microsoft provides to manage MSDE is the character-based osql utility. MSDE has no Query Analyzer, no Data Transformation Services (DTS) Designer, no Analysis Services, and no Books Online (BOL). But you can download BOL for free at http://www.microsoft.com/sql, and you can manage MSDE with an existing instance of the SQL Server tools, exactly as you manage any other SQL Server system.

2. MSDE Runs on 32-bit Versions of Windows


Like SQL Server, MSDE supports Windows XP, Windows 2000, and Windows NT. And like SQL Server Personal Edition, MSDE runs on Windows Me and Windows 9x. The limitations you must work with when you run MSDE on a Windows Me or Win9x platform include no support for integrated security, no asynchronous I/O, no read-ahead, and no Performance Monitor counters.

1. MSDE Is Optimized for Five Batch Workloads


MSDE has a managed concurrency workload governor that limits the engine to five concurrent batch workloads for best performance. As you submit more batch workloads, the concurrency governor progressively slows down the system. MSDE doesn't drop these excess workloads but processes them with increasingly degraded performance.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like