How to move databases configured for SQL Server AlwaysOn

How to move databases configured for SQL Server AlwaysOn

Rate This
  • Comments 9

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

Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post
  • 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.

  • Hi Expert, Thanks for posting so nice article, I got following error when Initiated the database recovery,

    1> USE master

    2> GO

    Changed database context to 'master'.

    1> ALTER DATABASE [MSCRM_CONFIG] SET ONLINE

    2> GO

    Msg 1468, Level 16, State 5, Server CRM-01, Line 1

    The operation cannot be performed on database "MSCRM_CONFIG" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

    Msg 5069, Level 16, State 1, Server CRM-01, Line 1

    ALTER DATABASE statement failed.

    Any help would be appreciated.

    Thanks & Regards

  • 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

  • Excellent Article Karthik! I successfully moved databases to different drive using your article. Thanks.

  • Thank you for the article.

    I am having the same issue as Potti: When I try to set the dB online to initiate recovery, I get the error:

    The operation cannot be performed on database X because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

    Msg 5069, Level 16, State 1, Server Y, Line 1

    It's strange because I've tried a similar script in a dev environment and it works fine.

    Potti, did you find a solutions?

    Thank you,

    Dave

  • 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.

  • 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?

  • 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_TLog\QP1LOG1.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:\QP1\LOGMNT1\QP1LOG1.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...'

  • 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.

Page 1 of 1 (9 items)