While we maintain a detailed inventory of all our SQL Servers and configurations, the list is quite extensive. I often find myself needing to quickly look up the Always On Availability Group (AG) connection string for a specific database, especially in environments with multiple AGs configured.
I’ll skip the background story of why things are set up this way, but here’s a handy SQL script I use to retrieve the connection string for either a specific AG database or all AGs on the current SQL Server instance:
/******************************************************************************************** Script: Get Fully Qualified SQL Connection String for Always On Databases Author: [Your Name or Handle] Reference: https://sqlpal.blogspot.com/2023/07/query-fully-qualified-connection-string.html Purpose: Returns a list of SQL Server connection strings for databases participating in an Always On Availability Group (AG). Parameters: @dbname NVARCHAR(500) - If NULL or empty string, returns connection strings for *all* AG databases. - If a specific database name is provided, validates that it exists and belongs to an AG. Output Columns: ConnString : Fully qualified DNS\Instance connection string. AGName : Availability Group name. DNSName : Listener DNS name. SQLInstance : Current SQL instance name. ClusterNodes : List of participating cluster node names. TCPPort : Listener port number. Notes: - Requires VIEW SERVER STATE permission (to access DMVs). - xp_regread requires sysadmin privileges (reads registry). ********************************************************************************************/ DECLARE @dbname NVARCHAR(500) SET @dbname = N'' -- Set to a specific database name if desired. -- STEP 1: Retrieve current server's domain name suffix from registry. -- This is used to append FQDN to the listener name. DECLARE @DomainName NVARCHAR(100) EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\\CurrentControlSet\\Services\\Tcpip\\Parameters', N'Domain', @DomainName OUTPUT -- STEP 2: Input validation. -- Ensure that the provided database exists locally. IF DB_ID(@dbname) IS NULL AND @dbname <> '' RAISERROR('No database found with name %s.', 16, 1, @dbname) -- If the database exists but is not part of any Availability Group, raise an error. 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) -- STEP 3: Check if Always On is enabled before proceeding. ELSE IF SERVERPROPERTY('IsHadrEnabled') = 1 BEGIN -- Build a comma-separated list of all cluster node names for reference. DECLARE @ClusterNodes VARCHAR(8000) SELECT @ClusterNodes = COALESCE(@ClusterNodes + ', ', '') + node_name FROM (SELECT DISTINCT node_name FROM sys.dm_hadr_availability_replica_cluster_nodes) AS a -- STEP 4: Generate and return connection info for each AG listener. SELECT -- Construct the FQDN + Instance connection string. UPPER( CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN DNS_NAME ELSE CONCAT( DNS_NAME, ISNULL(CONCAT('.', @DomainName), ''), '\', CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(100)) ) END ) AS ConnString, UPPER(ag.name) AS AGName, UPPER(dns_name) AS DNSName, UPPER(CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(100))) AS SQLInstance, UPPER(@ClusterNodes) AS ClusterNodes, port AS TCPPort FROM sys.availability_groups AS ag INNER JOIN sys.availability_group_listeners AS 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 -- STEP 5: Handle case when Always On is not enabled. ELSE IF SERVERPROPERTY('IsHadrEnabled') = 0 RAISERROR('SQL Server is not an AlwaysOn Cluster.', 16, 1) ELSE RAISERROR('Unknown error occurred.', 16, 1)
The earliest SQL Server version I’ve tested this script on is SQL Server 2016 SP2.
I hope you find it useful! If you happen to spot a bug or logical issue, let me know, I’ll do my best to fix it as soon as possible.
And for those who prefer scripting with PowerShell, here’s a version written in that too:
<# .SYNOPSIS Retrieves Always On Availability Group (AG) connection strings for one or all AG databases. .DESCRIPTION This script queries the specified SQL Server instance using SMO to return the fully qualified connection string for each AG listener. If a database name is provided, it validates that the database exists and belongs to an AG, then returns AG details for that specific database. .PARAMETER sql_server The target SQL Server instance in the form 'ServerName\InstanceName'. .PARAMETER db_name (Optional) Name of the database to check. If omitted or blank, the script returns information for all AGs on the instance. .EXAMPLE $sql_server = 'SQLServer\SQLInstance' # Returns FQDN connection strings for all AGs. .EXAMPLE $sql_server = 'SQLServer\SQLInstance' $db_name = "AppDB" # Returns the connection string for the AG hosting AppDB. .NOTES Tested on SQL Server 2016 SP2 and up. Requires SqlServer PowerShell module (Install-Module SqlServer). #> # ===================================================== # CONFIGURATION - Modify these variables as needed # ===================================================== $sql_server = 'SQLServer\SQLInstance' # Target SQL Server instance #$db_name = "AppDB" # Uncomment and set for specific DB # ===================================================== # MAIN SCRIPT LOGIC # ===================================================== # STEP 1: Connect to the SQL instance using SMO $sql_conn = Get-SqlInstance -ServerInstance $sql_server $sql_instance_name = $sql_conn.InstanceName Write-Host "Connected to: $sql_server" -ForegroundColor Green Write-Host "Instance Name: $sql_instance_name" -ForegroundColor Cyan # STEP 2: Verify that Always On is enabled on this instance if (-not $sql_conn.IsHadrEnabled) { Throw "Always On Availability Groups are not enabled on instance '$sql_server'." } else { $ag = $sql_conn.AvailabilityGroups Write-Host "Always On is enabled. Found $($ag.Count) Availability Group(s)." -ForegroundColor Green } # STEP 3: If a database name was provided, validate it and gather AG info if ($db_name -and $db_name -ne '') { Write-Host "Validating database: $db_name" -ForegroundColor Yellow $sql_db = Get-SqlDatabase -Name $db_name -InputObject $sql_conn $db_ag_name = $sql_db.AvailabilityGroupName if (-not $db_ag_name) { Throw "Database '$db_name' is NOT part of an Availability Group." } else { Write-Host "Database '$db_name' is part of AG: $db_ag_name" -ForegroundColor Green # Filter the AG list to the specific one containing the database $ag = $sql_conn.AvailabilityGroups | Where-Object { $_.Name -eq $db_ag_name } # Check and display if the local server is currently the primary replica $is_primary_replica = $sql_db.IsLocalPrimaryReplica() Write-Host "Is local server the primary replica? $is_primary_replica" -ForegroundColor Cyan # Display the name of the AG's primary replica $primary_replica = $ag.PrimaryReplicaServerName Write-Host "Primary replica for AG '$db_ag_name' is: $primary_replica" -ForegroundColor Cyan } } # STEP 4: Retrieve all AG listener information $ag_listeners = $ag.AvailabilityGroupListeners Write-Host "Found $($ag_listeners.Count) listener(s)" -ForegroundColor Yellow # STEP 5: FIXED - Create new calculated properties for convenience $ag_listeners | Add-Member -MemberType ScriptProperty -Name fqdn_ag_conn_string -Value { # Get FQDN of listener, trim any trailing backslashes, add single instance separator $listenerFQDN = [System.Net.Dns]::GetHostEntry($this.Name).HostName.TrimEnd('\') $listenerFQDN + '\' + $sql_instance_name } $ag_listeners | Add-Member -MemberType AliasProperty -Name ag_name -Value Parent # STEP 6: Output final results in a clean table format Write-Host "`n=== AVAILABILITY GROUP CONNECTION STRINGS ===" -ForegroundColor Magenta $ag_listeners | Select-Object ag_name, fqdn_ag_conn_string, Port | Format-Table -AutoSize -Wrap Write-Host "`nScript completed successfully!" -ForegroundColor Green