Files, BLOBs, NTFS and SQL Server 2008 – Age old database dilemma solved!
One of the most exciting new features in SQL Server 2008 is the ability to store files and BLOBs directly in the file system, while maintaining transactional consistency with a SQL Server 2008 database. SQL Server 2008’s new FILESTREAM attribute for VARBINARY data type solves the age old dilemma facing developers and IT Pros: Is it better to store files directly in a database or store them in the file system with path and filenames stored back in tables to maintain the relationship with the database.
Both approaches have pros and cons: Storing files directly in a database secures information using database permissions and enables this data to participate in transactions and backups. This approach, however, does not provide the best performance and storing large files can add significant size to a database. On the other hand, storing files in the file system while using tables and columns to link them to a database is less taxing on a database and provides more flexibility and performance since NTFS is very efficient at managing files. This approach, however, leaves files vulnerable to corruption or deletion, plus files cannot participate in transactions and backups which can leave a database unstable. Another issue in storing files outside the database is that the logic for linking these files to the database must be maintained (and duplicated) in any application that uses this data. This introduces maintenance issues that can become burdensome and complex as the number of applications connecting to the database increases and as the number of people or teams involved in managing this data increases.
SQL Server 2008 solves this dilemma elegantly. VARBINARY’s new FILESTREAM attribute provides the best of both worlds. By simply specifying FILESTREAM for VARBINARY data types, SQL Server 2008 shackles data (i.e. files or BLOBs) for that column to the table, while leveraging Windows NTFS to actually store and retrieve the data outside the database. This shackled data resides in the file system yet is managed entirely by SQL Server 2008. This data is protected by the database permissions and participates fully in transactions and backups as expected. Outside the database, users see this data as regular files in the file system that cannot be modified or deleted without admin rights since they are owned by SQL Server.
Here are a few interesting links discussing FILESTREAM:
Paper: Managing Unstructured Data with SQL Server 2008 (Whitepaper)
