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:
Make the secondaries unreadable.
Execute the command to move the DB files on all copies of the DB: ALTER DATABASE DatabaseName MODIFY FILE.
Failover to a synchronous secondary.
Move the physical files (MDF/LDF/NDF) to the new location on the new secondary replicas (which was a primary).
May need to execute : ALTER DATABASE [DatabaseName] SET HADR RESUME.
Failback to the original Primary, or do rolling failovers to all replicas in the Always On group.
Move the physical files (MDF/LDF/NDF) to the new location on the original secondary replicas (which was temporarily a primary).
Make secondaries readable again.
Obviously much simpler if you can just restart the SQL :-)
Maybe this will help someone.
Yitzchak.
