This is a SQL script used to select all Availability Groups Ports within a SQL instance or cluster.
USE master
GO
CREATE TABLE [dbo].[#TmpErrorLog] ([LogDate] DATETIME NULL, [ProcessInfo] VARCHAR(20) NULL, [Text] VARCHAR(MAX) NULL )
INSERT INTO #TmpErrorLog ([LogDate], [ProcessInfo], [Text])
Exec xp_readerrorlog 0, 1, N'Server is listening on'
SELECT @@SERVERNAME as 'Source SQL Instance',* FROM #TmpErrorLog
GO
USE master
GO
SELECT
@@SERVERNAME as 'Source SQL Instance',
e.Endpoint_ID,
e.name as EndpointName,
sp.name AS EndpointOwner,
et.PayloadType,
e.State_Desc
FROM sys.endpoints e
INNER JOIN sys.server_principals sp
ON e.principal_id = sp.principal_id
RIGHT OUTER JOIN ( VALUES ( 2, 'TSQL'),( 3, 'SERVICE_BROKER'), ( 4, 'DATABASE_MIRRORING') ) AS et ( typeid, PayloadType )
ON et.typeid = e.type
ORDER BY e.name DESC
SELECT
DISTINCT
@@SERVERNAME as 'Source SQL Instance',
Endpoint_ID, Protocol_Type, Local_TCP_Port--, count(*) as total
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL
--GROUP BY endpoint_id, protocol_type, local_tcp_port
ORDER BY protocol_type desc
SELECT
@@SERVERNAME as 'Source SQL Instance',
ag.name AS [AG Name], AGDatabases.database_name AS DatabaseName, ar.Replica_Server_Name, [Endpoint_URL]--,
/*ar.availability_mode_desc, adc.[database_name],
drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant,
drs.synchronization_health_desc, drs.recovery_lsn, drs.truncation_lsn, drs.last_sent_lsn,
drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn,
drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size,
drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate,
drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.database_state_desc*/
FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
INNER JOIN sys.availability_databases_cluster AS adc WITH (NOLOCK) ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag WITH (NOLOCK) ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK) ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id
INNER JOIN sys.availability_databases_cluster AGDatabases ON ag.group_id = AGDatabases.group_id
ORDER BY ag.name, ar.replica_server_name, adc.[database_name] OPTION (RECOMPILE);
SELECT
@@SERVERNAME as 'Source SQL Instance', dns_name as 'Listener Name', ip_configuration_string_from_cluster as 'Listener IP', port as 'Listener Port' from sys.availability_group_listeners
SELECT
@@SERVERNAME as 'Source SQL Instance',
ag.name as [Availability Group], ar.replica_server_name as [When Primary Replica Is],
rl.routing_priority as [Routing Priority], ar2.replica_server_name as [RO Routed To],
ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
INNER JOIN sys.availability_replicas ar on rl.replica_id = ar.replica_id
INNER JOIN sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
INNER JOIN sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority
DROP TABLE [dbo].[#TmpErrorLog]If you need assistance implementing custom SQL code, we can help!
