SQL SERVER – Adding File to Database in AlwaysOn Availability Group

Writing some typical interview questions have been on my list always. I look for new ways of tricking the person and most importantly, some of the interview questions are designed to make sure the person has strong fundamentals. This blog is one such attempt to understand the basics of the AlwaysOn Availability Group.

This is one of the common interview questions – What would happen if you add a new file (database file MDF or transaction log file LDF) to a database which is part of a high availability solution? As you might know that there are various high availability solutions like Failover clustering, database mirroring log shipping and the effect would be different in each solution. Out of many other high availability solutions, in this blog we will talk about availability group feature which was introduced in SQL Server 2012.

Let us talk about the basics of availability group first. Whatever modification operation done on the database would be logged to the transaction log file, called as log record. Since database is part of availability group the same command would be sent to all secondary replicas via log record. This means that adding data or log file to primary database essentially roll-forwards the same command to all secondary replicas.  This means that ADD FILE operation will be executed on Secondary servers. Using below code, I have added the file to drive E on Primary Instance.

USE [master]
GO
ALTER DATABASE [MyConDB]
ADD FILE (NAME = N'SQLAuthority_2',
FILENAME = N'E:\SQLAuthority.ndf' ,
SIZE = 4096KB, FILEGROWTH = 1024KB) TO FILEGROUP [PRIMARY]
GO

If this drive does not exist in secondary, then this operation will fail, resulting into the suspended secondary replica. Here is the information seen in the ERRORLOG (SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location)

2015-01-01 17:52:14.19 spid45s     Error: 5123, Severity: 16, State: 1.
2015-01-01 17:52:14.19 spid45s     CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\SQLAuthority.ndf’.

2015-01-01 17:52:14.27 spid45s     Error: 5183, Severity: 16, State: 1.
2015-01-01 17:52:14.27 spid45s     Cannot create the file “SQLAuthority_2”. Use WITH MOVE to specify a usable physical file name. Use WITH REPLACE to overwrite an existing file.

2015-01-01 17:52:14.30 spid45s     AlwaysOn Availability Groups data movement for database ‘SQLAuthority’ has been suspended for the following reason: “system” (Source ID 2; Source string: ‘SUSPEND_FROM_REDO’). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.

2015-01-01 17:52:14.33 spid45s     Error: 3313, Severity: 21, State: 2.
2015-01-01 17:52:14.33 spid45s     During redoing of a logged operation in database ‘SQLAuthority’, an error occurred at log record ID (34:358:1). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

If there is a possibility to recreate the same path on secondary replica, then we need to resume the data movement using below command.

ALTER DATABASE [SQLAuthority] SET HADR RESUME;
GO

If it is not possible to recreate the path on secondary, then we need to reinitialize this secondary again. Which means we need to restore full backup and a log backup from the primary.

Have you encountered the same problem in Log shipping or Clustering? What steps did you take to solve it? Do let me know via the comments below, I am sure we can learn from each other this way. Here is another interesting article about AlwaysOn.

Reference: Pinal Dave (https://blog.sqlauthority.com)