The Old Joel on Software Forum: Part 3 (of 5)

MSDE?

Does anyone have comments pro, con or otherwise about the Microsoft SQL Server 2000 Desktop Engine?

Neville
Tuesday, October 7, 2003

Cons:
You miss out on replication features.
It isn't bundled with analytic tools.
It "only" runs on Windows platforms.
It supports limited number of concurrent connections.
For most desktop applications you'll be fine, but if you even start trying to support like a POS system in a supermarket it probably won't be very happy.

Li-fan Chen
Tuesday, October 7, 2003

I prefer MSDE as an alternative to Access. You get all of the nice SQLServer features: Stored Procedures, scheduling and user defined functions, user defined datatypes, that Access doesn't provide.

For projects that are on the cusp, where I am not sure if MSDE is up to the task, I can build the solution with MSDE, load test it and then migrate to SQL Server if need be.

Being able to save a client a few thousand bucks is always nice.

Canuck
Tuesday, October 7, 2003

I believe Access now provides a nice interface to MSDE.

somebody correct me if I'm wrong.
Tuesday, October 7, 2003

Redistribution of MSDE is, umm, problematic.

I'm not talking about the licensing, I'm talking about the details of actually building an installer.

And then the client needs to know that they're running SQL Server on their box. And need to keep it patched. And that the existing SQL service packs don't work if MSDE was installed from the MSI merge module.

Chris Tavares
Tuesday, October 7, 2003

let's say i want to develop a desktop app, like... city desk.
city desk uses "the jet database engine" i believe. This is the same database engine access uses, right? Is MSDE a different engine? Or is it a version of jet with advanced features? Is there a whitepaper that describes the differences between access/jet/msde/sql server ?

sql noob
Tuesday, October 7, 2003

http://www.microsoft.com/sql/techinfo/deployment/70/msdejet.asp

Andrew Lighten
Tuesday, October 7, 2003

Some of the previous posts do a good job on the limitations of MSDE, however one thing I haven't heard mentioned is the file size limitation. I believe that you're limited to a 2GB file -- not sure if you can get past this by using FileGroups. If you've got a lot of data MSDE may not be for you.

Sgt. Sausage
Tuesday, October 7, 2003

"I prefer MSDE as an alternative to Access. You get all of the nice SQLServer features: Stored Procedures, scheduling and user defined functions, user defined datatypes, that Access doesn't provide."

You'll get no argument from me that MSDE isn't often preferable to Access.

But Access does have a rudimentary but very useful form of stored procedure that most people are unaware of. For example, see http://support.microsoft.com/default.aspx?scid=kb;en-us;201493

And regarding user defined functions, what could be simpler of more powerful than writing any function you want in VB or VBA and having it available for use in any query you want? That's exactly what you get with MS Access or VB combined with a Jet backend.

Herbert Sitz
Tuesday, October 7, 2003

"It supports limited number of concurrent connections."

Is this true? Apart from any OS trusted connection limits, I was under the belief that MSDE has no such limitations, though they do give some arbitrary number (5?) beyond which performance will degrade. It degrades for no reason other than the fact that they handicapped the execution engine and parallel execution.

Dennis Forbes
Tuesday, October 7, 2003

Last time I checked I can say for sure Dennis is correct.

Mitch & Murray (from downtown)
Tuesday, October 7, 2003

I was under the impression that the new version of Access USES MSDE. No Jet no more.

Wednesday, October 8, 2003

MSDE is a fine solution for creating small database applications that can expand easily to large applications simply by moving the database to a full SQL Server.

Performance does degrade after 5 concurrent connections. So what? How often will this solution have more than 5 users processing data at the same time? If you use a disconnected recordsets (ADO.Net) you can easily go over 5 users without a major performance hit. If performance degrades too much, get SQL Server and move the database.

Installation can be a pain in the ass. However, with InstallShield and the MSI provided by Microsoft, installation is easy and painless. We have and MSDE application at 40 sites and haven't had any installation problems.

The main disadvantage with using MSDE is that it does not come with a management interface. You can use Access to do some tasks or, if you have SQL Server, you can use Enterprise Manager. The other alternative is to roll you own or purchase source code for an Enterprise Manager clone. We wrote our own so we can limit the functionality to what we want our clients to do in the field. (Contact me offline if you need details).

All said, if you need a database that will run in large environments (SQL Server) and in small environments (MSDE) and don't want to license an engine, MSDE is a great solution. It is fast, stable, and has all the features of SQL Server 2000. And it is free.

Eric Budd
Wednesday, October 8, 2003

MSDE is great! The only serious downside: make sure your operations include SQL Server security patches if they apply. Either you include them as a security upgrade to your product that embeds MSDE, or your clients have to be informed that they should track SQL Server security advisories.
Hopefully this will become less of a downside once we have a unified "Microsoft Update" support facility.

Just me (Sir to you)
Wednesday, October 8, 2003

And, to add 2 cents more:

You can get the Enterprise Manager for free.

Just go and download the trial edition of sql server. DO NOT install the server stuff (that has a expiry date!). Just install the client tools. You get them all (query analyzer, EM etc). They all work, and don't expire.

Further, both the MDSE and the EM tools install and run fine on a win98 boxes. (you don’t need any server editions of OS to run this stuff).

So, if you are looking to play with sql server, and not pay...it is a good and free way to jump onto sql server.

On the other hand....you can now get sql server for $50 from MS anyway....

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Albert D. Kallal
Wednesday, October 8, 2003

>>On the other hand....you can now get sql server for $50 from MS anyway....<<

What? How? Do you have a link to that offer, Albert?

Tim Lara
Wednesday, October 8, 2003

Nevermind...Think I found it:

http://www.microsoft.com/sql/howtobuy/development.asp

Tim Lara
Wednesday, October 8, 2003

"I believe that you're limited to a 2GB file -- not sure if you can get past this by using FileGroups. If you've got a lot of data MSDE may not be for you."

If you've got a desktop application that uses a local database that needs over 2GB of database, then you're probably gonna have bigger problems than database size. MSDE is aimed at desktop apps that need a decent database, but at 2GB it's probably time to look MS-SQL or Oracle for performance and scalability reasons if nothing else.

Mark Hoffman
Wednesday, October 8, 2003

i think that is 2 GB per file stored in the DB, not 2GB for the entire database itself.

rz
Wednesday, October 8, 2003

Get all info on MSDE 2000 from http://www.microsoft.com/latam/sql/techinfo/development/2000/MSDE2000.asp

If you want to understand the inards of the performance capping better, read http://www.betav.com/Files/Content/Whitepapers/msde_files/msde.htm , which is based on SQL7 based MSDE, but it seems to apply to the 2000 version as well.
Unlike stated many times the limitation is not on 5 concurrent connections. That is just a heuristic put forward to avoid having to explain the details. You can have unlimited connections, but after 14 concurrent treads are executing, a variable delay is induced for new treads.
The above article shows you also how to monitor this.

Just me (Sir to you)
Thursday, October 9, 2003

If you have more than 14 concurrent threads, you are either not partitioning your application data layer correctly, or you should be having your clients shell out the extra cash for an instance of SQL Server... ;)

DAL Man
Tuesday, March 23, 2004