Wiki Home

MSDE


Namespace: WIN_COM_API
If searching on MSDN or Books Online for information on MSDE, use the search string "desktop engine" rather than "MSDE".
Technical limitations:

  • Total database size is limited to 2 Gig. MSDE can handle multiple databases, and if you must run with more than 2GB worth of data, this is the only option. The log is not counted in the 2GB limit--just the data file. The log can grow to any size.
    CREATE DATABASE [test] ON PRIMARY
    (NAME = 'test_Data', FILENAME = 'c:\test_Data.MDF', SIZE = 2100)
    LOG ON (NAME = 'test_Log', FILENAME = 'c:\test_Log.LDF', SIZE = 2500)
    
    CREATE/ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 2048 MB per database.

  • 2 processors. However: "I did all the initial development of my 1-GB U.S. Code of Federal Regulations XML Web service database http://www.oakleaf.ws/cfr/) with MSDE 2000 running on an 833-MHz PIII machine with 500 MB of RAM. The MSDE database showed no signs of slowing down under a simulated load of 250 simultaneous clients."
  • No support for full-text indexing. Microsoft doesn't advertise this missing MSDE 2000 feature in the technical information page.
  • Over 8 concurrent jobs degrades performance. "Once it has been activated, the workload governor limits performance by stalling a user connection for a few milliseconds each time the connection requests a logical read or write on any of the pages in the data files of a database." http://www.microsoft.com/sql/msde/techinfo/workloadgov.asp A well-written application will execute its server-side code in .25 to 5 seconds. Given this type of design, MSDE has plenty of horsepower to support dozens (to hundreds) of users. However, if your design is not well thought out, you could cripple it with a single application. See MSDEBench Marks for more.
    Other issues:

  • Perception. If it is free from MS, it can't be good. Um, you think that just because you paid MS for something that it is good? ;)
  • Bloats the install of an app. If it is just a single user standalone app, it adds 60M to the install, and installs a service on the machine. Even though the app can start/stop it as needed, people might not want a 'server' running on their machine.
    Jim Duffy says it best "MSDE is SQL server!" I can hear it now ;)
    See: http://www.microsoft.com/sql/downloads/sp3.asp for the service pack.

    Database management tools:

    Odbc Phone - VFP version of QA
    Ms Sql Backup - Simple VFP code to backup a database.

    Sql Servre 2000 Eval (includes full working versions of EM, QA and Profiler.)
    http://www.microsoft.com/downloads/details.aspx?familyid=D20BA6E1-F44C-4781-A6BB-F60E02DC1335&displaylang=en

    GaryDeWitt has written a bunch of client tools in VFP. Are they posted anywhere?

    http://www.toadsoft.com/toadsqlserver/toad_sqlserver.htm

    www.asql.biz - a Free Enterprise Manager like tool.

    QALite - a free .NET version of QA: http://rac4sql.net

    There are more... please post.
    more info: http://www.aspfaq.com/show.asp?id=2343
    licensing: http://www.microsoft.com/sql/evaluation/overview/default.asp
    http://www.microsoft.com/sql/howtobuy/msdeuse.asp
    Be aware that some maintenance functions (like DBCC DBREINDEX) can cause a database under 2gb to hit that limit during the reindex/optimization process. We just ran into this. In fact, after 2 1/2 years we're forced to have our client purchase the full standard edition due to mysterious performance issues. Oddly, the main table just crossed over 1/2 million records but the filegroup sizes are still well under 2gb (about 1.2 gb). We've already copied the data to a full version they have on another server and the app literally screams. So, moral of the story: don't count on MSDE as a long term solution as a backend to an enterprise system. Also, if mysterious things start to occur, like major slowdowns, etc. you won't be able to get help from MS. So if full support is important to you, stay away from MSDE in production, unless you truly are using it as a desktop data store, etc. -- Randy Jean

    I think it fair to mention that mySQL for the Linux platform is also entirely FREE. I doubt it has such market driven greedy motives in mind either such as not including any basic support tools like QueryAnalyser and Enterprise Manager. Also as your solution grows you won't have to purchase full blown CPU licenses or CALS.

    Not that MSDE doesn't serve a purpose.

    I noticed the mySQL wiki link is a bit weak, could someone make some brief notes there about their experiences? Thanks!
    Rick Hathaway
    See Also Msde Setup MSDEand Replication
    Contributors: Carl Karsten Roger Jennings Cindy Winegarden Steve Sawyer
    Category MSDE Category Database Alternatives
  • ( Topic last updated: 2007.02.17 05:58:16 PM )