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. Use programmatic methods to move the data from the SharePoint content database into an intermediary database for abstraction purposes.
3. 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.
