FILESTREAM in SQL Server
FILESTREAM in SQL Server
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. 1234 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. 1234 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)
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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolFILESTREAM in SQL Server
January 11, 2019 by Rajendra Gupta 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 dataClick 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. 1234 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. 1234 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 containerConclusion
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 Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022