Database File movement in SQL AlwaysOn Availability group: A walk-through
Hello all,
In this blog, we are covering the scenario of moving the database files of AlwaysOn Availability Group (AG) enabled database to another location on all replicas with minimum downtime.
To demonstrate database file movement, using Windows 2012 R2 cluster with AlwaysOn configured on SQL 2016 instance. This walk-through provides an example of how to move the database files to another location. The names and numbers of servers used are for example purposes only.
Primary Replica Node 1: SQLSERVER-0
Secondary Replica Node 2: SQLSERVER-1
Database Name: AutoHA-sample
Availability Group name: Contoso-ag
As per the below screenshots, AutoHa-sample database files are present at F:\Data and F:\Log directory respectively.
SQLSERVER-0:
SQLSERVER-1 :
For demonstration , let’s move the data and Log files to another location: "C:\MSSQL13.MSSQLSERVER\MSSQL\Data" .
We will use “Remove Secondary database from the Availability group” approach to achieve this and once the database files are moved to the new location, we will manually add the database back to the AG.
First step is to ensure that the Log backups are disabled on all primary/secondary replicas. This critical step ensures that Log file is not truncated. Since we are using Remove Database Out of AG/Join database to AG approach, log file need to be intact while joining the database back to the AG. If the Log backups continue to happen, then the Transaction log virtual log files are marked for reuse and truncated. Hence join database to AG operation would fail, then the only option available is to manually apply the Log backups/Perform re-initialization with full backup which can be cumbersome.
Note: Since the Log file is not truncated as the Log backups are disabled, ensure that the drive hosting Log file has adequate space.
Next step is to perform the database files movement on the secondary replica. Connect to the secondary replica and suspend the data movement as below:
When the data movement is suspended, the database status in AG changes to "Not- Synchronizing".
Next step is to remove the database out of AG using “Remove Secondary database” option:
Once the database is removed out of AG on the secondary replica, warning sign appears next to the database name in the Availability databases list.
The database state changes to "Restoring" state.
Now that the database is removed from AG, move the database files (Data and Log) to desired location. In this scenario, we are moving the database files to "C:\MSSQL13.MSSQLSERVER\MSSQL\Data" folder.
Since the database is in restoring state on the secondary replica, it is not possible to take the database offline. Next step is to stop the SQL Service on the secondary replica.
Since SQL Service is stopped on the secondary replica, this has no impact on the application. Application will continue connecting to the active primary replica using the AG listener name.
Move the database files to the new location and Start the SQL Service.
The database state will be in restoring state. Executing sysaltfiles view reflects the new file location. Kindly note that sys.database_files/database files through GUI would still reflect the old path till the AG failover is performed.
To join the database back to the availability Group using “Join to Availability group”:
The Log file is intact on the primary replica, AlwaysOn sends the log blocks from last hardened LSN on secondary replica. Transaction Log is replayed on secondary replica, the database is added back to AG.
Review the AG dashboard and wait for the database to come to Synchronized state (Synchronous replica) /Synchronizing (Asynchronous Replica). Time needed to catch up with primary replica depends on several factors like number of Log blocks sent across the network/IO on the secondary replica etc.
Once the database is synchronized, fail over the AG to the secondary replica (which has the database with modified file location).
Once the AG is failed over, perform the same set of steps on the new secondary replica. Enable the Transaction Log backup jobs on all the replicas only post carrying out the file movement activity on all the replicas.
NOTE: As stated earlier, disabling the Log backup jobs is a critical step before starting the file movement activity. Otherwise, while joining the database back to AG, error is reported as per the screenshot below:
Hope the above steps mentioned helps in database file maintenance activity.
Please share your feedback, questions and/or suggestions.
Thanks,
Don Castelino | Premier Field Engineer | Microsoft
Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.
Comments
- Anonymous
January 28, 2018
If you forget to stop log backups or can’t stop them due to heavy activity. You can still apply them to the secondary since it is in “no recovery” mode once it is removed from the avail group. Once you apply all logs with no recovery you can then join the database into the avail. group.- Anonymous
January 30, 2018
That's correct. Applying T log backups on secondary server is very much possible. But this approach can be very tedious as there could be many log backups to be applied. Also, identifying the first t log backup to be applied on the secondary could be tricky. Hence instead of manually applying the logs, if the drive on primary has adequate space, let SQL take care of the applying the log blocks on secondary, which is easy to implement. The option you suggested could be followed when the primary has space crunch on the drive hosting T log file and frequency of the T log backups is not very frequent.
- Anonymous
- Anonymous
September 22, 2018
What process would you follow if you do not have fail-over implemented and would like to change the location of the PRIMARY database files?Thanks - Anonymous
November 29, 2018
Great post !Thank you!! - Anonymous
January 01, 2019
The comment has been removed