Moving Database Files on SQL Server Always On

I think all of us know how to move files for SQL Server databases from disk to another disk but today I will explain how we can do it on SQL Server always on to reduce the downtime and taking new backup to return the database to always-on again. Let us start on the technical steps.

• Preparations Steps
• Check the current DB Files locations
• Check Always on Health Status
• Disable all backup jobs on Primary and secondary servers
• Suspend the data movement on Secondary server
• Remove Database from Primary server
• Update DB files by new disk location on secondary server
• Stop SQL Server instance on Secondary server
• Move (CUT) DB Files from old location to new Location (Past)
• Start the SQL Server instance again
• Verify the DB File Location Using T-SQL on the post
• Join the DB to availability group from Primary server
• Check SQL Server always on Health Status Using T-SQL on the post
• Enable the SQL Server Jobs\
• Failover the SQL Server for Primary (X) to Secondary Server (Y) and do the same steps on (X)

Let’s Start The Technical Demo

Preparations Steps

• Prepare your new disks
• Make sure the security copied from old disk to new disk

Check the current DB Files locations

SELECT D.Name AS Database_Name,M.name, M.physical_name AS current_file_location
FROM sys.master_files AS M
inner join SYS.Databases As D 
on D.database_id = M.database_id

Check Always on Health Status

SELECT sadc.database_name, 
       ag.name AS ag_name, 
       dhrs.is_local, 
       dhrs.is_primary_replica, 
       dhrs.synchronization_state_desc, 
       dhrs.is_commit_participant, 
       dhrs.synchronization_health_desc
FROM sys.dm_hadr_database_replica_states AS dhrs
     INNER JOIN sys.availability_databases_cluster AS sadc ON dhrs.group_id = sadc.group_id AND dhrs.group_database_id = sadc.group_database_id
     INNER JOIN sys.availability_groups AS ag ON ag.group_id = dhrs.group_id
     INNER JOIN sys.availability_replicas AS sar ON dhrs.group_id = sar.group_id  
AND dhrs.replica_id = sar.replica_id;

Now we will do our Activity on the secondary then after it, you can do failover and do the same steps on the primary node that it will be secondary node after failover

Disable all backup jobs on Primary and secondary servers

You should stop the backup to avoid any new log backup can be taken during this activity otherwise you will need to do restore for logs backup on secondary server to be able to join the Db again to availability IF you are taking backup using SQL Server native mode use below script and if you are using any third-party tool disable the SQL account of this tool in all nodes.

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1
and name = 'backup Job Name'
GO

Suspend the data movement on Secondary server

USE MASTER
GO
ALTER DATABASE [DEMO_MOVE_DB] SET HADR SUSPEND
GO
ALTER DATABASE [DEMO_MOVE_DB] SET HADR OFF;

Remove Database from Primary server

USE MASTER
GO
ALTER AVAILABILITY GROUP [DEMO_HAG] 
REMOVE DATABASE [DEMO_MOVE_DB];

Update DB files by new disk location on secondary server

USE MASTER
GO
ALTER DATABASE DEMO_MOVE_DB   
MODIFY FILE (NAME = DEMO_MOVE_DBlog,   
FILENAME = 'F:\logdbfiles\DEMO_MOVE_DB.ldf');  

LAST STEPS

• Stop SQL Server instance on Secondary server
• Move (CUT) DB Files from old location to new Location (Past)
• Start the SQL Server instance again
• Verify the DB File Location Using T-SQL on the post
• Join the DB to availability group from Primary server
• Check SQL Server always on Health Status Using T-SQL on the post
• Enable the SQL Server Jobs
• Failover the SQL Server for Primary (X) to Secondary Server (Y) and do the same steps on (X)

Keep Following Me

• Twitter
• LinkedIn
• Link
• Link