Nisarg Upadhyay
View configuration of SQL Server instance

Manage FILESTREAM filegroups of SQL Databases

June 3, 2022 by

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.

  1. Configure the FILESTREAM feature in SQL Server.
  2. Create FILESTREAM filegroup using SQL Server management studio and T-SQL statements.
  3. 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.

SQL Server configuration manager

In the SQL Server Properties dialog box, click on FILESTREAM and tick the following options.

  1. Enable FILESTREAM for Transact-SQL access
  2. Enable FILESTREAM for file I/O access
  3. Specify the desired name of windows shares in the Windows share name textbox
  4. Allow remote client access to FILESTREAM data

Screenshot

Enable filestream feature

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.

Open SQL Server instance properties

On the Server Properties dialog box, click on Advanced. Select Full access enabled option from FILESTREAM Access Level drop-down box.

Enable FILESTREAM Access level

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.

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.

Query output

View configuration of SQL Server instance

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.

SQL Database 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.

  1. Name: Specify the Name of the Filegroup.
  2. FILESTREAM files: Total number of FILESTREAM files. The default value is zero (0).
  3. Read-Only: If you want to make the new Filegroup a read-only filegroup, then tick the Read-Only column.
  4. 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:

Add FILESTREAM filegroup

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:

  1. Logical Name: Specify the name of the FILESTREAM container. In this demo, the file name is DF_EmployeeDocument.
  2. File Type: Select FILESTREAM Data from the drop-down box.
  3. Filegroup: Select the FILESTREAM filegroup. In this demo, the Filegroup name is EmployeeDocument.
  4. 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:

Add FILESTREAM datafile in SQL database

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:

In the syntax,

  1. Db_name: Specify the database name in which you want to create the FILESTREAM filegroup.
  2. 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

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.

Now, run the following query to view the details of the FILESTREAM filegroup.

Output:

View Filegroups in database

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.

Run following query to view the details of datafile.

Output

View Filegroups in database

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:

  1. How to enable the FILESTREAM feature.
  2. 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.

Nisarg Upadhyay
732 Views