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
Archive
Search By Tags
No tags yet.
Follow Us
  • Facebook Basic Square
  • Twitter Basic Square
  • Google+ Basic Square

Our Services Data

   Big Data & NoSQL

   Data Science

   Business Intelligence

   Relational Database

Software Development

   FullStsck Dev

   Data Engineering

   Spark Framework

   MicroServices

Products

  Tableau

About

   About Us       

   Careers

   Contact

Cloud

   AWS

   Azure

   GCP

Naya Technologies

71 Hanadiv st. Herzeliya, Israel 

Office: +972-(0)9-7465005

Fax: +972-(0)9-7465006

© 2018 by NAYA Technologies. All rights reserved | Privacy Policy | Terms & Conditions | Web Accessibility