In SQL Server many developers stores binary file data such as audio, video, or simple images. They have one in common, they will use the blob data (varbinary) to store those kinds of data. The problem with this is, that you are limited to 2GB at this. It also slows down the database performance or maybe of your complete database system.

Filestream to the rescue

The FILESTREAM (Yes capital letters) allows us to store large binary data onto the filesystem itself. The good news about it, is we don’t have a limitation of 2 GB as in the BLOB type. So the limitation is set at the filesystem level itself. Technically the SQL Server can access the files using the NTFS streaming API. So it will get the benefit of this streaming API while accessing the binary data.

Note: FILESTREAM is not a SQL Server data type to store data

Normally if we store the data in the BLOB data type, that will be stored in the Primary filegroup only. Not in the FILESTREAM. For this, we need to define a new filegroup called FILESTREAM. Then we need to define a table having varbinary(max) column with the FILESTREAM attribute.

This will allow the SQL Server to store the data in the file system for these data type. When we access the documents that are stored in the file system using the FILESTREAM, we do not notice any changes in accessing it. It looks similar to the data stored in a traditional database.

For example

Below you can traditional database storing the employee photo in the database itself.

After the change to a FILESTREAM, then it will look like this.

In the above illustration, you can see the documents are stored in the file system, and the database has a particular filegroup ‘FILESTREAM’. You can perform actions on the documents using the SQL Server database itself.

The next advantage of the FILESTREAM is that it does not use the buffer pool memory for caching these objects. So when we use caching of large objects anyway, it will cause issues for normal database processing. Therefore, FILESTREAM provides caching at the system cache providing performance benefits without affecting core SQL Server performance.

Enabling the FILESTREAM feature in SQL Server

We can enable the FILESTREAM feature differently in SQL Server.

1. During Installation: You can configure FILESTREAM during the SQL Server installation. However, I do not recommend doing it during the installation because we can later enable it as per our requirements
2. SQL Server Configuration Manager: In the SQL Server Configuration Manager (start -> Programs -> SQL Server Configuration Manager), go to SQL Server properties
3. In the SQL Server properties, you can see a tab ‘FILESTREAM’.

Click on ‘FILESTREAM’, and you get the below screen. Here you can see that this feature is not enabled by default

• Put a tick in the checkbox ‘Enable FILESTREAM for Transact-SQL access’
• We can also enable the read\write access from the windows for file I/O access. Put a tick on the ‘Enable FILESTREAM for file I/O access’ as well
• Specify the Windows share name and allow remote client access to this FILESTREAM data

• Click Apply to activate the FILESTREAM feature in SQL Server. You will get a prompt to restart the SQL Server service. Once we have enabled FILESTREAM access and restarted SQL Server, we also need to specify the access level using SSMS. We need to make changes in sp_configure to apply this setting.
• Run the below command to show the advanced option in sp_configure.

Run the command sp_configure to check all available options. We can see all the available options now. Since we are interested in FILESTREAM only, I highlighted this particular option.

Below is the option in the sp_configure

We need to specify the value from 0 to 2 while enabling SQL Server FILESTREAM using the query.

You can run the command to specify the access level. In the below command, you can see that we have specified SQL Server FILESTREAM access level as 2.

If you do not enable FILESTREAM using the SQL Server Configuration Manager, You can get the error message

‘FILESTREAM feature could not be initialized. The operating system Administrator must enable FILESTREAM on the instance using Configuration Manager.’

We can also provide this access level using the SSMS. Right-click on the server instance and go to properties.

Now click on Advanced, and you can see a separate group for SQL Server FILESTREAM.

In this group, we can define the SQL Server FILESTREAM access level from the drop-down option as shown below.

In this GUI mode, we have three options listed. The following table shows the mapping between GUI and t-SQL options for SQL Server FILESTREAM access level.

Important points to consider while using the SQL Server FILESTREAM feature

• We can use the SELECT, INSERT, UPDATE, and DELETE statements similar to a standard database query in FILESTREAM
• We should use FILESTREAM if the object size is larger than 1 MB on average
• Each row should have a unique row ID to use this functionality, and it should not contain NULL values
• We can create the FILESTREAM filegroup on the compressed volume as well
• In the Failover clustering, we need to use a shared disk for the FILESTREAM filegroup
• We can add multiple data containers in the FILESTREAM filegroup
• We cannot encrypt FILESTREAM data
• You cannot use SQL logins with the FILESTREAM container

Closing notes

That’s all of it. So I hope you could understand the FILESTREAM Feature a little bit, and maybe you will consider it in the near future of your (database) projects. Please leave me a comment if you like to share your thoughts.