We’re Serious – Don’t Modify Your Database or Face Consequences

Today in SharePoint land there was a forum post about what’s ok and not ok around modifying the database.  It’s been a long standing position by the SharePoint team to keep your paws out of the database, but recent rules added into the preupgradecheck go further to make sure the schema is in the expected state.

Trevor states:

Can you upgrade to SharePoint 2007 SP2?  The database timer job includes an index defrag in it.  Also, reading or writing to SharePoint databases will throw you into an unsupported state. > Please see: http://msdn.microsoft.com/en-us/library/bb861829%28office.12%29.aspx

Benjamin explains:

I wrote a blog post a while back which details some information regarded supported operations on MOSS databases at http://mossblogger.blogspot.com/2010/06/administration-supported-database.html

Microsoft published a very useful white paper back in November 2009 called "Database Maintenance for Office SharePoint Server 2007" which on the whole is still relevant.

I went on to explain the fragile nature of vendors working with Microsoft’s help to build tools which Microsoft SharePoint clients may use or recommend to clients.  Some of which may use backend protocol changes with microsoft’s protocol documentation to do things not readily exposed in the object model.  Migration and recovery tools may fall into this category.  As is suggested in the modified database error for preupgrade check.  “User modifications to the SharePoint content database, including but not limited to table schemas, index, stored procedures, are not supported and will cause upgrade[…] to fail.”  Obviously if you’re having problems with a tool you should go to the vendor of the tool.  It’s the same if you’ve been using a tool which might have changed the schema.  You should find out what might have caused the database to be modified.

What I’m finding is the preupgradecheck tool has some false positives with the modified databases rule particular.  Some who have installed Project Server or in this case below by simply upgrade not finishing it is reporting the database as modified.  Just because it is being reported as such does not mean that is the case… apparently.

One recent Q&A included someone who had possibly run the graual upgrade and another who simply needed to migrate the databases to avoid missing setup files reported by the preupgrade check.

As is the case with Quest software, most migration vendors and backup/restore vendors were given early access to the bits and many including quest participated in upgrade workshops to help ensure that the tools are compatible with the processes that Microsoft and it’s partners will go through in an upgrade… such that by upgrade time for the customer, the vendor is more aware of what might happen and how to help the customer overcome these challenges.

Earlier today I had finished an WSS 3.0 SP2 and MOSS 2007 SP2 install and fixed issues related to missing features and was getting a clean bill of health with the preupgradecheck.  After getting this I was curious what was fixed in the latest cumulative update currently the June 2010 CU.

Now I’m seeing

InvalidDatabaseSchema… Failed
ContentOrphan… Passed
SiteOrphan… Passed
PendingUpgrade… Failed

 

Failed : Content database with modified database schemas

User modifications to the SharePoint content database, including but not limited to table schemas, index, stored procedures, are not supported and will cause upgrade to future versions of SharePoint to fail.The databases in the following list seem to have been modified from the original schema:

• Data Source=moss2007ug;Initial Catalog=WSS_Content_c510ece1c03d4ec1875c4569e125f96b;Integrated Security=True;Enlist=False;Connect Timeout=15
• Data Source=moss2007ug;Initial Catalog=SharePoint_AdminContent_2da7a517-e7e7-431a-a279-0722750e2fff;Integrated Security=True;Enlist=False;Connect Timeout=15

Please revert the modified database schema to the original state. If necessary, contact any software vendors who might have made this change because reversing the database schema could cause data loss. For more information about this rule, see KB article 954772 in the rule article list at http://go.microsoft.com/fwlink/?LinkID=120257.

 

 

Before you freak out, run in the 121bin directory> psconfig -cmd -upgrade –force

After the upgrade completed, I’m now again getting a clean bill of health.