Moving Always On DBs without Restarting SQL


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.

#SQLServer #SQLServer2016 #AlwaysOn #AlwaysOn #YitzchakWahnon

Featured Posts
Posts are coming soon
Stay tuned...
Recent Posts