Finding the Primary Replica of an Availability Group

This is going to be a bit of a quick post but one about something that I see coming up time and time again on SQL Undercover’s search terms.

How do I find an availability group’s primary server?

A while back, Adrian put together a bunch of queries for Always On Availability Groups,

7 ways to Query Always On Availability Groups using SQL
7 more ways to Query Always on Availability groups

But he never actually answered that particular question.

That’s because, from within SQL there’s no simple way of finding out what the primary server is from a secondary replica.

Take a look in SSMS, and you’ll only see the node you’re connected to showing as ‘secondary’, all others appear with no role specified.

From the primary we can see the role of all servers…
…but from the secondary we only see the role of the server that we’re connected to

And the system tables aren’t much more use.

Ok, so with a two node AG this isn’t a huge issue but it you’ve got four or five nodes, figuring out who’s primary can be a little frustrating.

So what can we do?

Well we could take a look in the Undercover Catalogue if you happen to have it installed, but that’s not what this post is about (I just wanted to get a plug in 😉 ).

Our friend here is PowerShell or more specifically, the SMO.

Point the following script at any server that you know is part of the AG that you’re interested in and, primary or secondary, it’ll return all AGs that the server is part of and it’s current primary server.

#Find an availability group's primary server
#set $ServerName to any node that's part of the AG that you're interested in

$ServerName = 'SQL01'

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName;
$svr.ConnectionContext.StatementTimeout = 0;

foreach ($AvailabilityGroup in $svr.AvailabilityGroups)
{
    Write-Host "$($AvailabilityGroup.Name) : $($AvailabilityGroup.PrimaryReplicaServerName)"
}
we can see that even when pointing the script at SQL01, the primary replica for both AGs is SQL02.

It’s a simple script but one that I see a lot of people searching for, so I hope it’ll be of use to someone.

Thanks for reading

One thought on “Finding the Primary Replica of an Availability Group

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: