Rajendra Gupta

FILESTREAM in SQL Server

January 11, 2019 by

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.

  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

    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.


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.

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

FILESTREAM in SQL Server
Managing data with SQL Server FILESTREAM tables
SQL Server FILESTREAM Database backup overview
Restoring a SQL Server FILESTREAM enabled database
SQL Server FILESTREAM database recovery scenarios
Working with SQL Server FILESTREAM – Adding columns and moving databases
SQL Server FILESTREAM internals overview
Importing SQL Server FILESTREAM data with SSIS packages
SQL Server FILESTREAM queries and Filegroups
Viewing SQL Server FILESTREAM data with SSRS
SQL Server FILESTREAM Database Corruption and Remediation
Export SQL Server FILESTREAM Objects with PowerShell and SSIS
SQL FILESTREAM and SQL Server Full Text search
SQL Server FILESTREAM and Replication
SQL Server FILESTREAM with Change Data Capture
Transaction log backups in a SQL FILESTREAM database
SQL FILESTREAM Compatibility with Database Snapshot, Mirroring, TDE and Log Shipping
SQL Server FILETABLE – the next generation of SQL FILESTREAM
Managing Data in SQL Server FILETABLEs
SQL Server FILETABLE Use Cases

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
13,742 Views