Back in 2011, we have seen how to move a database which is participating in DB Mirroring here. In this blog post, let’s see how to move a database which is participating in a AG to a new drive(location). In my AG setup,I’ve three replicas(2 near Replicas-Sync mode and 1 far replica sitting in a different data center-Async mode). The database which I will be moving to a new location is “sales”. See below for current paths.
Now we shall move these files to below mentioned new location (In this post, let’s see method two mentioned below):
E:\Devices\MSSQL13.MSSQLSERVER\MSSQL\DATA ( MDF File)
F:\Devices\MSSQL13.MSSQLSERVER\MSSQL\Data ( LDF File)
Method one:
Remove the database from AG.
Detach the database.
Move the files physically to new location(s).
Attach the database.
Rejoin the database in your AG.
Method Two:
In this method we shall see how to move files without removing the database from AG.
Steps to perform in sequence:
Suspend data movement for the database which you are working on to all replicas.
Logically move the database files(On all the Replicas).
Stop the SQL Server services. – This step will create an outage for all other databases residing on this instance.
Now move the physical MDF and LDF files to your new location.
Start the SQL service from config manager.
Resume data movement.
Before proceeding any further, I made sure the new path exists on of my replicas.
1.
2. Run this on all the replicas.
Now…I stopped SQL Services and moved physical files to new locations and started SQL Server.
3. Now resume data movement.
Voila…Now it’s all set as per my requirement.
That is it folks for today! Have fun…
MSSQLTREK 
Nice article. I tried method #2 and works perfectly. I’m able to practice the steps on our test environment. Just fyi (based on my test), suspending the data movement from the primary replica puts the database on all secondary replicas to another state like (x mark) other than suspended state (pause mark) which prevented me from doing the logical moves. I had to resume the date movement again and then suspend them one at a time starting from the secondary replica then lastly the primary replica. Thank you for a very nice article. It helps me with my practice of moving db in AG environment especially that we’re in the process of getting a new storage.
My environment is different. I have 1 replica as join only and not sharing storage. I would like to mode data files in both primary and secondary without shutting down the service. Here are my steps
1- Remove DB from Secondary
2- Remove DB from AAG on Primary
3- Detach
4- Move Data Files to new location
5- Attach
6- Add DB to AAG
That doesn’t seem to move the data files on the secondary replica.
Its important to set the AG group member to Read-Only = No (Properties of the AG).
Else the Alter command is not able to execute.
Thank you!
I preferred method 1 ’cause it doesn’t break the entire AG as long as you maintain quorum. No downtime for other db’s in AG ’cause you don’t need to stop the sql server service on primary server, only on secondary server. The key is to stop and start sql server service on all secondary replica’s one server at a time to maintain quorum. Then add the db back to AG using JOIN ONLY option. Again thanks for the article.
Can’t think of a valid reason for suspending data movement on the secondary replica before running the ‘alter database …’ statement against master DB on the secondary replica, as the metadata information is altered within master database, which is not part of AlwaysOn. Could you please explain.