When it comes to high availability solutions in SQL Server, there are various options available such as failover clustering, database mirroring, and log shipping. One of the commonly used solutions is the AlwaysOn Availability Group feature, which was introduced in SQL Server 2012.

In this blog post, we will focus on one specific question that often comes up in interviews: “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?”

Let’s start by understanding the basics of the AlwaysOn Availability Group. In this solution, any modification operation performed on the primary database is logged to the transaction log file, also known as a log record. Since the database is part of an availability group, the same command is sent to all secondary replicas via the log record. This means that adding a data or log file to the primary database will essentially execute the same command on all secondary servers.

For example, if we want to add a file to the primary instance using the following code:

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 the specified drive (in this case, drive E) does not exist on the secondary replicas, the operation will fail, resulting in the suspension of the secondary replica. The ERRORLOG will show an error message indicating that the system cannot find the specified path.

To resume data movement on the database, you will need to manually resume the database using the following command:

ALTER DATABASE [SQLAuthority] SET HADR RESUME;
GO

If it is not possible to recreate the same path on the secondary replica, then the secondary replica needs to be reinitialized. This involves restoring a full backup and a log backup from the primary.

If you have encountered a similar problem in log shipping or clustering, it would be interesting to hear about the steps you took to solve it. Feel free to share your experiences in the comments below. We can all learn from each other’s challenges and solutions.

For more information about AlwaysOn Availability Groups, you can refer to the SQL Server Books Online or check out the blog of Pinal Dave, a renowned SQL Server expert.

Stay tuned for more articles on SQL Server concepts and best practices!