How to move database files to another location in SQL Server Always On Availability Groups

Prerequisites

You have an Identify deployment that uses SQL cluster.

Note: remember to back your databases up before proceeding.

Move database files to another location

In this example, we choose to move files of the IdentifyAudit database to D:\data. Firstly, verify the current data files path using command: sp_helpdb 'IdentifyAudit'

  • Primary Replica data file locations

primary-replica-current-file-location

  • Secondary Replica data file locations

secondary-replica-current-file-location

Step 1: Suspend data movement

Connect to the Secondary replica and go to Always on High Availability. Expand the High Availability and right-click on the database and choose Suspend Data Movement.

suspend-data-movement

The Suspend Data Movement wizard opens, click OK button

suspend-data-movement-ok

Once it is completed, you can see its Pause status

suspend-data-movement-pause

Step 2: Remove secondary database

In this step, you must remove the database out of the Always on Availability Group of the secondary replica. Right-click on the database and choose Remove Secondary Database.

remove-secondary-database

After the wizard below opens, click OK

remove-secondary-database-ok

Once it is done, you can see the new status of the IdentifyAudit database:

remove-secondary-database-status

Step 3: Stop SQL service

Stop the SQL Service on the secondary replica using the SQL Server Configuration Manager.

stop-sql-service

Step 4: Move data files to the new location

Move database files to the new location and start the SQL Service again.

Step 5: Tell SQLServer that you want to move files to a new location

Script:

Step 6: Join it to Availability group

On the secondary replica, right-click on the database and choose Join to Availability Group.

join-availability-group

Step 7: Promote the secondary replica to be the primary replica

After this step is done, the previous primary replica has become the new secondary replica.

manual-failover

Step 8: Repeat the 1st step to 7th step on new secondary replica

Repeat Step 1 to Step 7 on the new secondary replica. After that, you can switch their roles back if you want to.

After moving the IdentifyAudit database's data files, you can verify that everything works well:

  • Access an Identify instance and perform a login to its Admin site.
  • Use the Identify Configurator to create/upgrade/remove/replicate an instance.