Sql server Blog Forum Helping SQL server DBAs and Developers


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.


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.


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.



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:



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.




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.




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


Create and setup a SQL clustering on your Desktop or Laptop

How to create and setup a SQL clustering on your Desktop or Laptop


I thought to write this post after I discussed in a forum. SQL clustering is a bit complicated one. I mean most of the DBAs are not configured or tested it, because we don’t get a chance to do or to learn SQL clustering. Also it needs bit knowledge from out of SQL (Windows AD, Network and Storage (SAN)).

In this post, I will give you an idea how to configure SQL failover clustering to yourself. We are doing everything in virtual environment (Single machine) the same you can do physically but you need all in physically in production environment (Machine, Domain, Storage & network). It’s not possible to show all the steps and screen shot here because it has more than 40 screenshots. I have prepared a document and will share with you if needed.

What is SQL clustering?

In general cluster means more than one.

SQL clustering is a collection of two or more servers access the data from same single shared storage (SAN), if one server failed another one server will take the data from the storage and the application will be running without any problem.

I suggest readers to read Kendra Little's article. It's good article and she explains with diagram.



The followings are the software for setup clustering.

Software Free/Trail URL location
VMware workstation 30 day trial VMware workstation
Starwind 30 day trial Starwind iscsi SAN
Windows server 2008 60 day trial Windows server 2008
SQL server 2008 180 day trial SQL server 2008


Planning the IP address

Component Name IP address
Domain controller xxx.xxx.1.6
Node1 / Machine1 xxx.xxx.1.7
Node2 / Machine2 xxx.xxx.1.8
Windows cluster xxx.xxx.1.9
MSDTC xxx.xxx.1.10
Virtual SQL server xxx.xxx.1.11
Private IP xxx.xxx.2.11
Public IP xxx.xxx.2.12



Installing VMware


Installing a VMware workstation is an easy one. Just click the typical and next.



Creating VMware


Creating three virtual machines using the windows trail edition and assigning the right amount of RAM, CPU & disk Space for each guest host. Configure the network adapter for public and private network for internet connection.


1. Domain controller (DC)


2. Machine1 (Node1)


3. Machine2 (Node2)

Creating Domain DC


The domain controller is also known as active directory.



  • Install and create a domain.

Creating Shared disk


We know SQL clustering needs a shared disk typically SAN. The use of StarWind’s ISCSI we can make our local disk as shared disk. Because we don't have a SAN storage server physically so i am creating the shared storage in the DC machine itself using StarWind’s iSCSI.


Format the disk on each guest and use it.

Creating and installing windows Failover cluster

  • Installing the windows fail over cluster.
  • Validate and add the nodes.


  • Assign the IP and Cluster the Nodes
  • Add the MSDTC



Installing SQL Failover cluster

Install the SQL server failover cluster on both the nodes. Its easy one just you can give the network IP address and choose the shared disk.

Second node will automatically display the SQL cluster name and we can just add it.


To view the Cluster admin go to run --> type CLUADMIN.MSC

Node1 (Muthu1) is the primary host. Which will be taking care of the SQL server and the database applications.


Use this command to check which node the cluster SQL server is currently running.




How do I know, Is the cluster automatically fail over the server, if anything goes wrong on the running node?

Just test it. Power off the Node1 (Muthu1) and you can see the SQL server automatically fail over to the node2 (muthu2).


When the SQL cluster will be down? Or Is clustering is disaster recovery?

SQL fail over clustering is not a disaster recovery. In case if the storage (SAN) is not available then clustering won't start.


Testing: Power off the DC. In our case DC machine has storage shared disk. Hence the shared disk (SAN) goes off, then SQL clustering will not work.


My special thanks go to my VM ware team.

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


SQL server Disaster Recovery plan

SQL server Disaster Recovery Options

This month’s TSQL Tuesday party is being hosted by Allen Kinsel (Blog | Twitter) and the topic this month is disaster recovery. It’s a DBA Topic/Job.

The disaster recovery is really “It depends”.

What is disaster recovery?

Disaster recovery is called DR. Hmm...

Disaster recovery is the best option for the business to minimize their data loss and downtime. The SQL server has a number of native options. But, as I already told everything is depends upon your recovery time objective RTO and recovery point objective RPO.

That is how much data can you afford to lose? How long can you wait? How much money do you ready to spend?

If you have answers for these questions then, you can choose your best DR plan.

Who knows what will happen tomorrow?

I have seen most of the company don’t have at least simple DR setup, Like keep the backup in safe DR site. The reason is they didn’t face any disaster yet.

Truncating and deleting the 'critical tables' also consider a big disaster. So guys keep the backup, backup and backup the database. Before going to set-up your DR plan asks the questions to DR Guru's via online (Like twitter, SSC and MSDN) and also read the DR Guru Paul Randal's white papers it will help.

I have recommended to watch the brentozar‘s DR video. Its amazing video and it covers the basics DR plan.

Seriously I have no idea about Geo cluster. Thanks to @sqlsoldier and @SQLpoolboy  for their twitter reply about the Geo cluster method.

For understandable I will classify the disaster recovery (DR) into three types. I am not going in depth just I will cover the basics of high availability HA and disaster recovery DR.

1. Data center disaster

2. Server(Host)/Drive (Except shared drive) disaster

3. Database/Drive disaster


Backup restore is common method its simplest (less expensive) and good option for disaster recovery but it’s depends upon your backup strategy and recovery model. The backup restores will work for all the above three DR methods. The DBA job is keeping the data as much as safe and application availability (Zero down time). That is test and keep your database backups safely try to automate the backup test and move them automatically to the DR site.

The backup restore does not good choose for big databases (TB) in case of any disaster, because the restoration takes more time to bring the database online. However you can speed up the backup restoration using the following SQL native features.


Clustering is a combination of one or more servers it will automatically allow one physical server to take over the tasks of another physical server that has failed. Its not a real disaster recovery solution because if the shared drive unavailable we cannot bring the database to online.

Clustering is best option it provides a minimum downtime (like 5 minutes) and data loss in case any data center (Geo) or server failure.

  • Clustering needs extra hardware/server and it's more expensive.

Database mirroring

Database mirroring introduced in 2005 onwards. Database Mirroring maintain an exact copy of a database on a different server. It has automatic fail over option and mainly helps to increase the database availability too.

  • Database mirroring only works FULL recovery model.
  • This needs two instances.
  • Mirror database always in restoring state.

For more details read Robert L. Davis's Pro SQL Server 2008 Mirroring book.

Log shipping

Log shipping is the process of automating the full database backup and transaction log on a production server and then automatically restores them on to the secondary (standby) server.

  • Log shipping will work either Full or Bulk logged recovery model.
  • You can also configure log shipping in the single SQL instance.
  • The Stand by database can be either restoring or read only (standby).
  • The manual fail over is required to bring the database online.
  • Some data can be lost (15 minutes).

Peer-to-Peer Transactional Replication

Peer-to-peer transactional replication is designed for applications that might read or might modify the data in any database that participates in replication. Additionally, if any servers that host the databases are unavailable, you can modify the application to route traffic to the remaining servers. The remaining servers contain same copies of the data.

Note: Database mirroring, logs shipping and replication also treated as DR if it's located in different data center. We can use both the HA and DR combination to set up the disaster recovery.

Again everything is "It Depends". Read Paul Randal's white papers before going to configure the DR.


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