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 many databases 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, since I had many databases. 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

GUI: Expand the AG group and right click the DB –>suspend data movement

• Change the Readable secondary to ‘NO’ for all the secondary replicas, otherwise you will get an error.
• To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.

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

• 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

GUI: Expand the AG group and right click the DB –>resume data movement

• 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 in secondary replica and resume the database, the resume database command will create a data file.

ALTER DATABASE <db name> SET HADR RESUME;

Added:

After a reply from Richard L. Dawson – I thought to add this as well. Thanks for that. This will help others as well.

You cannot take the database offline in both primary and secondary replica, unless we remove from alwaysON. Removing the database in the primary will make your secondary database into the recovery mode it is a default configuration.

Same as for secondary, you cannot remove it, it will make the databases into recovery mode and moreover the database will not remove from AG group.

Msg 1468, Level 16, State 1, Line 1

The operation cannot be performed on database “DB” 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, Line 1

ALTER DATABASE statement failed.

So moving physical database files needs a down/offline, we cannot move database and it requires the database need to be standalone not in the AG. We can run the alter database command and maybe can try this “DBCC SHRINKFILE (A, EMPTYFILE)”. We know, how tough this is. http://www.bobpusateri.com/archive/2013/03/moving-a-database-to-new-storage-with-no-downtime/

We can remove the database from AG and can do this like, the process we follow for the normal database. If you have only one database and can afford downtime. Yes, we can do this. (OR) we can completely start from the scratch. There are many methods.

(1.Remove database from AG 2. Alter database and modify the file 3. Set offline 4. Cut & paste the physical file to the new location 5. Set back to the database online 6. Add the database into AG)

Note: If any log backup run during this process the LSN will not match with all the secondary replica, you need to restore with norecoery and need to add the database.

http://www.sqlserverblogforum.com/2016/08/how-to-solve-the-lsn-mismatch-in-sql-server/