Sql server Blog Forum Helping SQL server DBAs and Developers

3Aug/168

AlwaysON move database without breaking 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 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.

 

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/

 

 

Muthukkumaran Kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts

Comments (8) Trackbacks (0)
  1. Question. Does setting the database offline work as it does when you need to move a replicated database? This would preclude you having the shut down the entire node for one db.

  2. Thanks muthu. that was useful. expecting more like complete alwayson setup or geo clustering topic from u…

    • Hi Vinod,

      I am glad it helped you. I have more pending posts for AlwaysON issue happen in my environment. Still, need to catch those.

      It is easy to setup AlwaysON, Only place we stuck WSFS & Listener creation. In general, Infra team will build the WSFS and Listener you can get help from AD admins for CNO etc.

      I suggest you to start Perry Whittle’s Starway series. If I have some time, surely I will write an alwaysON setup configuration post.

      http://www.sqlservercentral.com/stairway/112556/

  3. Hi Muthu,

    I have a question. For moving database file, is it required to suspend data movement only for that particular db or all the dbs in that AG group ?


Leave a comment

No trackbacks yet.