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.
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)"
}
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