Moving Always On DBs without Restarting SQL

March 14, 2018

 

Recently we had to move a database involved in Always On without restarting the SQL

 

The architecture is SQL 2016 with always on, 4 nodes:

2 synchronous 2 asynchronous. 3 are on premise, 1 in Azure VM.

 

As the servers have multiple Always on groups the customer wanted to reduce the number of failovers, so SQL restart was not an option on all nodes.

 

Note:

This only works on for a database that has been the primary within the always on.

If you do not failover and make a secondary into a primary then a SQL restart is needed.

 

The procedure works as follows:

 

  1. Make the secondaries unreadable.

  2. Execute the command to move the DB files on all copies of the DB: ALTER DATABASE DatabaseName MODIFY FILE.

  3. Failover to a synchronous secondary.

  4. Move the physical files (MDF/LDF/NDF) to the new location on the new secondary replicas (which was a primary).

  5. May need to execute : ALTER DATABASE [DatabaseName] SET HADR RESUME.

  6. Failback to the original Primary, or do rolling failovers to all replicas in the Always On group.

  7. Move the physical files (MDF/LDF/NDF) to the new location on the original secondary replicas (which was temporarily a primary).

  8. Make secondaries readable again.

 

Obviously much simpler if you can just restart the SQL :-) 

 

----- Download an example script -----

 

 

Maybe this will help someone.

Yitzchak.

 

Please reload

Featured Posts

I'm busy working on my blog posts. Watch this space!

Please reload

Recent Posts

October 31, 2017

October 29, 2017

Please reload

Archive
Please reload

Search By Tags