Tricky question,
Note that licensing is different for both products, but in general both
are free within certain limits.
GENERAL DIFFERENCES.
-MSDE 2000 can use 2 processors, 2005 express cannot.
-MSDE 2000 has an inbult throttle that will kick in after 5 active
connection threads, 2005 Express has no such thing.
-2005 Express has easier/better maintenance features, and comes with a
Manager, MSDE 2000 has not such thing, so you need to get 3rd party
tools(available) or use the tools of the standard SQL server Enterprise
Manager(you need to get your hands on a real SQL Server edition like
developer/standard).
-SQL 2005 Express lacks several server features like an SQLServer agent
and DTS and some other advanced stuff that the MSDE has. Of course the
2005 express has all the new database features as well like an inbuilt
XML type. Also MS will probably integrate 2005 Express better with
their .NET technologie.
-Both servers cannot work as a replication server, just as a
replication client.
LINKS.
see:
http://download.microsoft.com/download/a/a/2/aa2a6558-f2c6-45ba-a16b-cb41f2= 1e70be/sqlexpress_datasheet_final.pdf
and
http://lab.msdn.microsoft.com/express/sql/default.aspx
also see:
http://www.microsoft.com/sql/msde/productinfo/features.asp OVERVIEW:
Storage wise SQL Express:
=B7 Supports 1 CPU, but can be installed on
any Server(multicore functionality not known)
=B7 1 GB Addressable RAM
=B7 4 GB Database Size
Storage wise MSDE:
=B7 Supports 2 CPU's(I'm not sure how multicore's are treated)
=B7 2 GB Addressable RAM
=B7 2 GB Database Size
=B7 has workload throtheling
Note that there is no limit on the number of databases on either type
of SQL Server!
SERVER SETUP
You need to choose your server carefully based on which engine you use.
Take 1 GB more RAM that can be addressed by the SQL server you choose.
be sure to force the kernel in RAM. Also note that if you just use 1
database your disk subsystem is not going to be taxed heavely, so do
not invest too much in RAID technologie.
If you need more than 1 database be sure to put each database on it's
own server instance (You can have 16 SQL servers running side by side)
because each *server* does not share it's RAM with other servers this
should speed both databases up *provided* you have enough RAM (4GB
min). This works well unless you need to do cross database scripting.
Note that in practice I would advise no more than 2 server instances on
a physical machine, and that on multiprocessor boxes you should assign
each Server instance it's own processor (provided you have enough
processors).
MSDE has the advantage on RAM and CPU, but the disadvantage on
connections (should scale up to 25 concurrent connections without
problems, but be careful after that)
MSDE shines with limited connections and limited data, doing complex
heavy calculations.
SQL Express has the advantage on the max database size, and newer
features, and no throtteling. It probably also integrates better with
..NET
ALTERNATIVES.
There are also alternatives to the msde:
http://www.vistadb.net Firefox
PostgreSQL
Hope this helps,
M=2E Evers
