How to move databases configured for SQL Server AlwaysOn

  • Artykuł

How to move databases configured for SQL Server AlwaysOn

 

This article describes how to change the location of the data files and the log files for any Microsoft SQL Server 2012 database which are configured with SQL Server AlwaysOn with minimal downtime.

Considerations

We have three node windows 2008/2012 cluster where SQL server is installed and configured for AlwaysOn. The location of AlwaysOn databases on all the three nodes are similar (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data). You run out of disk space on drive C:\ and decide to move the physical file of AlwaysOn to a new drive (D:\data\).

 

Node1 –Default instance (Primary replica)

Node2 –Default instance (Secondary replica –Synchronous)

Node3 –Default instance (Secondary replica –Asynchronous)

 

One of the solutions to accomplish this is completely remove the AlwaysOn configuration and reconfigure with the new location. But this process is time consuming and requires a lot of downtime. To overcome this we can follow the below steps which would minimise the downtime required to complete the relocation of database files.

Benefits

· Downtime time is time required for two failovers for any number of replicas.

· Downtime is only for application/users using that databases (unlike SQL instance restart that means downtime for applications/users using remaining databases too).

 

Prerequisites

  • Perform a full database backup on all the databases participating in SQL Server AlwaysOn.

  • Make sure that you have system administrator (sa) permissions.

  • Make sure that you know the name and the current location of all data files and log files for the database.

    Note You can determine the name and the current location of all files that a database uses by using the sp_helpfile stored procedure:

use < database_name>

go

sp_helpfile

   go

 

  • You should have exclusive access to the database that you are moving. If you have problems during the process, and if you cannot access a database that you have moved or if you cannot start SQL Server, examine the SQL Server error log and SQL Server Books Online for more information about the errors that you are experiencing.

Note: - We have seen scenarios where this doesn’t work on RTM version. So please upgrade the SQL instance with Service pack 1 before following this article

Planned Relocation Practice

NOTE: - For this scenario to work, below steps should be done in the same order.

In order to move the physical files we need to follow the below steps:

1. Disable read-only access for all the secondary replicas.

2. Modify the location of the data and transaction log files on all the replicas using the ALTER DATABASE…MODIFY FILE option.

3. Perform the failover of AlwaysOn group to any synchronous replica (Node2 in this scenario).

Note: This would clear all the file handles on the secondary replicas. On the new primary replica the database files will be used from the original location which can be verified using SP_HELPDB < DBNAME>.

 

4. Move the physical files (MDF/LDF/NDF) to the new location on all the secondary replicas.

 

Note: At this point the synchronization between the replicas are broken.

 

5. Initiate the database recovery using ALTER DATABASE…SET ONLINE on all the secondary replicas to resume the synchronization.

 

6. Perform the failover of AlwaysOn group back to original node (From Node2 to Node1 in this scenario).

7. Follow the step-4 & step-5 on the Node2 to fix the file location and resume the synchronization.

8. Finally enable the read-only access for all the secondary replicas.

 

AlwaysOn Group is online as before and the data files & transaction log files are moved to the new location.

Example

The following example moves the AdventureWorks data file and log file to a new location as part of a planned relocation.

 

Environment:

Node1 –Default instance (Primary replica)

Node2 –Default instance (Secondary replica –Synchronous)

Node3 –Default instance (Secondary replica –Asynchronous)

AlwaysOn Database –AdventureWorks

Data file location: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks.mdf

Log file location : C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks.ldf

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

--- Select “QUERY” from the “MENU” bar and select “SQLCMD MODE”

 

--Disable read-only access for all the secondary replicas

:Connect NODE1

 

USE[master]

GO

ALTER AVAILABILITY GROUP [AdventureWorksAG] MODIFY REPLICA ON N'NODE1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))

GO

ALTER AVAILABILITY GROUP [AdventureWorksAG] MODIFY REPLICA ON N'NODE2' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))

GO

ALTER AVAILABILITY GROUP [AdventureWorksAG] MODIFY REPLICA ON N'NODE3' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))

GO

 

--Modify the location of the data and transaction log files on all the replicas

:Connect NODE1

 

ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME='AdventureWorks',FILENAME='D:\DATA\AdventureWorks.mdf')

go

ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME='AdventureWorks_log',FILENAME='D:\DATA\AdventureWorks_log.ldf')

go

 

:Connect NODE2

 

ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME='AdventureWorks',FILENAME='D:\DATA\AdventureWorks.mdf')

go

ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME='AdventureWorks_log',FILENAME='D:\DATA\AdventureWorks_log.ldf')

go

 

:Connect NODE3

 

ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME='AdventureWorks',FILENAME='D:\DATA\AdventureWorks.mdf')

go

ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME='AdventureWorks_log',FILENAME='D:\DATA\AdventureWorks_log.ldf')

go

 

 

--Perform the failover of AlwaysOn group

:Connect NODE2

 

ALTER AVAILABILITY GROUP [AdventureWorksAG] FAILOVER;

GO

 

 

--Move the physical files (MDF/LDF/NDF) to the new location on all the secondary replicas.

 

:Connect NODE1

 

--Enable XP_CMDSHELL

sp_configure 'show advanced options',1

go

reconfigure

go

sp_configure 'xp_cmdshell',1

go

reconfigure

go

 

--MOVE FILES

xp_cmdshell'move "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks*.*" D:\data\'

go

 

--Disable XP_CMDSHELL

sp_configure 'show advanced options',1

go

reconfigure

go

sp_configure 'xp_cmdshell',0

go

reconfigure

go

 

 

:Connect NODE3

--Enable XP_CMDSHELL

sp_configure 'show advanced options',1

go

reconfigure

go

sp_configure 'xp_cmdshell',1

go

reconfigure

go

 

--MOVE FILES

xp_cmdshell'move "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks*.*" D:\data\'

go

 

--Disable XP_CMDSHELL

sp_configure 'show advanced options',1

go

reconfigure

go

sp_configure 'xp_cmdshell',0

go

reconfigure

go

 

 

 

 

--Initiate the database recovery

 

:Connect NODE1

 

ALTER DATABASE [AdventureWorks] SET ONLINE

GO

 

:Connect NODE3

 

ALTER DATABASE [AdventureWorks] SET ONLINE

GO

 

--Perform the failover of AlwaysOn group back to original node

 

:Connect NODE1

 

ALTER AVAILABILITY GROUP [AdventureWorksAG] FAILOVER;

GO

 

--To fix the file location and resume the synchronization on Node2

:Connect NODE2

 

--Enable XP_CMDSHELL

sp_configure 'show advanced options',1

go

reconfigure

go

sp_configure 'xp_cmdshell',1

go

reconfigure

go

 

--MOVE FILES

xp_cmdshell'move "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks*.*" D:\data\'

go

 

--Disable XP_CMDSHELL

sp_configure 'show advanced options',1

go

reconfigure

go

sp_configure 'xp_cmdshell',0

go

reconfigure

go

 

:Connect NODE2

ALTER DATABASE [AdventureWorks] SET ONLINE

GO

 

 

--Finally enable the read-only access for all the secondary replicas

 

:Connect NODE1

 

USE[master]

GO

ALTER AVAILABILITY GROUP [AdventureWorksAG] MODIFY REPLICA ON N'NODE1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))

GO

ALTER AVAILABILITY GROUP [AdventureWorksAG] MODIFY REPLICA ON N'NODE2' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))

GO

ALTER AVAILABILITY GROUP [AdventureWorksAG] MODIFY REPLICA ON N'NODE3' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))

GO

 

 

References

Creation and Configuration of Availability Groups (SQL Server)

ALTER DATABASE (Transact-SQL)

BACKUP (Transact-SQL)

RESTORE (Transact-SQL)

 

Tested on
  • Microsoft SQL Server 2012 Enterprise Edition service pack 1 with build 11.00.3000

 

 

 

Author:

Raghavendra Srinivasan , Support Engineer, Microsoft India GTSC

Reviewed by:

Karthick Krishnamurthy, Technical Advisor, Microsoft India GTSC

Comments

  • Anonymous
    February 08, 2014
    Didn't you say you were moving the files to D:Data ? The script appears to read like it would move the files to C:Data.

  • Anonymous
    February 09, 2014
    The comment has been removed

  • Anonymous
    March 20, 2014
    Nice article Rags. I noticed some typos though. In all the places where you're disabling XP_Cmdshell, you're setting it to 1, not 0. For example: --Disable XP_CMDSHELL sp_configure 'show advanced options',1 go reconfigure go sp_configure 'xp_cmdshell',1   ---this should be 0, not 1 go reconfigure go

  • Anonymous
    December 14, 2014
    Excellent Article Karthik! I successfully moved databases to different drive using your article. Thanks.

  • Anonymous
    January 09, 2015
    The comment has been removed

  • Anonymous
    March 09, 2015
    Hi, The given steps are almost correct but when you perform step to set database online you will get error that saying "The operation cannot be performed on database X because it is involved in a database mirroring session or an availability group..." I wish to add a steps in the existing steps: After file moved on NODE1 and NODE3 when there is a step to set the secondary db online, instead of doing this we need to pause replication for the database on NODE2 which is primary node. Then perform failover to its original node i.e. NODE1, move file on NODE2 and resume replication for the database on NODE2 and NODE3 which is in pause mode, wait for some time and see the DB will be in sync. Thanks. Amarnath Khandimalla.

  • Anonymous
    June 13, 2015
    Please either correct or remove this article as currently it is wrong as it is. People have "broken" their environments using your article. This is very unprofessional. There are too many errors in it. Where is your quality control? Have you actually run your own scripts that you have posted?

  • Anonymous
    June 23, 2015
    Unfortunately we found your method to be a little risky so I tried the following with success. From Primary, SUSPEND data movement between replicas Goto Secondary New query Select master database -- set the new location for LOG FILE Run command:  ALTER DATABASE [QP1] MODIFY FILE (NAME='QP1LOG1',FILENAME='I:QP1_TLogQP1LOG1.ldf') Restart the sql server instance to break the file handles before running the next step Connect to secondary New query Select master db --move the log file Run command:  xp_cmdshell 'move "Q:QP1LOGMNT1QP1LOG1.ldf" I:QP1_TLog' Restart the sql server instance Resume data movement Check dashboard for successful synch status. Failover to next node. Repeat steps above from 'SUSPEND data movement...'

    • Anonymous
      December 16, 2016
      I can vouch that this method works. Just implemented this on 3 Node cluster availability group.

  • Anonymous
    June 24, 2015
    The script mentioned above is tested and works as expected. In the step-4 we are able to successfully move the physical files (both data & log), which shows that there is no handle on the database files and hence the state of the database on secondary replicas should be "Not Synchronizing/ Recovery Pending". Once we try to get the database online on the secondary replicas the synchronization starts again. Note:- The above steps mentioned doesn't involve RESTART of SQL Server service which would affect other production databases which are not part of the Availability group on we are working on.

    • Anonymous
      December 16, 2016
      Is this possible without requiring a restart of SQL Server service on secondary instances?Thanks
      • Anonymous
        December 16, 2016
        My question is more specific to secondary instances and I don't want to do relocation on primary instance. I do not want to fail over primary to clear file handles on secondary instances.Thanks

  • Anonymous
    October 22, 2015
    I am getting this error on the step to move the files to a new location;  "The process cannot access the file because it is being used by another process."  What am I missing?

  • Anonymous
    February 02, 2016
    Great post! It helped me. Thank you!

  • Anonymous
    August 23, 2016
    I'm attempting this procedure on a 4 node AlwaysOn setup 2 secondary nodes are sycronous, the last is async, and I get the following error when attempting to move the files using xp_cmdshell:The process cannot access the file because it is being used by another process.Any assistance is greatly appreciated!

  • Anonymous
    September 08, 2016
    Thanks for this post! However, I recently had to do this and I didn't have to do most of these steps. Here's what I did:1) Make sure you're on a secondary node2) Disable AlwaysOn from SQL Server Configuration Manager and restart the database service3) Take the DB offline (if necessary. my secondary nodes aren't readable so I didn't have to do this)4) Alter the DB to point to new file locations5) Physically move the existing files6) Enable AlwaysOn and restart the serviceWorked like a charm for me. When you make the change on your primary node, just make sure you do a manual failover first so it becomes a secondary node.

  • Anonymous
    October 10, 2016
    Hi Raghavendra Srinivasan, Have you tested this on SQL 2016 - I'm just not able to get it to work, however I did it successfully on another setup on SQL 2012 about a year ago??