This article explains how to manage a data file and filegroups in an SQL database created on CentOS Linux. In my previous article, Manage filegroups of SQL Databases, we learned about the different types of filegroup and data files in SQL Server 2019 on windows and how to manage them using T-SQL queries. In this article, we will learn how to manage the filegroups in SQL Server 2019 on CentOS. I am going to cover the following topics in the article:

1. Create a Secondary filegroup and add a data file in it
2. Make a secondary filegroup as a default filegroup of SQL database

We are going to use the SQL Server Management studio to manage the filegroups. I have created a virtual machine on my workstation and installed CentOS Linux in the virtual machine. Also, configured the SQL Server 2019 in CentOS. You can read Manage SQL Database in CentOS: Install SQL Server on CentOS article to learn the installation process of SQL Server 2019 on CentOS. I have restored the backup of the Stackoverflow2010 database in SQL Server.

The SQL Server 2019 is installed on CentOS. The virtual machine is not part of the windows active directory; therefore, I am using an IP Address to connect to it. We are using SQL Server authentication to connect to the database engine. Open SQL Server management studio and specify the following values in the Connect to Server dialog box.

1. Server Type: Database Engine
2. Server Name: IP Address (192.168.1.47)
3. Authentication: SQL Server Authentication
4. Login: sa
5. Password: Specify the appropriate password of sa user

Screenshot

Run the following query to populate the list of database files of the stackoverflow2010 database.

Query Output

Now, let us understand how to create a new filegroup and add a data file to it.

Create Secondary filegroup and datafile

For demonstration, we are going to add a secondary filegroup named FG_Stackoverflow2010_Users and add a data file on the/SQLDatabase/Stackoverflow2010 location.

To add a secondary filegroup, expand Databases 🡪 Right-click on Stackoverflow2010 🡪 Select Properties.

In the Database Properties dialog box, Click on Filegroups 🡪 Click on Add Filegroup button. A row will be added in a Filegroup grid view. Specify the following values in the columns

1. Name: FG_Stackoverflow2010_Users.
2. Read-Only: Tick this option if you want to make the filegroup read-only. In our case, the filegroup is read-write, so we did not make any changes.
3. Default: If you want to make the filegroup read-only, tick this option. In our case, the filegroup is read-write, so we did not make any changes.

The Filegroup screen looks like the following image:

Once the secondary filegroup is added, we must add a data file in the secondary filegroup. In this demo, we add a data file named DF_Stackoverflow2010_Users in the FG_Stackoverflow2010_Users filegroup. To add a data file, click on Files 🡪 Click on Add. A row will be added in the Database files grid view. Specify the following values.

1. Logical name: Specify the logical name of the secondary data file. In this demo, the data file name is DF_Stackoverflow2010_Users.
2. Filetype: Specify the type of the data file. In this demo, the datafile type is ROWS DATA.
3. Filegroup: Specify the name of the filegroup. In this demo, the filegroup name is FG_Stackoverflow2010_Users.
4. Autogrowth / max size: Autogrowth is enabled, the value of Autogrowth is 64 MB, and the maximum allowed growth is unlimited.
5. Path: Specify the physical path of the secondary data file. In this demo, the physical path is /SQLDatabase/Stackoverflow2010
6. File Name: Specify the file name. In our case, the file name is DF_Stackoverflow2010_Users.ndf.

The Filegroup screen looks like the following image:

Click OK to create a secondary filegroup and datafile. Run the following query to view the filegroups of the stackoverflow2010 database.

Query Output:

As you can see, the FG_Stackoverflow2010_Users and DF_Stackoverflow2010_Users have been created. Now let us understand how to make FG_Stackoverflow2010_users a default filegroup.

Make secondary filegroup as a default filegroup

We want to make sure that the new tables must be created in the FG_Stackoverflow2010_Users filegroup. We can explicitly specify the filegroup name in the CREATE TABLE statement. Another way is to make the FG_Stackoverflow2010_Users filegroup as a default filegroup of the SQL database. To do that, open the Database Properties 🡪 Click on Filegroups 🡪 Tick on Default, and click OK.

Now, let us create a table named tblUsers in the stackoverflow2010 database. The table must be created in an FG_Stackoverflow2010_Users filegroup. Run the following query to create a table.

You can view the list of tables created in the FG_Stackoverflow2010_Users filegroup by executing the following query.

Query Output

As you can see, the tblUsers is created in the FG_Stackoverflow2010_Users filegroup.

Summary

In this article, we learned how to manage the filegroups of the SQL database in SQL Server 2019 on CentOS using SQL Server management studio. I have covered the following topics:

1. Create a Secondary filegroup and add a data file in it.
2. Make a secondary filegroup as a default filegroup of SQL database.

In my next article, we will learn how to configure the SQL Server Agent jobs in SQL Server 2019 on CentOS. Stay tuned!

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

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

Latest posts by Nisarg Upadhyay (see all)

• 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