Instance, listener and availability group information queries
Collection of T-SQL queries to check AG listeners, the listening port, service accounts, server properties and availability group replicas.
Diagnostic queries for a SQL Server instance: availability group listener addresses, listening port in the error log, service accounts, server properties, availability group replicas and the number of logical processors.
Availability group listener
select * from sys.availability_group_listener_ip_addresses
SELECT * FROM sys.availability_group_listener_ip_addresses;
SELECT *
FROM sys.availability_group_listeners agl
inner join sys.availability_group_listener_ip_addresses aglia on agl.listener_id = aglia.listener_id
Listening port in the error log
xp_readerrorlog 0, 1, N'Server is listening on'
Service accounts
The SQL Server Agent service account is read from the registry, then the service accounts are listed with sys.dm_server_services:
DECLARE @sn NVARCHAR(128);
EXEC master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
'ObjectName',
@sn OUTPUT;
-- get user Find Service Account for SQL Server and SQL Server Agent?
SELECT servicename, service_account
FROM sys.dm_server_services
GO
Method
SELECT @sn;
SELECT DSS.servicename,
DSS.startup_type_desc,
DSS.status_desc,
DSS.last_startup_time,
DSS.service_account,
DSS.is_clustered,
DSS.cluster_nodename,
DSS.filename,
DSS.startup_type,
DSS.status,
DSS.process_id
FROM sys.dm_server_services AS DSS;
Server properties and connection
SELECT
SERVERPROPERTY('ServerName'), SERVERPROPERTY('InstanceName'),SERVERPROPERTY('ComputerNamePhysicalNetBIOS') ,SERVERPROPERTY('MachineName'),
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address
sp_who2
xp_logininfo 'DOMAIN\user'
Databases of availability groups
SELECT Groups.[name] AS AGName ,
Databaselist.[database_name] AS DatabaseName
FROM sys.availability_databases_cluster Databaselist
INNER JOIN sys.availability_groups_cluster Groups ON Databaselist.group_id = Groups.group_id
ORDER BY
AGName ,
DatabaseName;
Availability group replicas
This query shows the primary replica, the synchronous or asynchronous mode, readable secondaries and the failover mode. Without a WHERE clause it lists every group; the filters are commented out.
--Show Availability groups visible to the Server and Replica information such as Which server is the Primary
--Sync and Async modes , Readable Secondary and Failover Mode, these can all be filtered using a Where clause
--if you are running some checks, no Where clause will show you all of the information.
WITH AGStatus
AS (
SELECT name AS AGname,
replica_server_name,
CASE
WHEN (primary_replica = replica_server_name)
THEN 1
ELSE ''
END AS IsPrimaryServer,
secondary_role_allow_connections_desc AS ReadableSecondary,
[availability_mode] AS [Synchronous],
failover_mode_desc
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
)
SELECT [AGname],
[Replica_server_name],
[IsPrimaryServer],
[Synchronous],
[ReadableSecondary],
[Failover_mode_desc]
FROM AGStatus
--WHERE
--IsPrimaryServer = 1
--AND Synchronous = 1
ORDER BY AGname ASC,
IsPrimaryServer DESC;
Number of logical processors
SELECT cpu_count
FROM [sys].[dm_os_sys_info]
Notes
- Not every section is a standalone statement: the isolated
Methodline, thesp_who2andxp_logininfocalls without a separator, and the missing comma after the lastAS client_net_addressprevent running the block in one go. Run the queries one by one. - The
DOMAIN\useraccount in thexp_logininfocall is an example to replace.