October 27, 2020 at 9:49 am
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)
Thanks
October 27, 2020 at 4:23 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 27, 2020 at 5:22 pm
Should have mentioned no SSIS in the initial post.
Is CMS not capable of scheduling jobs ?
October 27, 2020 at 6:35 pm
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:
http://www.sqlservercentral.com/articles/61537/
October 27, 2020 at 7:40 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply