Thursday, August 06, 2009

Microsoft's Position on Changing SharePoint Databases

I caught a Twitter message from a colleague, Mauro, today passing along a Microsoft Help and Support article which discusses Microsoft's position on database modifications for SharePoint Products. In short, the article breaks down to Microsoft doesn't support changes to SharePoint databases.

The article, "Support for changes to the databases that are used by Office server products and by Windows SharePoint Services (http://support.microsoft.com/default.aspx?scid=kb;EN-US;841057 )" includes a list of unsupported database changes, shown below:

" Examples of such database changes include, but are not limited to, the following:
  • 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
  • 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 unless Microsoft protocol documentation is followed exactly
  • 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 "

This subject comes up rather frequently during discussions and as a proponent of SQL Server in general and especially its Business Intelligence features such as Reporting Services for extending the capabilities of SharePoint solutions, I feel compelled to chime in.

I completely understand Microsoft's position. How could a software company possibly support custom, schema changes or functional modifications to their application? They cannot. Nor can Microsoft really ensure their software will function (optimally) with any type of foreign database object running on the live databses, even a view.

However, accepting Microsoft's position doesn't change two things:

1. SharePoint information is business intelligence information and often times needs to extract, transform, load, and display in other places than its native content database.
2. Where there's a will there's a way.

Exploring some scenarios...

If you need to add custom functionality to your SharePoint environment, then you might build your own custom database with triggers, functions, sprocs and all, and then use a programmatic methods to move the data you need out of the SharePoint content databases and into your custom database. Then, use Web Parts to deliver the data and functionality from the custom database throughout the SharePoint Web sites. This does not overstep any of the guidelines listed in the article.

Secondly, T-SQL queries are some times necessary for reports when using the Object Model as an XML data source reaches limitations. If you need to integrate SharePoint data with other systems or for reports you can:

1. Create a database snapshot of the SharePoint content datbase (if you have SQL 2005 / 2008 Enterprise Edition)
2. U
se programmatic methods to move the data from the SharePoint content database into an intermediary database for abstraction purposes.
Move data into an existing data mart or data warehouse during regular intervals.

Any of these three approaches allows you to leverage Views. The benefits of compiling T-SQL queries as Views is that you can secure them, you can use them as data sources in SQL Reporting Services reports, and you can store them on the database where they are accessible, reusable, they get backed up with database backups, you can optimize query performance with Indexes, and querying Views performs better because you are not storing queries nor are you filtering or transforming data in the Web tier.


Anonymous said...

Your Views will ultimately break if Microsoft changes their database schema. Then, you have to go back and re-write all of your SQL queries. It's a maintenance nightmare. Your best bet would be to write a routine in C# with the SharePoint API that gathers the necessary information and pushes that into a separate database.

Jes said...

Although I agree that Microsoft seems to have a pretty hard line on this, even using views is forbidden since reading the database through SQL is not allowed.

"Examples of such database changes include, but are not limited to, the following: Reading from the database.."

"In the event that unsupported read operations are discovered as part of a support call, the database will be considered to be in an unsupported state."

Since SQL 2005, SQLServer supports both database mirroring and log shipping and both of these scenarios allow you to create a readonly snaphot against the warm standby server to offload reporting. In my opinion this gives you the best of both worlds, point in time transactionally consistent reporting and a 100% supported environment.

Nicholas Bisciotti said...

I agree that reading from the SharePoint content database directly is not a good, long term solution.

However, the approach I like above C# API queries is to design a separate SQL database for the purpose of storing transformed SharePoint content database data according to proper business entity relationships and using SSIS to migrate data from the operational content database to the data warehouse.

The advantages to this approach:
- Data is stored according to entity, in proper data types
- Views do not break
- No interference with SharePoint software operations, database is isolated
- Best source logic can be applied for data that exists in multiple places

Blog Archive