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 