SQL server version is SQL Version 2016

Current setup is two nodes in primary having always on availability group. Adding a 3rd node which is DR(Disaster and Recover).

Does anyone knows a power shell script that automates failover from primary to DR once Primary(1st and 2nd node) becomes unavailable.

  • If your DR site is connected with a good connection, you could have it set to "syc" with automatic failover... that should trigger a failover if you have your Quorum configured correctly. But I'm guessing you can't do that as you ask for a powerShell script... This is a pretty tricky tool (specially if you are in Async) as it could lead to data loss... So if this is really what you want to do, you will need to bring your cluster up (cause it will probably lose Quorum if you main DC is down) and then run the TSQL query to do a failover with allow_data_loss. – Dominique Boucher Jul 15 '20 at 17:43
  • If you want more explicit details, please provide as much details as possible regarding your actual configuration – Dominique Boucher Jul 15 '20 at 17:43
  • Apologies if i did not give much details. But yes the 1st and 2nd node will be in synchronous. And DR will be in asynchronous which means data loss will be there... – C1ark05 Jul 15 '20 at 17:56
  • Do you have a non-prod env. where you can test it ? How is your cluster Quorum configured ? – Dominique Boucher Jul 15 '20 at 17:58
  • I know that 1st node to 2nd is automatic failover while 1st node to DR, it id a manual failover. But requirement is to automate it. If not via power sheel script, iam thinking of doing it via TSQL script and using a batch script. The T-SQL script i am thinking of exporting the scripts from tha manual failover wizard... – C1ark05 Jul 15 '20 at 18:01

There is good change that you need to fix your Quorum first (so that the cluster service can run and that the listener and the AG's databases can be used) See : https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/force-a-wsfc-cluster-to-start-without-a-quorum?view=sql-server-ver15#:~:text=Failover%20Cluster%20Manager-,To%20force%20a%20cluster%20to%20start%20without%20a%20quorum,Force%20my%20cluster%20to%20start.

Once your cluster is back up, then you will have to trigger a failover of your AG with allow_data_loss : https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql?view=sql-server-ver15

I would be really cautious with such a script as it may cause data Loss. You should also plan the "how to" to get back to your normal DC once the outtage is fix.

This is the kind of thing you probably want to practice on so I strongly recommand that you get some non-prod VM to set it up and test it.

  • Hmm... so you suggest of reconfiguring qurom first? Sorry but I forgot to tell you that other servers than the database servers, everything will be under SRM(site recovery manager) of vmware....and requirement is to automate this failover.. – C1ark05 Jul 15 '20 at 18:40
  • What should be the setup with the quorom if i need to change it? – C1ark05 Jul 15 '20 at 18:44
  • I don't think I would change the Quorum configuration, I would force the quorum fix in a DR scenario. I have read somewhere that you should only gives vote to the nodes that can be primary (automatically). So I guess I would keep the same quorum config: 1 vote per node in primary DC + file share withness in DC1 and no vote for your node in DR. Then, if a DR happen, I would fix the quorum on the DR node and force the failover. Now if you script thoses steps, you need to make sure you will execute this script only in a real DR scenario (otherwise, you could end up with a brain split scenario) – Dominique Boucher Jul 15 '20 at 19:09
  • Current setup is 1st node gets 1 vote,2nd node gets 1 vote and FSW gets 1 vote.(FSW is also in DC, not DC1. I got confuse with your example. – C1ark05 Jul 15 '20 at 19:14
  • What do you mean by fix the qurom on the DR? DR doesn't have a vote. – C1ark05 Jul 15 '20 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.