Edit (13/08/2010)
@joeloleoson Expands this article by explaining how vendors use Microsoft’s protocol documentation to "do things not readily exposed in the object model" on his blog. I recommend you have a read.
=========
I have seen a few queries recently around which database operations are supported in SharePoint. As SharePoint administrators, we need to be aware of which operations should be run on farm databases in order to ensure good farm health and performance - and which operations we should avoid at all costs in order to keep databases in a supported state.
Whilst I might take a slightly more relaxed approach in development environments with regard to database operations (I don't expect support from Microsoft for my VPC), my rule in a production environment is clear: never, ever perform an unsupported operation unless specifically asked to by Microsoft support. That includes reading from a content database - which might surprise some.
That's right - reading from a SharePoint DB will leave it in an unsupported state. That's confirmed in this KB article that was updated in April of this year and applies to pretty much any SharePoint product since WSS 2.0 up to and including SharePoint Server 2010 (with the exception of the logging database, where customisations are fully supported).
If you a take moment and think about this, prohibiting changes to the DB schema actually makes a lot of sense. Microsoft tests SharePoint based on a specific database schema and could not possibly predict changes that third parties are likely to make. Therefore, it could not practically support those changes. I think of this as analogous to purchasing a new BMW Z4, fitting a supercharger and then expecting the dealer to honour any warranty they may have promised - it just would not be practical nor economically viable.
As regards to preventing read operations (e.g. SELECT statements), I suppose that it's analogous to breaking the warranty sticker on an electrical appliance. OK, you may not have touched anything but it implies you may have done. Similarly, if Microsoft discover that you have been taking a look through a content database they would be inclined to think you have made a few changes along the way.
So we know that reading from a SharePoint database is prohibited. Here are a few other operations that Microsoft specifically list in the above KB article as leaving a database in an unsupported state:
• Adding database triggers
• Adding new indexes or changing existing indexes within tables
• Adding, changing, or deleting any primary or foreign key relationships
• Changing or deleting existing stored procedures
• Calling existing stored procedures directly
• Adding new stored procedures
• Adding, changing, or deleting any data in any table of any of the databases for the products that are listed in the "Applies to" section
• Adding, changing, or deleting any columns in any table of any of the databases for the products that are listed in the "Applies to" section
• Making any modification to the database schema
• Adding tables to any of the databases for the products that are listed in the "Applies to" section
• Changing the database collation
• Running DBCC_CHECKDB WITH REPAIR_ALLOW_DATA_LOSS (However, running DBCC_CHECKDB WITH REPAIR_FAST and REPAIR_REBUILD is supported, as these commands only update the indexes of the associated database.)
I recommend you read the KB article linked above for the most up to date list of (un)supported operations.
So which operations can we perform on a SharePoint database? The SQL DBA's out there will know that routine maintenance is essential for optimum performance - and few areas are as important to SharePoint performance than the SQL back end. Whilst I have not yet seen an equivalent resource for SharePoint 2010, Microsoft published a very useful white paper back in November 2009 called "Database Maintenance for Office SharePoint Server 2007". I encourage you to take a look if you haven't seen this already and are involved in MOSS administration (although I imagine it will be relevant to SharePoint 2010 also).
According to this white paper, the following maintenance tasks are recommended:
• Checking database integrity (ensures that your data and indexes are not corrupted).
• Defragmenting indexes by either reorganizing them or rebuilding them (applies mainly to indexes; fragmentation can be reduced by setting a large database start size).
• Setting the fill factor for a server (allows index performance to be fine tuned).
• Shrinking databases to recover unused disk space (white space caused by deleting documents, libraries, lists etc.).
Note that Microsoft do not recommend configuring auto-shrink operations, and advise only to shrink content databases due to the fact that other types of databases (e.g. SSP) do not typically undergo a large number of deletions.
In conclusion, things haven't really changed much since MOSS with regard to which DB operations are supported - the main message is to keep well away from them! I imagine Microsoft will release a similar resource for SharePoint 2010 with an updated list of recommended DB maintenance operations. For now, I will be implementing the above MOSS maintenance plans on my SharePoint 2010 test environment.
