MSDE and SQL Server

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.