The better you understand the SharePoint databases, the better off you can manage and optimize your farm.  Often the SQL databases are managed by the SQL team, so this is good info for them as well.  Note I’m providing some detail about the databases and tables, but as always DO NOT abuse this information and don’t modify databases or risk failed upgrades and failed support calls or worse.  Even read queries are cautioned against for production database.

The screenshots and information here was covered in my session on Considerations for Large-Scale SharePoint Deployments on Microsoft SQL Server with Paul Learning and myself at TechEd 2009.  A recording of our presentation including the deck is available for attendees.  A copy of the deck is Teched09_DAT307_Oleson_Learning

Config db - is the heart and soul of the farm.  It contains the top level schema of everything in the farm.  It contains a list of the global settings and configuration of the farm.  When you’re on the operations tab, you are making changes in the config database.  It understands what services are running where, so it can essentially determine servers by role and determine where the packages should be deployed.  The config db knows all of the server names of the servers in your farm, databases, and it knows the web applications by name.  Even down to the site collection, it keeps track of what site collections are in what databases.  What about subsites or subwebs?  No entry.  That’s another reason that site collections are the atomic unit.  They are ultimately listed in the config db which gives them portability. 

Content database – all content that is uploaded through the web ui and through the explorer view is stored in the content database.  The AllDocs, AllDocStreams, and AllDocVersions in combination contain information about all the files stored and uploaded in any list.  You can get additional meta data and content type information in other tables including context information like webs and sites.  But the BLOBs are in that ALLDocstreams table. 

The Search Database contains search properties and attributes.  Don’t be confused… The index is still in a collection on the file system on the index server and propegated to the query or search server.

The SSP Database contains the configuration of the various services you’d find on the SSP Admin interface.  As well it contains the profile database, information on the mysite host and various audiences that are compiled.

You’ll also see Admin content and SSP content, those are content databases just like the ones above, but are often easily recreated as long as data is not stored in them.  I use to tell people to create tasks lists and store their ops documentation in their.  I no longer recommend that.  I recommend using separate team sites with disk based copies of the libraries of those site collections for quick reference.

What is the one document you should never store single instance in SharePoint?  Your SharePoint DR Plan and execution steps!

Here’s another attempt at simplifying the databases stored in SharePoint… by Neil.

Resources and related posts

SharePoint Performance and File Groups for Temp db, Search db, and Content Dbs (blog post)

Performance recommendations for storage planning and monitoring (SharePoint CAT team “Storage” paper)

Technet article: Physical storage recommendations (Office SharePoint Server) TechNet Article

Using Microsoft Office SharePoint Server to implement a large-scale content storage scenario with rapid search availability Paul Learning & Knowledege Lake

Key capacity planning information and links (lots of links)

Top SharePoint Storage (SQL) Resources (Links)

Look forward to more…

Joel Oleson