0

Environment: SQL 2016 SP1, AG with 2 replicas, sync commit, no readable secondary. 1 file in the PRIMARY FG, 1 for the index FG. Problem: How to move the files for a database in this AG, from the current disk to a new disk, with no downtime, not taking it out of AG ?

I'd try to add a file to each file group, on the new disk, run shhrinkfile emptyfile, remove the empty files from the old disk, all on the primary. Log file will stay on the old disk. Not sure how to do it on the secondary.

Can someone please confirm I'm on the correct path, or improve on it, please ? Thank you.

  • Will you remove the old disks? or will they still be available for the system? – Stijn Wynants Mar 22 '17 at 14:10
  • That's the point of the move, to remove the old disk currently used by the data files, as this is the only thing still using it. The log file uses another disk. tx! – Razvan Zoitanu Mar 22 '17 at 14:15
  • 1
    You won't be able to move the mdf file without downtime. Even when in always on. – Stijn Wynants Mar 22 '17 at 14:19
  • So what's the path to take ? Remove db from the AG on both replicas, delete from secondary, then take db offline on primary, move files, add to AG, restore to secondary node on the correct paths and last join secondary to AG ? – Razvan Zoitanu Mar 22 '17 at 14:26
  • I'm trying out something on my systems quickly, i'll let you know if I succeed. You might be able to do it without downtime but will have to check first – Stijn Wynants Mar 22 '17 at 14:28
2

Took me some time to test it but this actually works.

Step 1

Add the new disks to both nodes of the SQL Server Availability group

Step 2

Stop the SQL Server service of your passive node

Step 3

Move the SQL Server database files from the old disk to the new disk

Step 4

Adjust the drive letter of the original disk to another letter using f.e. diskpart

Step 5

Rename the disk where you move the database files to to the drive letter of the original disk

For Example= Old disk D: --> renamed to F: New Disk E: --> renamed to D:

Step 6

Restart SQL Service on Passive node and wait untill everything is back into sync.

Step 7

Failover to the passive node & do the exact same process on your previously active node.

Downtime?

If you follow this process you will have no downtime, except for the failover of your availability group.

  • Smart trick so will accept the answer. Changing drive letter doesn't work for me, as I already have another dozen dbs using the new drive. Lesson learned: keep the PRIMARY FG small, always create objects in a secondary FG (create and mark it as the default FG when the db is deployed). – Razvan Zoitanu Mar 22 '17 at 16:45
  • That's a bummer! Best of luck! – Stijn Wynants Mar 22 '17 at 16:50
  • This is such a clever workaround to removing the databases from the AG and adding them back in, saved me hours. Thanks! – Aki Dec 21 '17 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.