Move data file to another drive in SQL Server Always On availability group database

 
In this approach, we have a small downtime requirement (we will cover it later with the steps). We configure the full and log backup on the AG database to prepare the database in case of any disaster. Take a log backup before the activity and disable all backups during the activity time. We should not break the backup chain else the restoration will not be possible. It ensures that the backup LSN (log sequence number) chain is intact and we are competent to perform restoration in case of any issues.

Let’s move the data file into another drive using this method.

Steps to perform on Secondary replica in SQL Server Always on Availability Group

Step 1: Connect to the secondary replica and go to Always On High Availability. Expand the High Availability. Right-click on the database and suspend the data movement.

It opens the following Suspend data movement wizard.

Click on Ok to suspend data movement. Once the data movement is suspended, the database status on Secondary Always on Availability Group to Not Synchronizing. You can also see the health status of Not Healthy.

Step 2: In this step, remove the database out from the SQL Server Always On Availability Group from the secondary replica. Expand the High Availability. Right-click on the database and Remove Secondary Database.

It opens another wizard to remove a database from the AG group. Click on OK.

Once you remove the database from the secondary replica AG group, it shows a warning sign with the database name in the Availability databases list.

The Secondary AG database state changes to Restoring state.

 

We removed the availability group database from SQL Server Always On Availability Group. The database is still available on the primary replica and available for the users. Only data synchronization between primary and secondary replica is not available.

Step 3: Use the Alter database command on the master database of the secondary replica to move the data file to another drive. You can refer the article How to move SQL database files (MDF and LDF) to another location for more details on alter database command.

ALTER DATABASE TestDB  

    MODIFY FILE (NAME = TestDB,  

                  FILENAME = 'E:\TestDB\TestDB.mdf'); 

GO

Once you execute this command, you get the following message:

The file “TestDB” has been modified in the system catalog. The new path will be used the next time the database is started.

Step 4: Stop the SQL Service on the secondary replica using the SQL Server Configuration Manager.

Step 5: Move the database file to the new location and start the SQL Service. Execute the Sp_helpDB command to verify the data file location

It still shows the old data file location on the secondary replica

If we check using the database view sys.database_files, it also returns the wrong data file location

Use TestDB

Go

SELECT name,physical_name  FROM sys.database_files

Sp_helpDB executes a stored procedure sp_helpfile, and its scope is set a database level. The secondary database is a replica of the primary in SQL Server Always On. In this article, we changed the data file location for the secondary database AG replica, but the primary database is still pointing to the old location. Secondary replica still gets the old data file path the primary replica. Due to this, we get old or incorrect data file locations using both sp_helpdb and sys.master_files commands for SQL Server Always On Availability Group.

Let’s check the data file location using the system view sys.master_files. 

SELECT name, physical_na me AS NewLocation, state_desc AS OnlineStatus FROM sys.master_files  WHERE database_id = DB_ID(N'TestDB')   GO

It shows the correct location for the data file in the E drive.

We get the correct results using the sys.master_files because this system view is scoped at the master database level. We have changed the system catalog in the master database on the secondary replica. The system database Master cannot be a part of the availability group. It is maintained separately for each instance. Due to this reason, we get the correct location of the data and log file on the secondary replica as well despite the different location of the data file on the primary replica.

• Note: You should always rely on the output of sys.master_file command instead of sp_helpdb and sys,database_files in SQL Server Always On availability group.

Step 6: On the secondary replica, right-click on the database and click on Join to Availability Group.

Once the database joins to an availability group, verify the status is synchronized. It might take a little time to become synchronized depending upon the number of transactions on the primary replica during the activity time.

Failover and perform the steps on new Secondary Server in SQL Server Always on Availability Group

Step 7: Perform a manual failover from the primary replica to secondary replica. After the failover, the old secondary replica becomes the primary, and we can verify the data file locations now using the sp_helpdb command.

Step 8: Now, we need to perform the same steps 1 to 7 on the new secondary AG database. After this step, both the primary and secondary replica database is moved to E drive. Monitor the AG dashboard.

Step 9: Enable both the full and log database backups on the AG replica (depending upon the backup configuration and priority on AG replica instances).