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 query1234567SelectCase is_read_onlyWhen 1 then 'DB is in Read-Only Mode'When 0 then 'DB is in Read-Write Mode'End as Database_StatusFrom sys.databasesWhere name='AdventureWorks2014'
The database recovery model should be FULL. We can check the recovery model using the following query123Select recovery_model_descFrom sys.databasesWhere name='AdventureWorks2014'
- 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 query1234567SelectCase is_auto_close_onWhen 1 then 'DB is in Auto Close Mode'When 0 then 'DB is not in Auto Close Mode'End as Database_StatusFrom sys.databasesWhere name='AdventureWorks2014'
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.
- 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
- 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
Secondary Replica data file locations
- 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 status123456789101112SELECT sadc.database_name,ag.name AS ag_name,dhrs.is_local,dhrs.is_primary_replica,dhrs.synchronization_state_desc,dhrs.is_commit_participant,dhrs.synchronization_health_descFROM sys.dm_hadr_database_replica_states AS dhrsINNER JOIN sys.availability_databases_cluster AS sadc ON dhrs.group_id = sadc.group_id AND dhrs.group_database_id = sadc.group_database_idINNER JOIN sys.availability_groups AS ag ON ag.group_id = dhrs.group_idINNER JOIN sys.availability_replicas AS sar ON dhrs.group_id = sar.group_idAND dhrs.replica_id = sar.replica_id;
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.
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');
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
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_name AS NewLocation, state_desc AS OnlineStatus
WHERE database_id = DB_ID(N'TestDB')
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).
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.
While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at email@example.com
View all posts by Rajendra Gupta