Blog do Ezequiel SQL Server Insights
Recently I was asked to advise a customer on a FILESTREAM implementation he was devising, and part of that ended up becoming this blog post that I hope can be of some help for you.
In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determines whether you should use database storage or file system storage. So, in a nutshell, these are the typical scenarios where this feature is best suited:
Overall, the main advantages of using FILESTREAM over other options are:
Still, a few restrictions apply to FILESTREAM implementations:
For smaller objects (less than 1MB), storing varbinary(max) BLOBs in the database can provide better streaming performance, as seen in the chart below, from Paul S. Randal White Paper on FILESTREAM storage. I highly recommend starting with this if you are assessing the implementation of FILESTREAM in your infra-structure. Also, another good place to start is the White Paper on Managing Unstructured Data with SQL Server 2008.
Anyhow, the following table (from the same source) provides a nice overview on the benefits and drawbacks of the several methods of handling BLOBs from SQL Server 2008 onwards:
To squeeze every ounce of performance from BLOB implementations using FILESTREAM requires a few extra steps. Here are some of the best practices which can be leveraged for that purpose:
I still have a few more notes on this subject, but those will wait for a new post in a few days.
Other material on the subject: FILESTREAM Best Practices The SQL Swiss Army Knife #4 - Making sense of FILESTREAM containers
Until next time!
Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.
Thank you for submitting this cool story - Trackback from progg.ru
Where you say:
Always create the FILESTREAM data container which will be used by the FILESTREAM enabled database in a separate volume.
I agree with the concept but as a filestream is created in the default data folder (unless using a specific script) would it not be helpful if the default filestream location could be defined?
We have an issue with Biztalk application using filestream where the application is unable to store more than 2 million records for some reason. Are there any configuration settings that could be causing this issue ? The Filestream column stores very small files (average of 3KB).
You indicate not to encrypt the filestream data. Since TDE does not support encrypting the data, that seems like bad advice if you are trying to secure your system. If you have an AES enabled processor on your system (almost everything intel and AMD has shipped in the last 4 years is) then any decent encryption software should not introduce significant overhead and encryption should be used.
We do not support encryption on Filestream data.
For security purposes, given that only the account under which the SQL Server service account runs is granted NTFS permissions to the FILESTREAM container, we recommend that no other account be granted permissions on the data container.
Great post! It helps! Thank you!