Registered Servers

  • From the server which is registered as a Central management server(CMS)  can I create a SQL job(automate it) in that server(CMS) which then will extract data from all the  registered servers under the CMS and dump that data into the CMS server/database.? Is this possible ?

    When I run a select query in the CMS ... DatabaseEngineServerGroup.master is what under the query is running against. Attached screenshot.

    • This topic was modified 9 months, 1 week ago by  mtz676.
    Attachments:
    You must be logged in to view attached files.
  • You can set up connections to create objects in msdb, or run sp_addjob, et al, from your CMS. This just runs scripts across all your connections.

  • Can you please elaborate on this or provide any links for the same.Thanks

  • Sorry, I misread your post thinking you were sending jobs through the CMS. You want to run stuff on your CMS to all servers automatically from a job. AFAIK, this isn't possible.

  • Could do it in PowerShell and DBATools, call Get-DbaRegServer, loop round the result set from that and call Invoke-DbaQuery, write the result set to the CMS server.

    But directly in SSMS no, unless you write a SSIS package or something to again loop round the servers and ETL the data you need into the CMS

     

  • SSIS - Yes. I already do that.

    But wanted to check if this can be done with Registered servers.

  • No cannot be done in SSMS with registered servers

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply