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.
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.