schedule a job in CMS

  • Is it possible to schedule a sql job in CMS ? As of now I have a query which captures data from all servers listed in CMS, but its is a manual process. Can I schedule it against multiple servers and capture that data and dump that data into  a single server/db/table.(Without using powershell or  multi server administration as it creates jobs on all servers)


    • This topic was modified 4 weeks, 1 day ago by  mtz676.
  • I imagine this depends on your CMS.

    But it sounds like a use case for SSIS to me.  With SSIS you can have it fire queries against multiple databases simultaneously or sequentially.

    Alternately, if you have a single server to run the job, you could have linked servers set up so you can pull the data you need.

    Those are the 2 approaches I can think of that are independent of any CMS.  Personally, I would use either SSIS or linked servers to get this information rather than an external tool.  Less overhead and in the event you change CMS's, you may lose the ability to do this data capture.

    The above assumes that the query you have is a TSQL query and not a query specific to the CMS.

  • Should have mentioned no SSIS in the initial post.

    Is CMS not capable of scheduling jobs ?

  • You may want to look at this thread.


    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:

  • CMS is just a registry of instances...and is hosted on a specific SQL Server instance.  You can schedule agent jobs to run on that specific instance - but you still need some way to 'connect' to each instance registered.

    For that, you can use linked servers, powershell, SSIS, sqlcmd or any other utility to connect to a registered instance.

    On the instance hosting the CMS registry, you can query the system tables to get the registered servers.  But you still have to connect to each instance.

    One note: you can open a mult-instance query by right-clicking on the CMS host name and selecting new query.  Run your code in that window and it will be executed across all registered instances.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 5 posts - 1 through 5 (of 5 total)

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