Ok. Let me get this out of the way first and foremost… if you’ve been working with SharePoint for a while and know that you’re not supposed to directly touch SharePoint’s databases, feel free to move on right past this post 🙂 BUT, if you know what I’m talking about and thought to yourself “yeah, but that’s just if you want to modify the databases, I can still run queries against them”, then I would encourage you to stick around and hear me out. Seriously 🙂
So, here’s the situation: SharePoint needs SQL Server to operate. I tend to think of SharePoint needing SQL Server like humans need oxygen: we often take the act of breathing for granted, but we really notice when we’re having trouble doing it. SharePoint is the same way with SQL Server; it puts just about every piece of data about a farm and its contents into SQL Server databases and when something goes wrong with those databases SharePoint really has problems.Now, that being said, SharePoint is also very particular in how it uses SQL Server. Its databases have to be set up in a very specific way, with some pretty inflexible configurations, and SharePoint reserves the right to change any of the details of those configurations any time it wants (usually via patches and updates).
Now, this relationship isn’t exactly new news. Nor is the fact that Microsoft does not support the modification of SharePoint’s databases or the access of their contents directly through SQL Server, and instead requires that the data in those databases be accessed through SharePoint’s UI, its object models, or its administrative interfaces. Lots of people way smarter than me have written quite a bit about it in the past, but like a bad penny I keep seeing the topic come up and kind of felt like putting my own thoughts out there on the matter, so here goes.
Because of how particular SharePoint is about its SQL Server databases, Microsoft has made some pretty definitive statements about what you can and can’t do with those databases. In general, it’s like this: don’t touch them. Don’t add things to them, such as indices, views, triggers, or new columns. Don’t delete anything off them either, or create new tables within them. Specifically, Microsoft has a knowledge base article on what is not supported, that I really suggest you read:Â http://support.microsoft.com/kb/841057. On the other side of the coin, there are specific things that you can do, but they’re really more maintenance-oriented things like defragmenting indices or creating maintenance plans for backups. The main source I use for what you can actually do with SharePoint’s databases is this article on TechNet: Database Maintenance for SharePoint 2010 Products.
One thing that I think people often overlook, and sometimes it seems like they’re eager to do so, is an important statement in that KB article I linked to above:
Read Operations Addendum
Reading from the SharePoint databases programmatically, or manually, can cause unexpected locking within Microsoft SQL Server which can adversely affect performance. Any read operations against the SharePoint databases that originate from queries, scripts, .dll files (and so on) that are not provided by the Microsoft SharePoint Development Team or by Microsoft SharePoint Support will be considered unsupported if they are identified as a barrier to the resolution of a Microsoft support engagement.
In plain English: querying SharePoint databases can lock data in those databases and cause performance issues, and can put you in an unsupported state. Yep, even running a query against a SharePoint database can be unsupported, a situation that I see a lot of people either ignorant of or willing to look past. Are there times where you may need to do it? Sure, but I would do my darnedest to avoid having to do that in a live Production environment without first trying to exhaust every supported approach to get the info I need the right way or to copy the database out and query it outside of my live Production SharePoint environment.
So that’s why I like to think of SharePoint’s databases as being a lot like Kuzco in Disney’s movie, the “Emperor’s New Groove”:
My Central Admin 
We had this discussion in the side room at SPS Dayton. Now I see what you were referring to. I agree that any sort of manipulation of the database with homegrown queries or code is a no-no. I have , on occasion, gone into the DB tables to view them with SQL managment studio, when there is no other option to track down really difficult problems, or when you are trying to reverse engineer the product for a customization. The last example of this for me was digging into the Social_DB from the profile service to see how those conversation bubble status updates actually worked. I have also used this technique in the past to track down orphaned sites, or to find out which content database a particular site collection might be in. (It has been awhile for some of this, there may be better ways to do it now)
By: Bill Crider on July 16, 2012
at 9:13 AM
>Any read operations against the SharePoint databases that originate from >queries, scripts, .dll files (and so on) that are not provided by the Microsoft >SharePoint Development Team or by Microsoft SharePoint Support will be >considered unsupported if they are identified as a barrier to the resolution of >a Microsoft support engagement.
I’ve queried SP databases for years and never had a lock-up or anything else nasty.
I’m maybe sceptical but I would imagine that Microsoft’s own consultants would have no hesitation in looking at the database even if they rightly would hesitate to amend it.
The logical conclusion from the previous paragraph is that the statement is there to give Microsoft’s own consultants work. Failing that it is there so MS has something to blame if their own database structure has failings – after all should a database lock-up on a query?
By: Mike Walsh on July 16, 2012
at 10:10 AM