Adding new data file to AG

Question

Can you please let me know the best and safest approach to add a new data file to an AG.

Answer 1

A new database or a new file to a filegroup for a specific database?

If just adding an additional file to an existing database - then all you need to do is make sure the exact same path exists on the secondaries. SQL Server will create the new file on the primary and send those commands to the secondaries - which will then create the new files.

If adding a database - then you have several options. I prefer performing the backup/restore process myself - then joining the database to the AG, but you can have SQL Server do that or use automatic seeding.

Answer 2

Hi @Chaitanya Kiran ,

You can add a new data file to AG by running below T-SQL on primary replica. For example,

USE [master]  
GO  
ALTER DATABASE [TSQL test]  
ADD FILE (NAME = N'TSQLtest_2',  
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL20171\MSSQL\DATA\TSQLtest.ndf' ,  
SIZE = 4096KB, FILEGROWTH = 1024KB) TO FILEGROUP [PRIMARY]  
GO  

If you are adding a database file – data or log to the Primary Replica, the add file command will also be executed on the secondary replicas.

Please note: If you have not placed your database files for your Primary and Secondary replica database on an identical path, then SQL Server will not be able to create that file on the Secondary Replica server. In the result of this, the add file command will fail on the Secondary replica, and it will force the replica to go into the suspended state.

Please refer to below blogs to get more information.

SQL SERVER – Adding File to Database in AlwaysOn Availability Group
Adding File to Database in AlwaysOn Availability Group

Best regards,
Cathy

===============================================

If the response helped, do "Accept Answer" and upvote it.