The Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is a data engine built and based on core SQL Server technology. With support for single - and dual-processor desktop computers, MSDE 2000 is a reliable storage engine and query processor for desktop extensions of enterprise applications. The common technology base shared between SQL Server and MSDE 2000 enables developers to build applications that can scale seamlessly from portable computers to multiprocessor clusters. You can install up to 16 separate instances of MSDE on a single machine.

Designed to run in the background, supporting transactional desktop applications, MSDE 2000 does not have its own user interface (UI) or tools. Users interact with MSDE 2000 through the application in which it is embedded. MSDE 2000 is packaged in a self-extracting archive for ease of distribution and embedding. However, there are a number of freeware, shareware and low - cost options for administration of MSDE, not the least of which is the free .NET Sample web-based SQL Server Admin interface distributed by Microsoft itself. All it takes is a quick search on the web, and you'll find at least two or three of them. You can also connect to it with MS Access 2000, or of course with Enterprise Manager if you own SQL Server. MSDE looks, acts, behaves and feels like a SQL Server.

In addition, MSDE 2000 can be built into applications and redistributed royalty-free with Microsoft development tools, such as Microsoft Visual Studio® .NET and Microsoft Office XP Developer Edition. This allows developers to build enterprise-class reliability and advanced database features into their desktop applications.

There are a number of Microsoft products that "allow" you as a developer to distribute the MSDE to your clients with your solution, royalty - free, if you own the product:

• SQL Server 2000 (Developer, Standard, and Enterprise Editions)
• Visual Studio .NET (Architect, Developer, and Professional Editions)
• ASP.NET Web Matrix Tool
• Office XP Developer Edition
• MSDN® Universal and Enterprise subscriptions
  

I should hasten to note that the ASP.NET Web Matrix Tool, developed by Microsoft gurus to give everybody a chance to install the .NET Framework and create ASP.NET solutions without any charges or licensing fees, is itself a free product, so it should be obvious that Microsoft has relaxed its licensing policy considerably for MSDE usage by developers.

You may find the following sample of links useful in evaluating MSDE and some of the freeware and shareware tools that have been developed for it:

SourceForge and other:
http://sourceforge.net/projects/asp-ent-man
http://www.sourcecode.co.nz/vsqlw/indexhtm

.NET Query Tool:
http://www.msde.biz/download.htm

Usage Guidelines (from the horse's mouth):
http://www.microsoft.com/sql/howtobuy/msdeuse.asp

An attractive alternative to using the Microsoft Jet database, MSDE 2000 is designed primarily to provide a low-cost option (wait a minute -"low cost"? Its FREE!) for developers who need a database server that can be easily distributed and installed with a value-added business solution. Because it is fully compatible with other editions of SQL Server, developers can easily target both SQL Server and MSDE 2000 with the same core 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. At least one test I've seen on the web stated that MSDE was 40 times faster than Access, and the guy posted the figures to prove it.

The key question becomes: Since MSDE has a "query governor" built in that restricts its performance by one of Microsoft's vague "workload" guidelines, and is recommended for solutions where there are no more than 5 simultaneous client connections, how well DOES IT REALLY perform under load? Will it support 10 users? How about over the web, where all the connection strings are likely to be identical, and the only active user would be "sa", perhaps (if you possess an above room-temperature IQ) -- with some sort of password?

It's really funny, but every single article I've found on the web (with the exception of the one test I found where the writer showed proof that MSDE is 40 times faster than Access) simply parrots the Microsoft hype stated above. Nobody seems to be willing to take the time to test this thing and see if it's worth using say, in a 100 or 200 user web - based application! I mean, really - I just got tired of looking and gave up!

So, wont as I am to believe in facts, figures and arithmetic, I simply did my own test. First of all, I already have MSDE installed on my "poor man's machine" - a 6 1/2 year old Pentium 166 with only 96 MB RAM that I have hooked up via BNC cable to my main development machine at home, for testing purposes. It runs XP Professional. I only turn it on about once every two weeks.

Second, I already had a test program built from my previous article on "COM+ Overhead in the .NET Platform", and all I needed to do was change the connection string to point to my poor old P166. Well, that was easy enough!

The bottom line? My old 166 running MSDE handled 10,000 completed requests for the "CustOrderHist" stored procedure in the Northwind database in 134 seconds without dropping a single connection. That's about 73 requests per second over a LAN network connection between the server machine and the client test machine. If you multiply by generally accepted industry multipliers, that's enough to support well into the several hundred user range!

However, I wasn't satisfied. This first test set emulated a client-server desktop type scenario. I wanted to also test a web-based scenario. So I whipped up an ASP.NET Webform page that called into my .NET DB Class Library and displayed the first row of the returned SqlDataReader. By writing something out to the page, we can compare the total bytes sent with Homer and have an extra "check" on whether each and every page request was successful

On this test I had to run Homer from the MSDE machine calling the web page on my main box (which runs Win2K Advanced Server), because WIndows XP Pro IIS only supports 10 http connections, and I wanted to ratchet it up higher than that. So to the extent that both Homer and the MSDE were both running on the same machine, you could make the case for a slightly flawed test. I set a random delay of from 200 to 500 milliseconds to simulate real traffic.

Here are the Homer results:

These are far from authoritative tests; more testing on specific apps and configurations would be necessary. But if you're looking for a free RDBMS solution to go with your application, one that instantly scales to the full-fledged versions of SQL Server without any changes, I'd recommend you trash MS Access - MSDE is a total no-brainer!

Finally I think this little exercise underscores what I believe is one of the most important rules in successful application architecture and development: In software development, don't assume anything, either what you read from "experts" or what you yourself may believe - even if you feel sure you are "right".  Instead, gather information -- plan and test, get the facts, figures and arithmetic, and you will be able to make sound architectural software development decisions as well as promises to the people who pay you that you'll feel confident you can keep.

Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform. Pete's samples at GotDotNet.com have been downloaded over 41,000 times. You can read Peter's UnBlog Here.  --><--NOTE: Post QUESTIONS on FORUMS!