A walk through of moving database files in SQL Server Always On Availability Groups
A walk-through of moving database files in SQL Server Always On Availability Groups
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolA walk-through of moving database files in SQL Server Always On Availability Groups
July 8, 2019 by Rajendra Gupta 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 1234567 Select Case is_read_only When 1 then 'DB is in Read-Only Mode' When 0 then 'DB is in Read-Write Mode' End as Database_Status From sys.databases Where name='AdventureWorks2014' The database recovery model should be FULL. We can check the recovery model using the following query 123 Select recovery_model_desc From 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 query 1234567 Select Case is_auto_close_on When 1 then 'DB is in Auto Close Mode' When 0 then 'DB is not in Auto Close Mode' End as Database_Status From sys.databases Where 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 status 123456789101112 SELECT 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 dhrs INNER JOIN sys.availability_databases_cluster AS sadc ON dhrs.group_id = sadc.group_id AND dhrs.group_database_id = sadc.group_database_id INNER JOIN sys.availability_groups AS ag ON ag.group_id = dhrs.group_id INNER JOIN sys.availability_replicas AS sar ON dhrs.group_id = sar.group_id AND 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. 1234 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 1234 Use TestDBGoSELECT 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. 1234 SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatusFROM 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).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. Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022