How to move databases configured for SQL Server AlwaysOn

 

Database Engine
blogs		 Analysis services
blogs		 Reporting services
blogs		 Integration services
blogs		 Data access tech     blogs

Database Engine videos Analysis services videos Reporting services videos Integration services videos Data access tech   Videos
Common Tasks
Search
Tags
Archives
Archives
MSDN Blogs > Microsoft SQL Server Tips & Tricks > How to move databases configured for SQL Server AlwaysOn

How to move databases configured for SQL Server AlwaysOn

Rate This
6 Feb 2014 12:36 AM
  • Comments 3

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 AdventureWorks2012 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 – AdventureWorks2012

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

Log file location  : C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks2012.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='C:\DATA\AdventureWorks.mdf')

go

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

go

 

:Connect NODE2

 

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

go

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

go

 

:Connect NODE3

 

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

go

ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME='AdventureWorks_log',FILENAME='C:\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*.*" C:\data\'

go

 

--Disable XP_CMDSHELL

sp_configure 'show advanced options',1

go

reconfigure

go

sp_configure 'xp_cmdshell',1

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*.*" C:\data\'

go

 

--Disable XP_CMDSHELL

sp_configure 'show advanced options',1

go

reconfigure

go

sp_configure 'xp_cmdshell',1

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*.*" C:\data\'

go

 

--Disable XP_CMDSHELL

sp_configure 'show advanced options',1

go

reconfigure

go

sp_configure 'xp_cmdshell',1

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 5 and 4 and type the answer here:
  • Post
Comments
  • Rob Hendricks
    8 Feb 2014 5:56 PM

    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.

  • potti
    10 Feb 2014 1:03 AM

    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

  • 20 Mar 2014 3:05 PM

    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

Page 1 of 1 (3 items)