I am writing this blog post because I have recently come across numerous blog posts and articles discussing how you can directly query SharePoint databases. I have also stumbled upon a web part available for free that shows the most popular content among the SharePoint farm, this web part directly queries the SharedServiceProvider database to retrieve the information needed by the web part. Moreover, I have recently downloaded a tool that grabs deleted documents from the content database and saves them back to your hard drive.

Before diving into the pros and cons of querying SharePoint databases, let’s explore them and see how we can write direct T-SQL queries against them.

Below is a very high-level database diagram. I would like to extend a special Thank you to Ethan for coming up with this useful diagram.

For instance, dbo.AllUserData is a table that holds information about all the list items for each list. Here is a sample query to retrieve the contributions of each user in a certain site collection:

SELECT      tp_author, count(*) as 'Number Of Documents & Items'
FROM        dbo.AllUserData
Where       tp_SiteId='GUID'
Group by    tp_author

And here is another query to the SharedservicesProvider database that retrieves the most popular documents:

SELECT ANLResource.DocName, COUNT_BIG(*) AS HitCount 
FROM ANLResourceHits 
INNER JOIN 
ANLWeb 
ON ANLResourceHits.WebGuid = ANLWeb.WebGuid 
INNER JOIN 
ANLResource
ON ANLResourceHits.ResourceId = ANLResource.ResourceId 
WHERE (CHARINDEX(’.aspx’, ANLResource.DocName) = 0) 
AND (CHARINDEX(’.swf’, ANLResource.DocName) = 0) 
GROUP BY ANLResource.DocName 
ORDER BY HitCount DESC

Well, I know it’s incredibly easy, you can extract as much data as you want and consume this Data in your custom solutions (Reports, Web Parts,…) and the sky is the limit.

BUT

1. This is completely unsupported by the EULA you agreed to when you installed SharePoint.
2. Your queries are not guaranteed to work after applying any patches or service packs to SharePoint since Microsoft could change the database schema anytime.
3. Directly querying the database can place extra load on a server and hence performance issues.
4. Direct SELECT statements against the database take shared read locks at the default transaction level so your custom queries might cause deadlocks and hence stability issues.
5. Your custom queries might lead to incorrect data being retrieved.

A Debate about the stability issues

I have recently took part in a debate on one of the forums with a guy who claims that there are no stability issues when you directly query the database since you can use the With (NoLock) clause in your TSQL Queries as follows :

Select * From dbo.AllDocs With (NoLock)

This may seems better, you can avoid the deadlocks by using (NoLock) clauses BUT you will run into another problem which is retrieving incorrect data because you will be observing the database in an intermediate state (Data are not committed yet).

Insertions and modifications of the SharePoint databases

It is clearly unsupported to update, delete, or insert records. The risks are surly far more obvious.
Also be aware that any database changes would definitely break the supportability as stated by Microsoft. Examples of such database changes include, but are not limited to, the following:

• Adding new indexes or changing existing indexes within tables .
• Adding database triggers.
• Adding, changing, or deleting any primary or foreign key relationships.
• Changing or deleting existing stored procedures.
• Adding new stored procedures.
• Adding, changing, or deleting any data in any table of any of the databases.
• Adding, changing, or deleting any columns in any table of any of the databases.
• Making any modification to the database schema .
• Adding tables to any of the databases.
• Changing the database collation

The proper and supported ways for data access in SharePoint

The process of data-retrieval in SharePoint should be executed via the SharePoint object model or the built-in SharePoint web services and this is attributed to the following:

1. Microsoft has gone through a lot of work to ensure that using the object model or web services will lead to stable and efficient database interactions.
2. The likelihood of Microsoft breaking their own object model is far less that the database schema changes.
3. You are not going to lose Microsoft supportability.

Summary

Direct Queries to SharePoint databases is a considered a no-no, you should use SharePoint object model or web services instead.Do not worry, I will show you in later posts how to do the data access through the object model, stay tuned!