MSSQL de Availibility Group VeritabanıAdı,ip,port,agname,dnsname Listelemek

tarihinde gönderilmiş tarafından

MSSQL Serverda Availability Group kullanıyorsanız ve birden fazla ag ve instancenız varsa tek tek gidip ssms(SQL Server Management Studio) üzerinden bakmaktansa sistem dmvlerinden yaralanarak sorgulama yapabilirsiniz.Bazen hangi sunucuda hangi Ag( Availability Group ) aktif , aktif olan sunucular içindeki veritabanlarının ismi hangi ip\port’u kullandığı ve dns name gibi bilgilere gerek duyabiliriz.

Aşağıdaki T-SQL de hangi ag de hangi veritabanı var ve aglerin ip,port,dnsname gibi bilgilerini sorgulayacağız.

SELECT 
AG.name AS [AvailabilityGroupName], 
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole],
--arstates.role,
dbcs.database_name AS [DatabaseName],
LEFT(agli.ip_configuration_string_from_cluster,29) as ip,
gli.port,
gli.dns_name
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id 
   --AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
INNER JOIN sys.availability_group_listeners as gli on dbrs.group_id=gli.group_id
INNER JOIN sys.availability_group_listeners as agli on agli.listener_id=gli.listener_id

Loading