How to create a dependency between two or more Availability Groups

  • Article

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

 

import-modulefailoverclusters
$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.

 

 

 

Regards,

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