SQL Server Utilities - Monitor AlwaysOn Availabilty Groups

One of my client’s has recently downgraded his SQL Servers from Enterprise Edition to Standard Edition to cut costs. The 2 SQL Servers are configured in AlwaysOn Availability Groups for HA and host 26 user databases which on the Enterprise Edition were part of the same single AlwaysOn Group. Now moving to SQL Server Standard Edition means that now we only have Basic Availability Groups where each database will be part of it’s own dedicated group making a total of 26 different AlwaysOn Groups.

Ideally, we could have a dedicated listener per each group allowing each group to be independent of any other group. In such a configuration we would not care about which instance would host which groups so the groups can be floating around between the 2 instances serving user requests.

But in the specific environment there is a cross database dependency enforcing us to have all groups located on the same instance. This means that we will have a single Listener for all the groups and we have to assure that all groups are hosted by the same SQL Server instance.

To meet this requirement I have used the Listener as the anchor/reference point to determine which instance is the instance that should host all databases. I have created a Powershell script that enforces this logic by implementing the following steps:

Get the Listener/VNN Cluster resource to determine the Owner Node

Get the SQL Server Availability Group resources to determine the Owner Node

Per each SQL Server Availability Group find if the Owner Node is different than the Listener Owner Node

Any SQL Server Availability Group that meets that condition will be Failed over

Since a failover for each group takes few seconds to complete failing over 20+ groups crossed a minute so I had to modify the code to be multi-threaded which I have done using the RunspaceFactory class to carry out the tasks in parallel. This makes failing over all the groups complete in just few seconds.

”

Powershell script to enforce all SQL Servers Availability Groups are hosted on the same SQL Server instance.

[bool]$user_interactive = [Environment]::UserInteractive;

try
{       
    # Get the vnv/listener in order to determin the active node that should host all the availability grroups.
    $vnn_obj = Get-ClusterResource | Select-Object Name, OwnerNode, OwnerGroup, ResourceType, State | Where-Object {$_.ResourceType -eq "IP Address" -and $_.OwnerGroup -ne "Cluster Group" -and $_.State -eq "online";}
    if ($user_interactive) {Write-Host 'The VNN is owned by' $vnn_obj.OwnerNode.Name -ForegroundColor Green}; 

    # Get the availability groups.
    $ags_obj = Get-ClusterResource | Select-Object Name, OwnerNode, OwnerGroup, ResourceType, State | Where-Object {$_.ResourceType -eq "SQL Server Availability Group";}
    
    $dictionary = New-Object System.Collections.Generic.Dictionary"[int, String]";
    $counter = 1;


    # Loop over the ags. 
    foreach($ag in $ags_obj)
    {            
        # Find if an ag is located on a node other than the node hosting the vnn.
        if ($ag.OwnerNode.Name -ne $vnn_obj.OwnerNode.Name)        
        {
            [string]$query = 'ALTER AVAILABILITY GROUP [{0}] FAILOVER;' -f $ag.Name;
            if ($user_interactive) {Write-Host $query -ForegroundColor Yellow}; 

            # Populate an object with the ags that we need to failover.
            $dictionary.Add($counter, $query);
            [string]$server = $vnn_obj.OwnerNode.Name;
                        
            $counter++;
        }
    }


    # Loop over the ags that we need to failover.
    # Execute the task in parallel to shorten failover time.
    foreach ($key in $dictionary.Keys)
    {
       if ($user_interactive) {Write-Host "$key  $($dictionary[$key])" }; 
       
       [string]$query = $dictionary[$key];

        $script_block = 
        {
            param
            (               
               $server                       
              ,$query 
            )         
               
           try
           {
               $connection_string = "Server=$Server; Database=master; Integrated Security=true; Application Name=AGMonitor;";

               $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($connection_string);            
               $SqlCommand = $SqlConnection.CreateCommand();
               $SqlConnection.Open(); 
               $SqlCommand.CommandText = $query;       
               $sqlCommand.ExecuteNonQuery();  
           }
           catch [Exception] {Throw; }    
        }  
              
        
        $RunspacePool = [runspacefactory]::CreateRunspacePool(1, 30);
        $RunspacePool.Open();
        
        $Jobs = @();

        $PowerShell = [powershell]::Create();
        $PowerShell.RunspacePool = $RunspacePool;
        $PowerShell.AddScript($script_block).AddParameter("server",$server).AddParameter("query",$query);

        # Start the threads
        $Jobs += $PowerShell.BeginInvoke();  
    }

    # Wait for threads to complete.
    while ($Jobs.IsCompleted -contains $false)
    {        
        Start-Sleep -Milliseconds 10;       
    }
    $RunspacePool.Close();
    $RunspacePool.Dispose(); 
}
catch [Exception] 
{        
    $exception = $_.Exception;
    if ($user_interactive) {Write-Host -ForegroundColor Red $exception.Message};         
}    

#finally
#{
#    $dictionary = $null;
#}