Executing Direct SQL Queries on SharePoint Content Databases: Is it a good idea???

This article is based on the findings and lessons learnt during one of my recent assignments which included the development of an Analysis Tool which can gather all Vitals out of a SharePoint Farm which can be further leveraged to take decisions during the migration at some later stage.

While deciding the direct execution of SQL Queries on SharePoint Databases, you should consider the following questions and plan accordingly-

What could be the possible repercussions if we execute direct SQL queries on Content Database?

  • Reading from the SharePoint databases programmatically, or manually, can cause unexpected locking within Microsoft SQL Server which can adversely affect performance.
  • Any read operations against the SharePoint databases that originate from queries, scripts, .dll files (and so on) that are not provided by the Microsoft SharePoint Development Team or by Microsoft SharePoint Support will be considered unsupported if they are identified as a barrier to the resolution of a Microsoft support engagement.
  • If unsupported read operations are identified as a barrier to the resolution of support engagement, the database will be considered to be in an unsupported state.
  • To return the database to a supported state, all unsupported read activities must stop.

What are unsupported operations on SharePoint Content Databases?

It is clearly unsupported to update, delete, or insert records. The risks are surely far more obvious. Also be aware that any database changes would definitely break the supportability as stated by Microsoft. 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
  • Calling existing stored procedures directly, except as described in the SharePoint Protocols documentation
  • Adding new stored procedures
  • Adding, changing, or deleting any data in any table of any of the databases for the products
  • Adding, changing, or deleting any columns in any table of any of the databases for the products
  • Making any modification to the database schema
  • Adding tables to any of the databases for the products
  • 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.)
  • Enabling SQL Server change data capture (CDC)
  • Enabling SQL Server transactional replication
  • Enabling SQL Server merge replication

What are supported operations on SharePoint Databases?

  • Operations that are initiated from the SharePoint administrative user interface
  • SharePoint specific tools and utilities that are provided directly by Microsoft (for example, Ststadm.exe)
  • Changes that are made programmatically through the SharePoint Object Model and that are in compliance with the SharePoint SDK documentation

What happen if unsupported data modification is discovered?

If an unsupported database modification is discovered during a support call, the customer must perform one of the following procedures at a minimum:

  • Perform a database restoration from the last known good backup that did not include the database modifications
  • Roll back all the database modifications

What if previous version of the database that does not include the unsupported modifications is unavailable or if the customer cannot roll back the database modifications?

  • The customer must recover the data manually.
  • The database must be restored to an unmodified state before Microsoft SharePoint Support can provide any data migration assistance.
  • If it is determined that a database change is necessary, a support case should be opened to determine whether a product defect exists and should be addressed.

What can be done if still the content database needs to be queried directly for some reason?

Never run the direct SQL queries on Content Database in Production Environment

Take Following steps:

  • Restore the Database backup from Production to Development Environment
  • Take Database Offline
  • Run SQL Queries with [NOLOCK] option

Before running the above steps make sure the database is not in intermediate stage [nothing is checked out] else can get different document count then actual.

Key Takeaways: Based on the facts exposed by Microsoft Documentation on Direct Query Execution on Content Databases the key takeaways are:

  • This is completely unsupported by the EULA you agreed to when you installed SharePoint.
  • Your queries are not guaranteed to work after applying any patches or service packs to SharePoint since Microsoft could change the database schema anytime.
  • Directly querying the database can place extra load on a server and hence performance issues.
  • Direct SELECT statements against the database take shared read locks at the default transaction level so your custom queries might cause deadlocks and hence stability issues.
  • Your custom queries might lead to incorrect data being retrieved.
Advertisements

SharePoint Best Practices : COM Objects Disposal

While writing the code with SharePoint Server Side Object Model I often stuck with a question “What all objects should I be responsible to get disposed off?”

Though this seems to be a simple question but I found it worth researching upon to extract the list of all the Objects for which a developer is responsible to get disposed off in order to avoid any memory leaks in the farm.

In order to simplified the story I have divided SharePoint Objects following three categories:

List of Objects that must be Disposed explicitly

  1. SPWeb Object created with SPSite.OpenWeb
  2. SPSite objects created with SPSite.SelfServiceCreateSite
  3. SPWeb created by SPLimitedWebPartManager
  4. SPSite created by SPSiteCollection.Add
  5. SPSite created by SPSiteCollection[] index operator
  6. SPWeb created by SPWebCollection.Add
  7. SPWeb created by SPWebCollection[] index operator
  8. SPSite created with UserProfiles.PersonalSite

List of Objects that must be Closed (Not Disposed) explicitly:

  1.  PublishingWeb created by PublishingWeb.GetPublishingWebs[] index operator
  2.  PublishingWeb created by PublishingWeb.GetVariation
  3.  PublishingWeb created by PublishingWebCollection.Add

List of Objects that must Never be Disposed explicitly

  1. SPListEventProperties.Web
  2. SPWebEventProperties.Web
  3. SPItemEventProperties.Web
  4. SPItemEventProperties.ListItem.Web
  5. SPItemEventProperties.Web.Site
  6. SPItemEventProperties.ListItem.Web.Site
  7. SPFeatureReceiverProperties.Feature.Parent
  8. SPSite.RootWeb
  9. SPWeb.ParentWeb
  10. SPList.ParentWeb
  11. SPSite returned by SPControl.GetContextSite
  12. SPWeb returned by SPControl.GetContextWeb
  13. SPSite returned by SPContext.Current.Site
  14. SPWeb returned by SPContext.Current.Web

How to ensure proper disposal of object after the job is done:

There are following two Constructs available that can ensure the disposal of SharePoint Objects even if any unforeseen runtime exception occur in the code.

1. Use “Using Block”

using(SPWeb oWeb = osite.RootWeb)
{
}//oWeb will get disposed automatically as soon as using block ends

2. Use “Try, Catch & Finally Block”

try
{
SPWeb oWeb = osite.RootWeb;
}
catch(Exception ex)
{
throw;
}
finally
{
oWeb.Dispose();  //oWeb will get disposed as soon as Dispose() method called.

}

Hope this will help someone in need…