Microsoft SQL Server

SQL Server Core Engineer Tips

How to create a dependency between two or more Availability Groups

How to create a dependency between two or more Availability Groups

  • Comments 1
  • Likes

Hi all,


Problem description: I got a specific scenario where I have to failover two or more AlwaysOn Availability Groups in the same time.  To explain by another way, I have to create a dependency between 2 Availability Groups. If one of them failover, the second one has to move also on the same node.

In my case, I have to do that because, I’m not allow to put in the same group all databases. And a third application has to perform a query (write/read) between N databases from two different 2 Availability Groups.



Action plan: As it’s not a normal or expecting behavior, I will share with you, how I did to perform this task.


Step 0: Copy past the script PowerShell below on all nodes involved (SQL_Node_1 and SQL_Node_2 and SQL_Node_3) inside C:\HADRON\failoverAutomaticHadronGroup.ps1


$Cluster_Name =  "myWindowsClusterName";


$my_AG1_Name ="AG1";
$my_AG2_Name ="AG2";

# We are looking for the current node of my_AG1_Name
$AG_1  =get-clustergroup  -name$my_AG1_Name-Cluster$ClusterName|select-object  OwnerNode,State


# then We are looking for the current node of my_AG2_Name
$AG_2=get-clustergroup  -name"IngestPub"-Cluster$my_AG2_Name|select-object  OwnerNode,State


if($AG_1.OwnerNode.Name -ne$currentTriggerNode  -and$AG_2.OwnerNode.Name -eq$currentTriggerNode )
          # so we move the group AG_1 to the same node of AG2
          Move-ClusterGroup"$my_AG1_Name"–Node$AG_2.OwnerNode.Name -Cluster$ClusterName ;
elseif ($AG_2.OwnerNode.Name -ne$currentTriggerNode  -and$AG_1.OwnerNode.Name -eq$currentTriggerNode )
          # so we move the group AG_2 to the same node of IngestPub
          Move-ClusterGroup"$my_AG2_Name"–Node$AG_1.OwnerNode.Name -Cluster$ClusterName




Step 1: Open eventvwr.msc

Step 2: Go on the chanel:  Application and Services Logs\Microsoft\Windows\FailoverClustering\Operationnal

Step 3: Select eventID 1201 (The Cluster service successfully brought the clustered service or application 'XXXXXX' online.)

Step 4: Right-click -> Attach Task to this Event

Step5: Check start a program


Step 6: Select the powershell script of the step 0


  •  Program/script : powershell.exe
  •  Add arguments : -Noninteractive –Noprofile –Command "&{C:\HADRON\failoverAutomaticHadronGroup.ps1}"


NB: To modify the task, open Task Scheduler, please go to the  Windows  Scheduled Task Microsoft-Windows-FailoverClustering_Operational_Microsoft-Windows-FailoverClustering_1201.





Michel Degremont | Xbox Live Music - DBA Team - SQL Server & PDW |



  • If you have an issue with Error Value: 2147750687" and a warning with Task Scheduler did not launch task "name" because instance "GUID" of the same task is already running, logged in the Microsoft-Windows-TaskScheduler/Operational log

    Please have a look to the kb:

    Duplicate triggers are generated incorrectly in scheduled tasks in Windows Vista or in Windows Server 2008

    Other similar: The Task Scheduler service runs the same job two times in Windows Server 2008 or in Windows Vista


Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment