SharePoint databases in action

In a previous blog post about Windows SharePoint Services databases I spoke about what SQL technology was used to hold Windows SharePoint Services content and configuration information. In this post I’ll have a look at these databases in action.

 

After creating a SharePoint site the information that is now entered into SharePoint is stored in a SQL database. To locate the name of this database you need to go into the SharePoint Central Administration for your site. You do this on the server on which you installed SharePoint via Start | Administrative Tools | Windows SharePoint 3.0 Central Administration like so:

 

 

Now select the Application Management tab then the Content Databases from under the SharePoint Web Application Management section. You should now see the name of the database used by SharePoint like that shown below:

 

 

If you click on the database name (in this case ShareWebDb) it will bring up further information about the database. As you can see from the above screen shot it is also possible to add additional databases from this window. Another important fact to remember about SharePoint is that you not only have GUI tools like what is shown here but you also have similar tools that can be run from the command line, allowing for scripting if necessary.

 

In terms of the file system, where exactly is the SharePoint content database stored? It is stored wherever the default data directory is for the SQL instance that you installed on your machine. In this case because we are examining Companyweb on SBS2008, which is using SQL 2005 Embedded Edition you will find those files in C:\windows\sysmsi\ssee\msql.2005\mssql\data like shown below.

 

 

Remember that for each SQL database there are normally two associated files, a .MDF (data) and .LDF (index). Both of these files are important for correct database operations. The actual file size of these database files will grow as the information in your SharePoint site increases, to as large as allowed by your version of SQL server or hardware. In this case, because Companyweb on SBS2008 uses SQL 2005 Embedded Edition it can grow by as much free disk space as there is available on the drive.

 

Note that you can relocate these databases to other locations if required, which is something the SBS 2008 wizards automate for you.

 

Finally, if we take a look in the SQL management tools we can also see the database. Because I am again using SBS 2008 here these GUI tools have been automatically installed since they don’t, by default, come with SQL 2005 Embedded Edition even though they are a free download from Microsoft. To launch the GUI tools go Start | All Programs | Microsoft SQL Server 2005 and run SQL Server management Studio Express. To connected to the SQL 2005 Embedded Edition instance you will need to use the following connection string

 

\\.\pipe\mssql$microsoft##ssee\sql\query

 

Once entered you should see something like:

 

 

Now simply locate the database (SharedWebDB) under the databases folder and right mouse click to view the properties.

 

More detailed information about Windows SharePoint Services databases and Windows SharePoint in general is located in my Windows SharePoint Operations Guide.