In this article, we are going to learn how we can manage the FILSTREAM filegroup of SQL Database. I am going to cover the following topics.
- Configure the FILESTREAM feature in SQL Server.
- Create FILESTREAM filegroup using SQL Server management studio and T-SQL statements.
- Add multiple FILESTREAM files to the Filegroup.
First, let us enable the FILESTREAM feature.
Enable the FILESTREAM feature
We can enable the FILESTREAM using the SQL Server configuration manager tool. To do that, Open the SQL Server configuration manager 🡪 Right-click on the SQL Server instance on which you want to enable the FILESTREAM feature, and click Properties.
In the SQL Server Properties dialog box, click on FILESTREAM and tick the following options.
- Enable FILESTREAM for Transact-SQL access
- Enable FILESTREAM for file I/O access
- Specify the desired name of windows shares in the Windows share name textbox
- Allow remote client access to FILESTREAM data
Screenshot
Click OK to save the configuration parameters. Once changes are applied, restart the SQL Services.
Configure the FILESTREAM access level
Once the FILESTREAM has been enabled on the SQL Server instance, we must configure the FILESTREAM access level. The valid configuration values are following:
Description of option | Value in T-SQL command | Option in SQL Server management studio |
FILESTREAM access is disabled | 0 | Disabled |
FILESTREAM access for T-SQL queries | 1 | Transaction SQL access enabled |
FILESTREAM access for T-SQL queries and windows streaming | 2 | Full access enabled |
To set the values of the above parameters using SQL Server management studio, open SSMS and connect to SQL Server instance. Right-click on the instance and select Properties.
On the Server Properties dialog box, click on Advanced. Select Full access enabled option from FILESTREAM Access Level drop-down box.
Click OK to save the configuration and restart the SQL Server services.
Alternatively, you can run the sp_configure command to set the file access level for FILESTREAM. The configuration parameter to set the access level is filestream_access_level. If you want to enable the FILESTREAM Access for the T-SQL queries and windows streaming, the query is written as follows.
|
1 2 3 4 5 6 |
use master go exec sp_configure 'filestream_Access_level', 2 reconfigure |
Once a parameter is set, you must restart the services. Once services are restarted, run the sp_configure command to view the values of the filestream_access_level parameter.
|
1 2 3 4 5 |
use master go exec sp_configure |
Query output
As you can see, the value of the filestream_access_level parameter is two (2). Now, let us create a FILESTREAM filegroup.
Create a FILESTREAM filegroup using SQL Server Management Studio
To create FILESTREAM filegroup using SSMS, open it and connect to the SQL Server database engine 🡪 Right-click on EltechDB database 🡪 Select Properties.
In the Database Properties dialog box, click on Filegroup. Click on Add Filegroup. This will add a row in a FILESTREAM grid view. Specify the following parameters in the grid-view.
- Name: Specify the Name of the Filegroup.
- FILESTREAM files: Total number of FILESTREAM files. The default value is zero (0).
- Read-Only: If you want to make the new Filegroup a read-only filegroup, then tick the Read-Only column.
- Default: If you want to make the new Filegroup as a default FILESTREAM filegroup, then tick the Default column.
We are creating a filegroup named EmployeeDocument, so the Database Properties dialog-box looks like the following:
Now we must specify the path of the FILESTREAM container in which you want to store the files. To do that, click on Files in Database Properties. On the files screen, configure options as follows:
- Logical Name: Specify the name of the FILESTREAM container. In this demo, the file name is DF_EmployeeDocument.
- File Type: Select FILESTREAM Data from the drop-down box.
- Filegroup: Select the FILESTREAM filegroup. In this demo, the Filegroup name is EmployeeDocument.
- Path: Specify the location of the directory in which we are storing the files. In this demo, the location of the D:\EltechDB\EmployeeDocuments.
The Database Properties dialog-box looks like the following:
Click OK to create a FILESTREAM container. Now, let us understand how to create a FILESTREAM container using T-SQL.
Create a FILESTREAM filegroup using T-SQL query
We can use ALTER DATABASE ADD FILEGROUP statement. We are adding a FILESTREAM filegroup in the SQL database; therefore, we must specify CONTAINS FILESTREAM keyword. It must be specified after the name of the Filegroup.
The syntax to add a FILESTREAM filegroup is shown below:
|
1 2 3 4 5 6 |
USE [master] GO ALTER DATABASE [DB_Name] ADD FILEGROUP [Filegroup_Name] CONTAINS FILESTREAM GO |
In the syntax,
- Db_name: Specify the database name in which you want to create the FILESTREAM filegroup.
- Filegroup_name: Specified desired name of the Filegroup.
We are adding a filegroup named EmployeeDocument in the EltechDB database. The ADD FILGROUP statement is following
|
1 2 3 4 5 6 |
USE [master] GO ALTER DATABASE [EltechDB] ADD FILEGROUP [EmployeeDoucment] CONTAINS FILESTREAM GO |
Now, we must specify the location of the file stream container. We are using the ALTER DATABASE ADD FILE TO FILEGROUP statement. To add a file, run the following T-SQL query.
|
1 2 3 4 5 6 |
USE [master] GO ALTER DATABASE [EltechDB] ADD FILE ( NAME = N' DF_Employeedocuments', FILENAME = N' D:\EltechDB\EmployeeDocuments\' ) TO FILEGROUP [EmployeeDoucment] GO |
Now, run the following query to view the details of the FILESTREAM filegroup.
|
1 2 3 4 5 6 7 8 9 10 11 |
USE eltechdb go SELECT df.NAME AS [Logical Name], df.size / 128 AS [File Size], fg.NAME AS [FileGroup Name], df.physical_name AS [Physical Path] FROM sys.database_files AS df INNER JOIN sys.filegroups AS fg ON df.data_space_id = fg.data_space_id; |
Output:
As you can see, the FILESTREAM filegroup named EmployeeDocument has been created, and a data file named DF_EmployeeDocuments has been added.
Add multiple FILESTREAM data files to the FILESTREAM filegroup
We can use ALTER DATABASE ADD FILE TO FILEGROUP statement to add a secondary FILESTREAM data file to Filegroup in the database. Suppose we want to add a data file named DF_EmployeeContract in the EltechDB database. The location of the FILSTREAM data file is D:\EltechDB\EmployeeContract. The query is as the following.
|
1 2 3 4 5 6 |
USE [master] GO ALTER DATABASE [EltechDB] ADD FILE ( NAME = N' DF_EmployeeContracts', FILENAME = N'D:\EltechDB\EmployeeContracts' ) TO FILEGROUP [EmployeeDoucment] GO |
Run following query to view the details of datafile.
|
1 2 3 4 5 6 7 8 9 10 11 |
USE eltechdb go SELECT df.NAME AS [Logical Name], df.size / 128 AS [File Size], fg.NAME AS [FileGroup Name], df.physical_name AS [Physical Path] FROM sys.database_files AS df INNER JOIN sys.filegroups AS fg ON df.data_space_id = fg.data_space_id; |
Output
As you can see, the datafile DF_EmployeeContracts has been added to the EltechDB SQL database.
Summary
In this article, we learned how we could manage the filegroups in SQL Database. We learned the following scenarios:
- How to enable the FILESTREAM feature.
- Add a FILESTREAM filegroup using SSMS and T-SQL queries.
In the next article, we will learn the different ways to change the location of the FILESTREAM filegroups.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022