SQL Server FILESTREAM - Is it for you?

SQL Server 2008 introduced a new feature called FILESTREAM, which in simple terms is an attribute for varbinary(max) data type. It allows the unstructured data such as BLOBs beyond a certain size to be stored on the file system instead of DB. This brings in new choices for storing BLOB data.

FILESTREAM in a few words:
- Stores BLOBs in the file system, but through the database thereby providing transactional consistency
- BLOB size is limited only by the available file storage space [Previously, BLOB size limited to 2gb]
- The underlying file system storage mapping can be to SAN or network paths.
- The BLOBs stored in file system can be accessed through DB, directly accessing file folds on OS, or by streaming the files through a network share.

FILESTREAM in more detail:
- The data belonging a DB instance enabled with FILESTREAM stores data on the file system in what is called a Data Container. The data container has the files created using cryptic names (based on guid) and maintained by the SQL Server, as the BLOB data undergoes change. Since the data container is also accessible through the file system, any incorrect tweaking of the container could lead to data corruption and render the container useless. This is a risk to monitor all the time and take preventive actions to avoid.
- We could create multiple data containers and map them to the SQL Server as filegroups thereby providing load balancing with multiple data storage sinks.

Some of the key questions from a broader deployment, maintainability, integrity perspective:

1. How to handle data recovery in case of data container corruption?
2. Can we just backup the DB without the file system data? [assuming data is in SAN, which need not be moved]
3. I would like to back up or restore the DB and just remap the filegroup path information [that maps to SAN]. Is this possible?

Read the reference articles for more info.

References:
- http://msdn.microsoft.com/en-us/library/cc949109.aspx
- “How to: Set Up FILESTREAM on a Failover Cluster” ( http://msdn.microsoft.com/en-us/library/cc645886.aspx).
- http://research.microsoft.com/apps/pubs/default.aspx?id=64525