How to move AlwaysON AG Databases ?

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.

1

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

2. Run this on all the replicas.

3

Now…I stopped SQL Services and moved physical files to new locations and started SQL Server.

3. Now resume data movement.

4

Voila…Now it’s all set as per my requirement.

5

That is it folks for today! Have fun…

Advertisements

4 thoughts on “How to move AlwaysON AG Databases ?

  1. Ramil April 24, 2018 / 3:07 pm

    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.

  2. DALILA FRIDI November 20, 2018 / 8:17 am

    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.

  3. Thomas May 24, 2019 / 2:54 pm

    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.

    • Nate June 21, 2019 / 7:16 am

      Thank you!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.