Recently I received an inquiry regarding the use of SQL Server FileStreams. The database was designed on one machine, and restoring to a new machine made it difficult to get the file streams to work again.
If you follow this link to Microsoft they provide an overview of what a filestream data type is and how it is implemented.
http://technet.microsoft.com/en-us/library/bb933993%28v=sql.105%29.aspx
In short, a filestream data type is similar to having a varbinary(max) or binary(max) data type in your table definition. In comparison, the filestream data is accessed in a different manner as far as the database engine is concerned. Manipulating the data is performed by the operating system instead of SQL Server, utilizing the Windows Cache instead of SQL Server Cache.
Just like varbinary(max) and binary(max) datatypes, there SQL Server files contain the actual data. The only difference is that the SQL Engine is not used to directly manipulate the binary contents. Some key benefits from using a filestream data type are:
- Data is contained in the SQL Server file objects, and therefore backed up along with the database
- Persistence of data is integrated within an SQL transaction with all the benefits a transaction provides
- SQL Server Cache is not used for manipulating the files
- If the data is on a clustered database server it is available when the host is transferred
Because the binary data is contained in an SQL Server File, it is recommend that a separate file group and file be used for filestream data so that it can be excluded from a backup process. Of course you can only exclude that data by performing filegroup backups which make recovery more difficult.
IMHO, filestreams are an attempt to fix a symptom rather than fixing a problem. To me, because BLOB (Binary Large Object) data is better maintained outside of your database engine. I’ll pick up on that thread tomorrow.
Back to the original question, why they are having problems getting filestreams to work. The most likely reason is credentials. Just like any database file, the account that provides credentials for the SQL Server engine must have adequate windows directory permissions. Even though the data is managed through the Windows service, it is contained in an SQL Server file, and credentials are supplied by SQL Server for the manipulation.
Do you like BLOB data in your relational database storage, regardless of how it is maintained? That’s where I am going tomorrow. You can beat me to the punch with your comment today or by sending an Email to btaylor@sswug,.org.
Cheers,
Ben