AlwaysON下迁移数据库----无需破坏HADR

Move database without breaking alwaysON

 

This post is going to show the database movement from one drive to another drive, without breaking the database from alwaysON configuration. An application has created the database to both primary and secondary replica servers to the default location of C drive.

There are methods like detach/attach, backup/restore & alter database. For alwaysON HADR servers, the best method is Alter database. Since it is in the mirror/sync mode. Detach/attach will not work and backup/ restore, we need to break databases from the HADR.

Note:

It’s a two node synchronous replica, if you have more replica, you should plan each well.

If you have any standalone database (Without adding DBs into HADR), you need to plan for the downtime. Since, the secondary replica servers need to take down.

In primary, for high transaction system, make sure you have good space for transaction files.

 

Steps:

Before going to start the database movement, write the script for each step and you can write a dynamic SQL for larger number databases. That’s what I did. It will minimize the time.

 

• In primary server, suspend the database movement to all secondary databases. It will stop the sending the log to the secondary, which can increase the log size https://msdn.microsoft.com/en-us/library/ff878303.aspx

ALTER DATABASE <DB name> SET HADR suspend

• Change the Readable secondary to ‘NO’ for all the secondary replicas, otherwise you will get an error.

Right click the primary replica alwaysON group –> properties –> Readable secondary  –> No

To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.

 

• Note down all the files and file location from the system tables.

 

select db_name(a.database_id),a.name,a.physical_name,size/128.0 AS CurrentSizeMB,*

from sys.master_files a join sys.databases b

on a.database_id =b.database_id

–where a.physical_name like ‘c%’

–and a.database_id >4

–and type_desc <>’rows’

order by a.name

 

• On the mirror server run the “ALTER DATABASE <DB name> MODIFY FILE” command. You need to run this for each database.

 

use master

ALTER DATABASE <DB_name> MODIFY FILE (NAME =<logical name>

,FILENAME =’F:\SQL_DATA\DB_name.mdf’)

 

• On the secondary replica server stop the SQL Server instance.

 

• Move the database file (MDF & LDF) files to the changed location (Cut & Paste).
• Start the SQL Server instance and check the file locations using the above query.

 

• In primary replica server resume the database by using the following ALTER DATABASE statement:

 

ALTER DATABASE <DB name> SET HADR Resume.

 

• Change the Readable secondary to ‘Yes’

Fail over and repeat the steps for the partner server.

Additionally, if you add any files in the primary and the folder name is incorrect in the secondary, the database will go into suspend mode.

Just check the error log, you can get more info on, why the database is suspended mode.

Error: 5123, Severity: 16, State: 1.

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘L:\SQL_log\DB_name.ndf’.

 

Created the folder and resume the database, the resume database command will create a file.

ALTER DATABASE <db name> SET HADR RESUME;

转自：http://www.sqlservercentral.com/blogs/sql-server-blog-forum/2016/08/03/alwayson-move-database-without-breaking-hadr/