SQL Server 2008 -- File Stream (4)

● Summary

●  When to use

1.       Objects that are being stored are, on average, larger than 1 MB

2.       Fast read access is important.

3.       You are developing applications that use a middle tier for application logic.

4.       For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

●  Storage

1.       When a table contains a FILESTREAM column, each row must have a unique row ID.

2.       FILESTREAM data containers cannot be nested.

3.       When you are using failover clustering, the FILESTREAM filegroups must be on shared disk resources.

4.       FILESTREAM filegroups can be on compressed volumes.

● Security

1.       FILESTREAM data is secured just like other data is secured: by granting permissions at the table or column levels.

2.       Encryption is not supported on FILESTREAM data.

3.       When the database is closed, the physical data container is fully available and subject to Windows security check.

4.       Recommend that you secure directories that contain FILESTREAM data so that the files cannot be accidentally altered or deleted.

● Management

1.       FILESTREAM is implemented as a varbinary(max) column and integrated directly into the Database Engine, most SQL Server management tools and functions work without modification for FILESTREAM data.

2.       All backup and recovery models with FILESTREAM data, and the FILESTREAM data is backed up with the structured data in the database.

3.       If you do not want to back up FILESTREAM data with relational data, you can use a partial backup to exclude FILESTREAM filegroups.