One question that pops up quite a few times when running the SharePoint Sales Academies is “why should I use SQL server and not just stick with (W)MSDE?”
In short :-
WMSDE does not include enterprise management tools such as those provided with SQL Server 2000 (e.g. SQL Enterprise Manager MMC tool) for backing up and restoring the database. However command line tools can still be used (e.g. stsadm tool).
WMSDE can only be managed locally – you cannot remotely connect
WMSDE does not support full-text search – so search from within team sites is not possible
You cannot deploy WMSDE in web farm/clustered configurations, so the solution cannot scale beyond certain limits, and remains a single point of failure.
You cannot use WMSDE for anything other than WSS due to the custom schema it uses (MSDE or SQL Server 2000 can also be used for other custom applications). There is no user interface to edit the database file.
Read the full article for more information.