SharePoint Portal Server 2003 (SPS 2003) offers the choice of using either Microsoft SQL Server 2000 Desktop Engine (MSDE) or Microsoft SQL Server 2000 to store the databases. MSDE is limited in a number of ways, including a maximum size limit of 2GB and a maximum of 5 concurrent user connections – meaning that it is only suitable for small installations. Typical recommendations are <1000 users or <2GB data. Note that this is only an issue if the full SPS 2003 product is installed using MSDE.

Windows SharePoint Services (WSS) is a Windows Server 2003 component, and if SPS 2003 is not installed WSS uses a special version of MSDE, known as SQL Server 2000 Desktop Engine (Windows), or WMSDE. This is an unthrottled version of MSDE designed only to be used by Windows components, and as such is not limited in the same way as MSDE. The maximum size limit and current connections limit have both been removed. However it is still much more limited than SQL Server 2000 in the following ways:

  • 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.

 

MSDE and WMSDE are great options to choose for a test, proof-of-concept or small-scale deployment. If your solution needs to scale up to a large number of users or sites, there is no way you should be considering anything other than SQL Server 2000. Using WMSDE or MSDE will simply not offer the scalability, resilience, performance or ease of management required for a solution of that scale. The WSS Administrator’s Guide recommends: "Consider using SQL Server instead of WMSDE if you anticipate supporting more than 10 active and large Web sites."

 

If you have installed WSS using WMSDE, and then install SPS 2003 over the top, SPS 2003 will uninstall WMSDE and replace it with MSDE or SQL Server 2000. For a guide to the migration process from WMSDE to SQL Server, see the Windows SharePoint Services 2.0 Administrator’s Guide, article "Migrating from WMSDE to SQL Server".

Don’t forget to check for updates to WMSDE and WSS using Windows Update.

 

[Update: 18th March 2005]:

I was asked in response to this article whether it is possible to check whether MSDE or WMSDE is installed. It is not obvious how to do so, but the following support article explains all:

"You may not be able to distinguish between instances of Microsoft SQL Server 2000 Desktop Engine or Microsoft SQL Server 2000 Desktop Engine (Windows) in the Add or Remove Programs entry in Control Panel" http://support.microsoft.com/default.aspx?scid=kb;en-us;823465