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 |