It is interesting to note that when you setup and install a standard Windows SharePoint Services 3.0 (WSS 3.0) it will configure the web application and database on a single server.
You will notice that the database configured is an internal version of SQL Server which can be accessed if you install client database access software.
The recommendation from Microsoft is that you should never access this database and manipulate any data which is perhaps why in WSS 3.0 they have configured a typical installation as an internal database. Their advice is to use the SharePoint API framework to perform any data access or manipulation through using Visual Studio.
This article hopes to show you how you can access the internal database to see the way in which SharePoint is configured to access lists / libraries through non-normalised table structures.
Once you understand how tables and data are configured in the system you will then appreciate how important it is to optimise a SharePoint application.
The steps for accessing WSS 3.0 Internal Database are as follows:
Step 1: Download and install SQL Server Management Studio Express 2005
Step 2: Open SQL Server Management Studio Express and type the following Server name into the text-field: “\\.\pipe\mssql$microsoft##ssee\sql\query”
That is it quiet simple really, now some tables you will find of interest here are:
AllUserData
This table contains a number of fields which is generic for all lists, you will see nvarchar1 .. nvarchar64, int1..int16, float1..float12, etc. All the columns which you generate depending on the field type of these columns the data will be assigned to be stored in one of these cells. If you want to know where a column from a list is stored in the AllUserData table you can find this information out by running the following query:
“select tp_Fields from AllLists where tp_id = (select tp_id from AllUserData where tp_ListId like ‘%Your-List%’)”
The field “tp_fields” is a long string which details the columns in a list and how they are mapped to the respective SQL Server table AllUserData.
I advise that you take care when making any modifications here since any change you can make could potentially destroy your SharePoint installation.
Once you have had a look inside the SharePoint internal database you should have a pretty good idea about how you can access and manipulate records and an understanding of the architecture used inside SharePoint.
If you would like help with setting up or configuring your SharePoint 2007 or WSS 3.0 installation please contact us at Click IT Solutions. We have consultants with experience working with SharePoint technology and provide advice for configuration, development or deployment / hosting options. Contact us now at (02) 9029 5194 or alternatively you can email us at info@clickitsolutions.com.au.
