How to create a dependency between two or more Availability Groups

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 |



Comments (1)

  1. Degremont Michel says:

    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…/2617046

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