Is it possible to run query against only part of the Central Managed Servers?

1 vote

2 answers

138 views

sql-server sql-server-2008 central-management-server

                          For example, I want to run this query to get the status of some servers having high availability group set up:

    SELECT  s.role_desc,  g.name, r.replica_server_name , GETDATE() as checkTime
        FROM sys.dm_hadr_availability_replica_states AS s
        INNER JOIN sys.availability_replicas AS r
            ON s.replica_id = r.replica_id
    	INNER JOIN  sys.availability_groups g
    		ON r.group_id = g.group_id

...but some of the managed servers are SQL Server 2008, which don't have those views in the query, and hence the query returns error.  

I have tried something like:

    SELECT  s.role_desc,  g.name, r.replica_server_name , GETDATE() as checkTime
        FROM sys.dm_hadr_availability_replica_states AS s
        INNER JOIN sys.availability_replicas AS r
            ON s.replica_id = r.replica_id
    	INNER JOIN  sys.availability_groups g
    		ON r.group_id = g.group_id
    where  r.replica_server_name in
    (select server_name  FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
    where server_name like 'theserversIwant%a'
    )


...but it doesn't work. It still queries the entire list of all the managed servers. 
                        

Asked by Ricky Hope (31 rep)

Mar 26, 2019, 05:49 PM
Last activity: Mar 27, 2019, 09:14 AM

Is it possible to run query against only part of the Central Managed Servers?

Related Questions