Shortly after releasing SQL Server 2008 the Microsoft SharePoint Team Blog announced the SQL Server 2008 support for SharePoint Products and Technologies. This article discusses some aspects of using SQL Server 2008 instead of SQL Server 2005 together with unstructured data.
Business value
Due to the explosion in the volume of digital data and the reduction in the cost of storage hardware and memory many organizations store a lot of unstructured data in a database. Unstructured data contains documents, digital images, audio or videos and is also called unstructured binary files or binary large object (BLOB). Since digital data becomes more and more business critical Microsoft serves a company needs with SQL Server 2008.
SQL Server 2008 is a “business application that merges traditional relational data structures with unstructured digital content” [1]. It
- meets the need of relational and non-relational data storage,
- reduces the cost of managing the data and
- has a better performance compared to SQL Server 2005 (talking about unstructured data) and
- it is not limited to the maximum of 2GB per file compared to SQL Server 2005.
In order to get performance improvements you can externally store digital data. The metadata is still stored in the database. An ID references the external file.
SQL Server 2008 has a built in feature to externally store data. SQL Server 2005 doesn’t have that feature. But together with SharePoint 2007 you can use a different approach to store data on the file system.
SharePoint 2007
Please remember that you need Service Pack 1 for Microsoft Office SharePoint Server 2007 and Windows SharePoint Services 3.0 if you want to use Windows Server 2008 and / or SQL Server 2008. There are two possibilities to externally store digital data with SharePoint 2007:
- FileStream (built in feature with SQL Server 2008)
- External BLOB Storage – EBS (for use with SQL Server 2005)
The second possibility doesn’t need the installation of SQL Server 2008. It also works with SQL Server 2005 because the decision where to store the data is not made by the SQL Server. The first possibility takes advantage of a new SQL Server 2008 feature.
FileStream
SQL Server 2008 introduces a new feature called FileStream. With FileStream you can externally store BLOB's. FileStream is an attribute you can set on a varbinary column. Your external data is also included in backup and restore.
If you want to use FileStream in general you have to follow 3 steps:
- Enable support for FileStream on the SQL Server instance.
- Create a database with a FileGroup and FileStream.
- Create a column and activate FileStream.
Now you could try to activate FileStream after SharePoint has created the content databases. Since modifying databases in SharePoint is not supported by Microsoft this is not a good idea. Further releases a SharePoint will definitely support this feature but for now this is not an option. (So I didn't test that.)
The next few days I will write about External BLOB Storage and ISPExternalBinaryProvider which will be published this week.
[1] Microsoft: Managing Unstructured Data with SQL Server 2008, Page 1
