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
RECONFIGURE
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!!
Regards
Piyush Bajaj
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
