Friday, March 20, 2009

SharePoint databases

I’ll be talking about the storage requirements around Windows SharePoint Services v3 (WSS v3) specifically here.

 

One of the core components required for WSS v3 to operate is a place to hold its content and configuration data. It does so in SQL databases. There are quite a range of SQL databases available from Microsoft starting with the Embedded and Express Editions (both free), moving up to Workgroup, Standard and Enterprise. There are also different versions when you consider SQL 2005 vs SQL 2008. The good news is that they can all be used to store WSS v3 content and configuration.

 

Microsoft SQL Server 2005 Feature comparison (Embedded Edition is not listed here)

Microsoft SQL Server 2008 Feature comparison

 

Prior to installing WSS v3 on a machine a SQL Server instance needs to exist. If one is not present then you can select to have WSS v3 install SQL Server 2005 Embedded Edition (SSEE). Now SQL Server 2005 Embedded Edition is a far cry from the old MSDE database although it still does have limitations (as you would expect in a free version). The most important of these are the fact that it doesn’t come with any GUI tools, cannot be version upgraded and cannot be accessed across a network. Another overlooked default is that fact that it installs itself, programs and data, into the C: drive which can lead to space issues later. However, one major plus of the Embedded Edition (unlike the Express Edition) is it does not place a limit on the size of a database (the Express Edition limits databases to 4GB in size).

 

Thus, the Embedded Edition is a great option to get started with WSS v3, especially in the fact that you can work around some of the initial limitations. For example, you can download some free GUI administration tools from Microsoft and you can also relocate the databases to another drive to free up space. This is exactly what the SBS 2008 ‘Move SharePoint databases’ wizard does. I have heard people say that you should use a more upmarket version of SQL (e.g. Workgroup or Standard) because it give better performance. I must admit that I have never been able to prove the fact that SQL Embedded Edition is any slower than SQL Standard for typical WSS v3 sites. Now that may not be the case when you get really large WSS v3 sites ( ie > 4GB) but how many WSS v3 sites have you ever come across that are that large initially? Not many I would think. The only reason I can see you wanting to use a more upmarket version of SQL is if you wanted some of the specific high end SQL tools and abilities, like integrated SQL backup and restore as well as clustering. However, for most WSS v3 sites, at least initially, this is generally not required and because you can easily upgrade to another version of SQL later if required it doesn’t make sense to me to go this expense until absolutely necessary.

 

image_4_34E93C60

 

If you are happy using the SQL Embedded Edition that comes with WSS v3 then you can simply complete a Basic install (although not on SBS 2003 which requires a side by side install to prevent things from breaking). This will install SQL Server 2005 Embedded Edition (SSEE) onto you machine, install WSS v3 and use SQL Embedded Edition automatically to store content and data. Nothing could be simpler. If however you want to use another version of SQL server as your data repository that version needs to be installed prior to installing WSS v3 and then you’ll need to select an Advanced install.

 

The difference between a Basic and Advanced install are quite marked in that a Basic install does everything for you, including setting up the first WSS v3 site. With the Advanced installed you need to do everything manually.

 

In summary then, WSS v3 requires a version of SQL Server to hold its content and configuration databases. You can choose from a wide range of SQL Server version from Microsoft, however the Embedded Edition that comes with WSS v3 is suitable for most initial deployments. If you plan to do much work with WSS v3 then it is beneficial to have a good basic knowledge of SQL Server as well.

 

Detailed information about WSS v3 databases and installing options can be found in my Windows SharePoint Operations Guide along with more information about getting SharePoint up and running in a business.