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.
EXEC sp_configure 'show advanced options'
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
Filestream access level
We need to specify the value from 0 to 2 while enabling SQL Server FILESTREAM using the query.
FILESTEAM access level
Value 0 shows that FILESTREAM access is disabled for this
Value 1 enables the FILESTREAM access for the SQL query.
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.
EXEC sp_configure filestream_access_level, 2
RECONFIGURE WITH OVERRIDE
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.
Equivalent t-SQL option
EXEC sp_configure filestream_access_level, 0
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
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
- Deploy Azure Data Lake Analytics database using the U-SQL scripts - February 23, 2021
- Join database tables using U-SQL scripts for Azure Data Lake Analytics - February 17, 2021
- Writing U-SQL scripts using Visual Studio for Azure Data Lake Analytics - February 11, 2021