MSDE Demystified

Sometimes the best things come in small packages, and MSDE is one of them. MSDE is Microsoft’s mini data-storage engine and query processor that you can freely distribute and embed in other applications, according to the terms of its license agreement. Microsoft supports MSDE under two labels. Confusing? Some customers think so. When the company introduced MSDE with SQL Server 7.0, the acronym stood for Microsoft SQL Server Data Engine. Microsoft rebranded MSDE as Microsoft SQL Server 2000 Desktop Engine when SQL Server 2000 shipped, but Microsoft also calls this version SQL Server 2000 Desktop Edition. (SQL Server 2000 Books Online consistently uses the Desktop Engine taxonomy for the product.) To add to the naming complexity, some users confuse MSDE with the SQL Server 2000 Personal Edition. This week I show you how MSDE differs from the full SQL Server and compare MSDE 2000 with the Personal Edition.

MSDE shares SQL Server 2000 Standard Edition's engine features. However, three traits distinguish MSDE from its parent: A query governor limits database performance when SQL Server executes more than 5 batches concurrently, database size is limited to 2GB, and the engine conspicuously lacks GUI administration tools. MSDE doesn't ship with any administration tools because it's designed to be a hidden data store for another product, and that product needs to provide the necessary maintenance tools. Technically, you can use a standard version of Query Analyzer or Enterprise Manager to interact with MSDE, but legally you can't use the tools that way unless you have a valid client access license (CAL) to use them with another version of SQL Server.

MSDE 2000 has some features in common with SQL Server 2000 Personal Edition but differs in others. The Personal Edition shares the 5-user query limit but isn't restricted to a 2GB database and comes with standard SQL Server administrative tools. You can use Personal Edition on any machine that has a valid license for connecting to SQL Server 2000 Standard or Enterprise Edition. In some cases, you could use either MSDE or Personal Edition. The biggest difference between the two products is that you can distribute MSDE 2000 in applications that you build, but you can't distribute Personal Edition.

Some novice users might wonder whether you could use either MSDE 2000 or the Personal Edition as a low-cost alternative to SQL Server Standard Edition for a small work group. Don't do it! This practice is illegal. You'd also find that performance would suffer because the query governor kicks in when more than 5 users try to run concurrent T-SQL batches.

I hope this brief discussion has cleared up a few questions you might have had about MSDE in its various incarnations. You'll find more detailed answers to license and usage questions for MSDE at http://www.microsoft.com/sql/howtobuy/msdeuse.asp .