Query the fully qualified connection string for the AlwaysOn Connection

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'SYSTEMCurrentControlSetservicesTcpipParameters',

    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 = 'SQLServerSQLInstance'
#$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

Query the fully qualified connection string for the AlwaysOn Connection

Rate

Share

Share

Rate