Rajendra Gupta
suspend the data movement wizard

A walk-through of moving database files in SQL Server Always On Availability Groups

July 8, 2019 by

SQL Server Always On Availability Groups feature provides high availability (HA) and disaster recovery solutions. We can add multiple databases in a single Always on Availability Group on SQL Server Enterprise edition. Before we add a database in the AG group, we need to use the following checklist.

Checklist to add a database in SQL Server Always On Availability Group

The following checklist is applicable to add a database on Always On Availability Groups.

  • We can add only User databases in an AG group
  • The User database should be in a read-write mode. You can verify the database status using the following query

  • The database recovery model should be FULL. We can check the recovery model using the following query

  • Initiate a full recovery log chain using the full database backup. If we do not have an existing database backup, take a manual full database backup before adding a database into the AG group
  • We cannot add a database into multiple availability groups
  • The database must not set to Auto Close. We can verify it using the following query

We should have a similar location for both the data and log files on the primary and all applicable secondary replicas.

Recently I come across an issue of low disk space on disk holding data file for a database in Always On Availability Groups. We can use the following approach to resolve the incidents for low disk space.

  1. Expand the data file disk size. It is the simplest method to resolve this issue. You can simply ask the storage or VM administrator to expand the disk to have sufficient free space
  2. If you have multiple data files for SQL database in SQL Server Always On Availability Group, you can plan to move some of the data files into another drive having sufficient free disk space

The method to move data files on a database in Always On Availability Groups is different from the standalone database. Let’s explore the methods to move the data files in an Always On Availability Group.

Environment Details for this article

  • TestDB database in Synchronous data commit mode with automatic failover and readable secondary replica
  • Data and log file for TestDB are in D:\TestDB folder. You can verify the data files path using the sp_helpdb ‘testDb’ command on both primary and secondary replica

    Primary Replica data file locations

    Primary replica in SQL Server Always on Availability Group

    Secondary Replica data file locations

    Secondary replica database

  • The database should be healthy and synchronized in SQL Server Always On Availability Group Dashboard. You can use the following query as well to check the synchronization status

    AG synchronization status

Suppose D drive is having space issues, and we want to move the existing data file to another drive (E:\TestDB) having sufficient free space. We can use the following approach to do it.

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.

suspend the data movement

It opens the following Suspend data movement wizard.

suspend the 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.

AG synchronization status

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.

Remove Secondary Database

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

Remove Secondary Database wizard

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.

warning sign in the Availability databases list.

The Secondary AG database state changes to Restoring state.

AG database state as Restoring

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.

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

location of the data files

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

the database view 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.

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

the system view sys.master_files

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.

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.

AG sync status

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.

data file locations

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).

Conclusion

In this article, we learned to move the data file for SQL Server Always On Availability Group database. Try exploring this on the test environment and become familiar with the steps.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views