Techyaz.com

How to Add Data File to Availability Database?

by · Published · Updated

Sometimes, we need to add data file to availability database in Always on Availability Group. Adding database file is a straight forward method but if your databases participate in Always on Availability group then you need to take extra care while planning or performing such activity. If you add data file to availability database on primary replica without planning then your secondary databases in availability group might get into “Not Synchronizing” state  irrespective of having identical drives and folder structure on all your replica servers.

If your drive letters or folder structures are identical on all replica servers then most of the time you will not face any issue while adding the database files but sometimes if you mount additional drive on all replicas to make it identical and then add data file in this new drive then you might face the issue. If all replica servers don’t have identical path then adding database file operation on primary replica will not successfully replicate to other replica servers because of different drive and folder structure. This, in turn, causes the secondary replica to enter the NOT SYNCHRONIZING state.

Note: Always try to keep drive and folder structure identical on all secondary replica servers to the corresponding primary replica.

If you have added a data file on primary database of Always on Availability group then you will see multiple errors logged in SQL Server error log on your secondary replica where secondary database is suspended from always on availability group and showing in “Not Synchronizing” state. You might see one of the below error codes in your log file.

Error:5123, Severity:16, State:1.
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\MSSQL\Data\Techyaz_Data2.ndf’.

Or

Error:5183, Severity:16, State:1.
Cannot create the file “Techyaz_Data2”. Use WITH MOVE to specify a usable physical file name. Use WITH REPLACE to overwrite an existing file.

Or

Error: 3313, Severity: 21, State: 2.
During redoing of a logged operation in database ‘Techyaz’, an error occurred at log record ID (76:659:1).

Now i will describe how to fix such issues or how to plan and add a database file to always on availability databases.

Add Data file to Availability Database

If you have already added data file on primary database and your secondary databases are suspended from availability group then we need to fix it to reestablish always on availability group. If you have not added the data file and want to add it then you can keep reading this article as i have described it later in this article. I have described how to plan database file addition to avoid any future synchronization issue.

Let’s go ahead with the first option where you have already added the data file to primary availability database and secondary database status is showing as not synchronizing due to this change. Now we need to fix this issue to reestablish the synchronization between replicas of availability group. To resolve this problem the DBAs  must complete the following steps:

  1. Remove the secondary database from the availability group that is showing as “Not Synchronizing”. To do this, either you can use GUI method in SSMS or run below T-SQL statement. You can connect to secondary replica then you can expand Always On High Availability node followed by Availability Groups Now, select the availability group and then expand the Availability Databases node. To remove a single database, select it in either the Object Explorer pane or the Object Explorer Details pane. Right-click the selected database or databases, and select Remove Secondary Database in the command menu. If you want to do it using T-SQL statement then you must connect to secondary replica and execute below Alter command.
ALTER DATABASE Techyaz SET HADR OFF;  
GO
  1. Once database will be removed from AOAG, restore a full backup of the filegroup that contains the added file to the secondary database, using WITH NORECOVERY and WITH MOVE option. This option is mandatory if you don’t have any log backups. Although sometimes you can do it by only running log backup on primary replica and restore it on secondary replica in norecovery mode as mentioned in next step.
  2. Now, back up the transaction log that contains the add-file operation on the primary database, and manually restore the log backup on the secondary database using WITH NORECOVERY and WITH MOVE.
  3. Prepare the secondary database for re-joining the availability group, by restoring, WITH NO RECOVERY, any other outstanding log backups from the primary database.
  4. Rejoin the secondary database to the availability group. Read this article to get step by step method to add a database to availability group.

If you havenot added the data file then you can plan it with following steps.

  1. Remove the database from secondary replica as described in above section. Now, secondary database will be showing in to restoring state.
  2. Add the data file to your availability database on primary replica.
  3. Take a log backup of this availability database on primary replica.
  4. Copy this log backup to secondary replica and restore it on its corresponding secondary replica using NORECOVERY and WITH MOVE option.
  5. Now add the database back to always on Availability Group.

Here, I have shown you step by step process to add data file to availability database. I have also let you know how to fix the issues that might arise due to adding the database files to availability database on primary replica. I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Read More:

Manvendra Deo Singh
Follow me:
Latest posts by Manvendra Deo Singh (see all)

Tags:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *