Query the fully qualified connection string for the AlwaysOn Connection

While we do maintain an exact inventory of sql servers and their configuration, its too numerous and I often find myself in a need to look up AG connection string for a particular database. This is especially true if there are multiple Availability Groups setup.

But I will spare the details on how and why things are the way they are here.

Here is the SQL script that I use to look up connection string used for a specific AG database or for all AGs defined in the current SQL Server instance:

/* GET FULLY QUALIFIED SQL CONNECTION STRING FOR ALWAYSON DATABASE

There is only one parameter, @dbname, if you are looking to get connection string 

for a single database

So, if the value for @dbname is null or an empty string, this script  will return 

connection strings for all AGs defined in the current sql session

*/

declare @dbname nvarchar(500)
set @dbname = ''

-- Read the domain name suffix for the current server that we will
-- append to the listener's DNS Name.
DECLARE @DomainName NVARCHAR(100)
EXEC master.dbo.xp_regread 

    N'HKEY_LOCAL_MACHINE', 

    N'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', 

    N'Domain',@DomainName OUTPUT

-- throw an error if @dbname provided does not exist on the current sql server
IF db_id(@dbname) is null and @dbname != ''   

   RAISERROR('No database found with name %s.', 16, 1, @dbname)

-- check to see if the @dbname is an AG database
ELSE IF db_id(@dbname) is not null and @dbname != ''  and 

    NOT EXISTS (select * from sys.availability_databases_cluster 

                where database_name = @dbname

               ) 

   RAISERROR('Database is not part of an Availability Group: %s.', 16, 1, @dbname)

ELSE IF SERVERPROPERTY('IsHadrEnabled') = 1
BEGIN
	DECLARE @ClusterNodes VARCHAR(8000) 
	SELECT @ClusterNodes = COALESCE(@ClusterNodes + ', ', '') + node_name
	FROM (select distinct node_name 

               from sys.dm_hadr_availability_replica_cluster_nodes) a

	select   UPPER(case when SERVERPROPERTY('InstanceName') is null then dns_name
	           else CONCAT(dns_name, ISNULL(CONCAT('.', @DomainName), ''), '\', CAST(SERVERPROPERTY('InstanceName') as nvarchar(100)))  end) ConnString, 
			 UPPER(ag.name) AGName,
			 UPPER(dns_name) DNSName, 
			 UPPER(CAST(SERVERPROPERTY('InstanceName')  as nvarchar(100))) SQLInstance, 
			 UPPER(@ClusterNodes) ClusterNodes,
			 port TCPPort
			 from sys.availability_groups ag
			 inner join sys.availability_group_listeners agl on ag.group_id = agl.group_id
			 where cast(agl.group_id as varchar(100)) like case when @dbname is null or @dbname = '' then '%' 
			       else (select cast(group_id as varchar(100)) from sys.availability_databases_cluster where database_name = @dbname) END

END
ELSE IF SERVERPROPERTY('IsHadrEnabled') = 0 

      RAISERROR('SQL Server is not an AlwaysOn Cluster.', 16,1)
ELSE
      RAISERROR('Unknown error occurred.', 16,1)

The lowest version of SQL I have tested this on is SQL 2016 SP2 and the highest version SQL 2019 RTM.

I hope you may also find this script useful.  if you find or run into any bug or a logical error, please let me know and I will do my best to fix it asap.

And, here is a one written in PowerShell:

$sql_server = 'SQLServer\SQLInstance'
#$db_name = "AppDB"

$sql_conn = get-sqlinstance -ServerInstance $sql_server
$sql_instance_name = $sql_conn.InstanceName


if (!$sql_conn.IsHadrEnabled)
{
   Throw "AlwaysOn AG is not enabled on $sql_instance"
}
else {$ag = ($sql_conn.AvailabilityGroups)}

if($db_name -ne $null -and $db_name -ne '')
{
        $sql_db = get-sqldatabase -Name $db_name -InputObject $sql_conn
        $db_ag_name = $sql_db.AvailabilityGroupName
        if(!$db_ag_name)
        {
            Throw "Database ($db_name) is NOT part of an Availablity Group"
        }
        else
        {

            "Database ($db_name) is part of AG: $db_ag_name"
            $ag = ($sql_conn.AvailabilityGroups) | where {$_.Name -eq $db_ag_name}
            $is_primary_replica = $sql_db.IsLocalPrimaryReplica()
            "Is local server primary replica: $is_primary_replica"
            $primary_replica = $ag.PrimaryReplicaServerName
            "Primary replica for Availability Group ($db_ag_name) is: $primary_replica"
        }


}

$ag_listeners = $ag.AvailabilityGroupListeners

$ag_listeners | Add-Member -MemberType ScriptProperty -Name fqdn_ag_conn_string -Value  {[System.Net.Dns]::GetHostEntry($this.Name).HostName  + "\" + $sql_instance_name}
$ag_listeners | Add-Member -MemberType AliasProperty -Name ag_name -Value  Parent
$ag_listeners | select ag_name, fqdn_ag_conn_string