1

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.

0

2 Answers 2

4

Try wrapping your query inside some conditional logic which checks for the @@version. It will still access all servers in your Central Managed Server list, but it won't throw the error. Here is an example you could try.

IF (SELECT @@VERSION) LIKE '%2008%'
BEGIN
    SELECT 'not avail on 2008' AS role_desc
        ,'not avail on 2008' AS NAME
        ,'not avail on 2008' AS replica_server_name
        ,GETDATE() AS checkTime
END
ELSE
BEGIN
    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
END

Regarding your question about saving the results of the CMS query - I'm not aware of a built-in method for doing that. However, one option (that requires a little extra work) is to:

  1. Run the CMS query, select all of the results grid and choose Copy with Headers.
  2. Head over to DBFiddle and click on the Text To DDL button at the bottom of the page.
  3. Paste the contents of the CMS grid results in the Formatted Text window and enter a table name in the Table Name box.
  4. Click the Preview DDL button which will show you a CREATE TABLE command along with INSERT statements.

You'll have to do some minor modifications to the generated DDL such as removing tick-mark quotes around column names and using brackets around column names that have spaces like [Server Name] or putting brackets around reserved identifiers like [Name].

This will allow you to save the results to a table of your choice for further analysis.

4
  • 1
    Thanks. Any way we can store the result set to a local or temp table for further manipulation? Say, for example, if there is any of the servers having a mix of primary and secondary HAGs, it triggers an alert to the DBAs.
    – Ricky Hope
    Mar 26, 2019 at 22:30
  • Thanks but I am looking to automate this entire process so that it can send report in the midnight everyday. I have done quite a lot of research and it seems there is no way doing this with CMS. I may have to create linked servers and let the code loop through them to get status but this way looks awkward and hard to maintain in the future. Any better idea is greatly appreciated.
    – Ricky Hope
    Mar 27, 2019 at 16:04
  • @user1238837 - use Powershell to submit the query to each server and output the results to a txt file - the Powershell script could read the list of servers from a txt file and loop through each server to send the query, then output each result to the same txt file using append Mar 27, 2019 at 16:05
  • @user1238837 - you might be able to use the technique in this link where I answered a similar question Putting together Powershell script to document SQL Server with Instances Mar 27, 2019 at 16:23
0

I like Scott's answer. But what if the criteria for whether you want to run your query against a particular server isn't something that can easily be handled with condition logic from inside the query? Here's an alternate approach:

You can create a Server Group within your Central Managed Server list.

Right-click on the name of your CMS server in the Central Management Servers portion of the Registered Servers window, and select New Server Group.... Give it a name, like "Not2008", and an optional description. Then either move existing registered servers to that group, or add one or more new registered server entries to the group. Now you can right-click on the group and use New Query to open a query window that you can use to run your query against only the members of that group.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.