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”: