SQL Server FILESTREAM attribute Implementing – PART I

Hey Folks,

In my previous blog post, I already explained what FILESTREAM is; if you would have missed then check this URL

Let me tell you some advantages of using SQL Server FILESTREAM attribute:

  • When using FILESTREAM, structured and unstructured data are logically connected while physically separated, and cause of this unstructured data does not impose any query performance since it’s not physically stored in-line with table data.
  • While backing up the database, it automatically includes all the BLOB data from the file system, or user can also exclude BLOB as it is contained in its own database filegroup.

Before going further, first understand how to enable FILESTREAM in your machine, if you had not enabled during the installation of SQL Server instance:

  • Go to SQL Server Configuration Manager, select Services.
  • Then right click the instance, and select Properties, go to FILESTREAM tab and then:



  • Click Ok.

How to enable FILESTREAM for the Server Instance?

Well to work with FILESTREAM attribute it’s necessary that the machine and server instance access levels should match each other. So let’s enable it using T-SQL statement:

exec sp_configure filestream_access_level,2


Here sp_configure is a stored procedure using which we are changing the values of filestream_access_level, the value can be different also like:

0: Disables the FileSream completely

1: Enables FileSream for T-SQL only

2: Enables FileStream for T-SQL, and also include local or remote file I/O streaming access as enabled for the machine

Or, there is also an option to enable it via SSMS:

  • Right click the instance, go to select Properties




  • Click Advanced, and then select FILESTREAM Access Level as Full access enabled, and click Ok:


You might face this error while enabling the FILESTREAM sometimes:


‘FILESTREAM is not supported on WOW64’, which I had faced recently.

The solution for this problem is, if we want to use FILESTREAM bit versions of SQLServer and Operating System should be same. It means we cannot enable FILESTREAM on a 32-bit version of SQL Server running on a 64-bit operating system or vice-versa.

Well this was all about how to enable FILESTREAM attribute. So please don’t hesitate to ask any queries related to it.

And in the next part, I will explain how to implement this FILESTREAM in real-time environment.

So please be tuned!!



Piyush Bajaj

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook


About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published.