This article will discuss SQL Server FILESTREAM including installation, configuration, enabling and general considerations.
We may need to store a different kind of data in our SQL Server database apart from the regular table-based data. This data may be in multiple forms such as a document, images, audio or video files. You might have seen people using BLOB data to store these kinds of data, but you can save only up to 2 GB using this. This kind of data also slow down the performance of your database system since it tends to be large and takes significant system resources to bring it back from the disk.
FILESTREAM, in SQL Server, allows storing these large documents, images or files onto the file system itself. In FILESTREAM, we do not have a limit of storage up to 2 GB, unlike the BLOB data type. We can store large size documents as per the underlying file system limitation. SQL Server or other applications can access these files using the NTFS streaming API. Therefore, we get the performance benefit of this streaming API as well while accessing these documents.
Note: FILESTREAM is not a SQL Server data type to store data
Traditionally if we store the data in the BLOB data type, it is stored in the Primary file group only. In SQL Server FILESTREAM, We need to define a new filegroup ‘FILESTREAM’. We need to define a table having varbinary(max) column with the FILESTREAM attribute. It allows 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.
Let us understand the difference in storing the images in the database or the file system using SQL Server FILESTREAM.
Below you can traditional database storing the employee photo in the database itself.
Now let us look at the changes in this example using the SQL Server FILESTREAM feature.
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.
One more advantage of the FILESTREAM is that it does not use the buffer pool memory for caching these objects. If we cache these large objects in the SQL Server memory, 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.
- 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
-
SQL Server Configuration Manager: In the SQL Server Configuration Manager (start -> Programs -> SQL Server Configuration Manager), go to SQL Server properties
In the SQL Server properties, you can see a tab ‘FILESTREAM’.
Click on ‘FILESTREAM’, and you get 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 for 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.
|
1 2 3 4 |
USE master Go EXEC sp_configure 'show advanced options' Go |
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
Option | Min value | Max Value |
Filestream access level | 0 | 2 |
We need to specify the value from 0 to 2 while enabling SQL Server FILESTREAM using the query.
FILESTEAM access level | Description |
0 | Value 0 shows that FILESTREAM access is disabled for this |
1 | Value 1 enables the FILESTREAM access for the SQL query. |
2 | Value 2 enables the FILESTREAM access for the SQL query and Windows streaming. |
You can run the command to specify the access level. In below command, you can see that we have specified SQL Server FILESTREAM access level as 2.
|
1 2 3 4 |
EXEC sp_configure filestream_access_level, 2 GO RECONFIGURE WITH OVERRIDE GO |
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.
GUI Option | Equivalent t-SQL option | Description |
Disabled | EXEC sp_configure filestream_access_level, 0 | Disable access |
Trasact-SQL access enabled | EXEC sp_configure filestream_access_level, 1 | Access for t-SQL only |
Full access enabled | EXEC sp_configure filestream_access_level, 2 | Full access (t-SQL and windows streaming) |
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
Conclusion:
In this article, we took the overview the FILESTREAM feature in SQL Server and explored ways to enable it at the SQL Server instance level. In the next article, we will create a database with SQL Server FILESTREAM data and perform multiple operations on it.
Table of contents
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023