Wednesday, February 16, 2011

SharePoint Foundation BLOB storage


If you download and install SharePoint Foundation 2010 from the Microsoft web site and use it with the default options you’ll end up storing the content on SQL Server 2008 Express which is limited to 4GB database sizes. if you work with SharePoint Foundation 2010 on SBS 2011 you’ll end up storing the content on SQL Server 2008 Express R2 which allows databases up to 10GB in size. One of the options beyond that is Binary Large Object Storage or BLOB storage.

So what’s BLOB storage? Many SharePoint sites are full of things like pictures, documents and the like which can vary in size from a few kilobytes up to many megabytes. The larger these ‘binary objects’ become the more size they chew up in the SQL database in which they are stored. SQL databases are typically not the best places for storing large binary data. In order to reduce the size of the SQL databases and improve their performance you can enable BLOB storage which basically allows this large data to be stored on the file system rather than in the database, yet still take advantage of many of the features that databases provide.

Sounds good eh? Well yes and no (and I think more so no for SharePoint). The good thing is that it reduces the total size of your SharePoint database and allows it to break the 10GB limit. However, I see plenty of downsides:

- BLOB storage adds complexity to SharePoint. How do you backup and restore BLOBs? How do you configure and enable it? What happens when you need to migrate data?

- If you work with a lot of files and BLOB storage you are going to end up with lots of orphaned BLOB stores which will require tidy up.

- You cannot assume that your current implementation using the present version of the software will be compatible with future versions of Microsoft Office or SharePoint Foundation (this straight from the Microsoft technical document).

- No utility is available for moving BLOB data from an existing content database into the external BLOB store. After you enable BLOB storage on SQL server on new BLOB data will end up in the BLOB storage location.

- Some backup and restore functions in SharePoint Foundation operate on the content databases but not on the external BLOB store. You must handle the backup and restore of external BLOB data differently to standard SharePoint.

- Microsoft recommends that if the content databases become larger than 16GB you upgrade to the full version of SQL Server that provides for unlimited database sizes.

- Remote BLOB storage is most useful for large file storage, typically in environments that require archiving.

- Once you enable remote BLOB storage all BLOB data ends up there. This means that if you have lots of small BLOB files accesses regularly you may experience latency.

- SQL BLOB Storage using FILESTREAM with SharePoint Foundation can only be to the local drives and does not support snapshot backups.

On balance then I think that if you are approaching the 10GB limit with the free version of SQL Server 2008 Express R2 it’s time to think about upgrading to a full version of SQL rather than implementing BLOB storage. Too me, from what I see, there is just too much downside.

You have been warned.