FILESTREAM storage in SQL 2008

Background

Of Late, I was involved in a discussion where people asked on methodology to be adopted for storing images as a part of their reporting project using SQL reporting services. One group believed in storing images on file system and storing the links alone in SQL DB whereas other group was arguing for storage of images themselves in the SQL DB. Together, we discussed on the pros and cons of each approach and given below were various points that came up

Advantages of storing images on the db

1. Images will not get lost. Migration of database to new server involves migration of image files too.
2. All the database security gets applied on image as well. Storing on file system requires this to be done manually

Disadvantages of storing images on db

1. Images are mostly static and they increase the overall size of db a lot. Backups of db requires backing up of static images as well. On the other hand, file systems can undergo differential backup storing only changed information
2. Very unlikely to be cached in the user's browser, so performance might get affected
3. IIS will have to retrieve the image from DB, so more bandwidth used between IIS Server and SQL Server.

FILESTREAM access - merging advantages of both approaches

SQL 2008 has introduced FILESTREAM access which combines the advantages of both of the above methodologies .

FILESTREAM stores images on the OS file system and it can be directly accessed from webpages provided you set correct filestream access level.It can also be accessed from T-SQL for all DML operations. In FILESTREAM file operations are transactional and hence you cannot delete or rename FILESTREAM files through the file system.

FILESTREAM is implemented as a varbinary(max) column in which the data is stored as BLOBs in the file system. As they are stored in file system, the normal size restriction of 2 GB does not apply here and size is restricted only by the volume size of the file system.

FILESTREAM data is secured by granting permissions at the table or column levels. If a user has permission to the FILESTREAM column in a table, the user can open the associated files.It uses the NT system cache for caching file data and reduces any effect that FILESTREAM data might have on Database Engine performance

Refer the below article to know more on FILESTREAM data and steps on how to make db ready for FILESTREAM access

http://technet.microsoft.com/en-us/library/bb933993.aspx