Monday 29 July 2019

SQL Server : Check Linked servers pointing to Primary or Secondary


-- Script to check Linked servers pointing to Primary or Secondary




Declare @lsrv_name sysname
declare @exctbl varchar(500);
Select @lsrv_name = 'AGN6_SQ1_PL_Test_RO'  -- change linked server name here

If exists (Select 1 from tempdb..sysobjects where xtype = 'u' and name like '#data%')
Begin
Drop table #data 
End 

Create table #data(Replica_type varchar(100), Linked_Srvr Sysname)

If exists (select * from sys.sysservers where srvname like @lsrv_name)
Begin
set @exctbl = 'SELECT ars.role_desc, '''+ @lsrv_name +''' as Linked_srv_name
FROM ' + @lsrv_name +'.master.sys.dm_hadr_availability_replica_states ars 
INNER JOIN ' + @lsrv_name +'.master.sys.availability_groups ag 
ON ars.group_id = ag.group_id
AND ars.is_local = 1'
print (@exctbl);
insert into  #data  exec(@exctbl)
END

select * from #data
Drop table #data

No comments:

Post a Comment