Shortly after releasing SQL Server 2008 the Microsoft SharePoint Team Blog announced the SQL Server 2008 support for SharePoint Products and Technologies. This is part 2 of an article discussing some aspects of using SQL Server 2008 instead of SQL Server 2005 together with unstructured data.

The last time I talked about the business value of using SQL Server 2008 instead of SQL Server 2005 and I talked about the FileStream attribute in general.

External BLOB Storage and ISPExternalBinaryProvider

With the Windows SharePoint Services 3.0 hotfix package dating May 25, 2007 [1] Microsoft introduced an external storage API for storing files on an external storage device other than Microsoft SQL Server. This hotfix was later included in Service Pack 1.[2] There is also a technical documentation on how to use the API [3].

Overview

The EBS Provider interface is part of the Windows SharePoint Services 3.0 and is therefore independent of the database management system. The external BLOB storage runs parallel to the site’s SQL Server content database. The content database stores the sites structured data as well as the metadata of the unstructured, externally stored data. An ID references the external content.

The image is part of an article by Microsoft: Overview of External BLOB Storage in Windows SharePoint Services.

Limits and Trade-Offs

The Windows SharePoint Services 3.0 expose a BLOB store provider (EBS Provider) interface that adds a lot of complexity to your SharePoint farm affecting backup and restore, garbage collection and other aspects of your deployment. Due to its simplicity by design it has the following limits and trade-offs [4]:

• You can’t update or edit external data. In order to keep file-level transactional consistency between the SQL Server content databases every edit and update creates a new file on the file system. A garbage collector has to be implemented.
• You have to manually move existing content from the database to the external storage. Either you have to do a site level backup and restore or you have to update every file. In this case the old version is deleted from the database.
• You can’t use your existing disaster recovery plan. You can’t use backup or restore operations because some of them doesn’t include externally stored content.
• You have to enable the EBS on every web front-end server. The EBS provider has to be activated at farm level which has impacts on every web application and site collection.
• Errors or system latency created by the EBS Provider have a direct impact in performance to the site in general.
• The interface will be different within the next full-version release of SharePoint Products and Technologies. Because of that you can't assume that your implementation is still working in the future.

Implementation

If you want to use the API you have to implement

• an EBS provider
• and a Garbage Collector.

If you want to know how there is a great article you can find at Kyle's Blog.

Summary

External BLOB storage with SharePoint 2007 SP1 and SQL Server 2005 or SQL Server 2008 is not recommended right now. But in future versions it will be a great feature. Independent of external file storage the use of SQL Server 2008 together with SharePoint 2007 is reasonable because of the new features. Furthermore you will be prepared for the next full-version release of SharePoint (or maybe earlier with a new Service Pack and or infrastructure update).

[1] http://support.microsoft.com/kb/937901/en-us

[2] http://kyletillman.net/blog/post/SharePoint_External_Binary_Store-PartI.aspx

[3] http://msdn.microsoft.com/en-us/library/bb802976.aspx