Skip to main content
Go Search

Ayman El-Hattab's Blog [MVP]

Home
Ayman El-Hattab's Blog
Marwan Tarek's Blog
Mohamed Yehia's Blog
  

SharePoint 4 Arabs - Online SharePoint Training, Video Tutorials and Webcasts in Arabic > Ayman El-Hattab's Blog [MVP] > Posts > Five reasons not to directly query SharePoint databases
Five reasons not to directly query SharePoint databases

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.

 

DISCLAIMER : I highly recommend executing the following queries in a non-production environment.

 

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!

Comments

Re: Five reasons not to directly query SharePoint databases

Thanks Ayman its really Helpful .. waiting for the post "how to do the data access through the object model"
can you add in it point about when should use Object Model and when web service !!
at 9/26/2009 10:42 AM

Re: Five reasons not to directly query SharePoint databases

Thanks for the compliment, really appreciate it.
Yes, sure, I'll dicuss this point.
Ayman El-Hattab at 9/26/2009 11:57 AM

Question ?

Thanks Ayman for this post, but what is the use of having these queries if i can't execute it on the production environment ?

Will be very useful if you give an example to use the object model or web services for the same type of data.
 
at 10/3/2009 2:40 AM

Re " Question ?

It seems that the provided queries have confused some people. Just to clear any confusion, I have provided those queries just as examples for the wrong and un-supported methodologies of data access.

I sometimes directly query the databases in my development environment for troubleshooting purposes but this should be totally avoided in production envs for the mentioned reasons.

By the way, I have gone through many projects where I needed to retrieve data from SharePoint .The Object Model and the Web Services were at my disposal and I never needed to query the database!
Ayman El-Hattab at 10/3/2009 4:13 AM

What are the proper and supported ways?

Hello Ayman,
and thanks for this useful post.
You wrote you would show the proper and supported ways for data access in SharePoint. I have tried to find that post, but no luck. When do you think you can write it, please?
Many thanks.
Petr
at 4/2/2010 8:54 AM

Re: Five reasons not to directly query SharePoint databases

So u mean that i cannot create any custom tables inside the sharepoint DB?
I should not touch the sharepoint content db for creating my own custom tables inside it. Rather i should go for alltogether separate custom DB for any custom tables needed.
at 5/13/2010 3:04 PM

Creating a custom table inside Sharepoint DB

Can i create a custom table inside sharepoint content db?
OR is the best practice is to not to touch the sharepoint db and for any such requirement go for alltogether separate custom DB and then use it.
at 5/13/2010 3:08 PM

RE: Creating a custom table inside Sharepoint DB

Yes, you can do but you shouldn't.

If you need to have custom database tables, then create a separate custom database and use it for you new database objects, I also prefer to create this database in another instance than the old holding SharePoint databases.
Ayman El-Hattab at 5/13/2010 3:16 PM

Add Comment

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.

Title


Body *


Attachments