I am unable to find the specific command that would return the local replica role(i.e. Primary or Secondary) for a SQL Virtual Machine.

I am able to view this role using azure portal in two ways:

1. Via SQL configuration >> SQL Extension configuration.
2. Via clicking "High Availability" from Right hand side pane.

I tried some commands like:

az vm extension show --resource-group <your-resource-group-name> --vm-name <your-sql-vm-name> --name SqlIaasExtension --subscription <your-subscription-id> --output json

az sql vm show --name <sql-vm-name> --resource-group <your-resource-group-name>

az sql vm show -g <your-resource-group-name> -n <sql-vm-name> --query "properties.availabilityReplicas[*].role"

However, I am not able to get this local replica role(i.e. Primary/Secondary) using Azure CLI. I need to get this details my patching automation.

Note: I am not allowed to do this by sql login to the these servers. I need to find this using my Azure cloud access.

I am unable to find the specific command that would return the local replica role(i.e. Primary or Secondary) for a SQL Virtual Machine.

I am able to view this role using azure portal in two ways:

1. Via SQL configuration >> SQL Extension configuration.
2. Via clicking "High Availability" from Right hand side pane.

I tried some commands like:

az vm extension show --resource-group <your-resource-group-name> --vm-name <your-sql-vm-name> --name SqlIaasExtension --subscription <your-subscription-id> --output json

az sql vm show --name <sql-vm-name> --resource-group <your-resource-group-name>

az sql vm show -g <your-resource-group-name> -n <sql-vm-name> --query "properties.availabilityReplicas[*].role"

However, I am not able to get this local replica role(i.e. Primary/Secondary) using Azure CLI. I need to get this details my patching automation.

Note: I am not allowed to do this by sql login to the these servers. I need to find this using my Azure cloud access.

• azure
• azure-devops
• azure-sql-database
• azure-sql-managed-instance

2 Answers 2

I figured out a solution for this task. Here is my solution:

function Get-SqlNodePrimaryStatus {
param (
    [Parameter(Mandatory=$true)]
    [string]$SubscriptionID,
    [Parameter(Mandatory=$true)]
    [string]$ResourceGroupName,
    [Parameter(Mandatory=$true)]
    [string]$VMName
)

try {
    Write-Output "Switching to subscription: $($SubscriptionID)..."
    Set-AzContext -Subscription $SubscriptionID -ErrorAction Stop | Out-Null
    Write-Output "Successfully switched to subscription: $($SubscriptionID)"

    $vmStatus = Get-AzVM -ResourceGroupName $ResourceGroupName -Name $VMName -Status
    
    # Look for SqlIaasExtension
    $sqlExtension = $vmStatus.Extensions | Where-Object { $_.Type -eq "Microsoft.SqlServer.Management.SqlIaaSAgent" }
    
    if ($sqlExtension) {
        # Find the Resource Provider Plugin substatus
        $rpStatus = $sqlExtension.Substatuses | Where-Object { $_.Code -eq "ComponentStatus/Resource Provider Plugin/succeeded" }
        
        if ($rpStatus -and $rpStatus.Message) {
            # Convert the JSON message to an object
            $statusObj = $rpStatus.Message | ConvertFrom-Json
            
            # Check for HighAvailability and SqlAvailabilityGroups
            if ($statusObj.HighAvailability.SqlAvailabilityGroups) {
                # Return the IsCurrentNodePrimary value from the first AG
                return [System.Convert]::ToBoolean($statusObj.HighAvailability.SqlAvailabilityGroups[0].IsCurrentNodePrimary)
            }
        }
    }
    
    return $false
}
catch {
    Write-Error "Error checking primary status: $_"
    return $false
}
}

Example usage:

$status = Get-SqlNodePrimaryStatus -SubscriptionID "<your-subscription-name>" -ResourceGroupName "<your-resource-group-name>" -VMName "<your-vm-name>"
Write-Output $status

You can do this in powershell, querying the DMVs, eg

$row = invoke-sqlcmd "select sys.fn_hadr_is_primary_replica ( 'dbname' ) is_primary"
$val = $row[0]

You can use your "Azure cloud access" with az vm run-command

https://learn.microsoft/en-us/sql/relational-databases/system-functions/sys-fn-hadr-is-primary-replica-transact-sql?view=sql-server-ver16