Sharepoint: supportability scenarios

Today I posted some samples about Search and Powershell integration. I mention about the supportability of querying sharepoint tables. I thought about it and I wanted to clarify my point of view and what is public & published.

I have listed the main areas (I'm primarly focusing on Office12) where I have seen supportability questions. If you have any question, please call your support contact or access http://support.microsoft.com/

• Support for changes to the databases that are used by Office server products and by Windows SharePoint Services 
• Information about the Maintenance Plan Wizard in SQL Server 2005 and about tasks that administrators can perform against SharePoint databases
• Hardware virtualization support for SharePoint products and technologies
• Supported and unsupported scenarios for working with custom site definitions and custom area definitions in Windows SharePoint Services, in SharePoint Portal Server 2003, and in Office SharePoint Server 2007

On the other hand, there are articles as SharePoint Database Access where it is recommended to not query database but instead use the object model.

Looking back to the scenario of my original comment if you are debugging with the SQL Profiler your SSP Database and at the same time running the powershell script or getting the crawl history, you will see something like:

• SP:Starting Event
• exec dbo.proc_MSS_GetCrawlHistory @ContentSourceID=NULL,@MaxRecords=NULL,@BeginTime=NULL,@EndTime=NULL,@CrawlStatus=NULL
• SP:StmtCompleted
• SELECT A.CrawlID,A.CrawlType,B.ContentSourceID,A.Status,A.StartTime,A.EndTime,CAST(A.SuccessCount as int) as SuccessCount,A.ErrorCount,A.WarningCount from MSSCrawlHistory as A inner join (select distinct CrawlID,ContentSourceID from MSSCrawlContent) as B on A.CrawlID = B.CrawlID WHERE A.ProjectID = 1 AND DATEDIFF([Day], A.StartTime, GETDATE()) <= 7 ORDER BY A.EndTime DESC

So you may think: "as these tables are not heavy used, a directly querying with some changes may have less locking, we should follow that", something that you may test only in you lab/dev environment.

SELECT A.CrawlID,A.CrawlType,B.ContentSourceID,A.Status,A.StartTime,A.EndTime,CAST(A.SuccessCount as int) as SuccessCount,A.ErrorCount,A.WarningCount from MSSCrawlHistory as A with(nolock) inner join (select distinct CrawlID,ContentSourceID from MSSCrawlContent with(nolock)) as B on A.CrawlID = B.CrawlID WHERE A.ProjectID = 1 AND DATEDIFF([Day], A.StartTime, GETDATE()) <= 7 ORDER BY A.EndTime DESC

Just thinking in the task, this may seems better, but think about the possibility that committed data may be a requirement.

Finally, should you TSQL your sharepoint database? I would say no, as:

• You may affect overall response of your environment
• Unless you have enough information through our open specification program you may conflict with you EULA acceptance.

What would be better, querying or using the object model?

• Working with the object model gives you supportability for your code as you won't have breaking changes (backward compatibility) or you will be advised about deferred/obsolete code and have migration paths. something that using TSQL you won't have.

Namaste!