Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

I want to use a Central Management Server, with a server group underneith, to run management queries against multiple server.

I know how to run a query against the whole group through SSMS, but I want to schedule queries to run through server agent.

How do I do this?

share|improve this question

2 Answers

up vote 3 down vote

What you're looking for it sounds like is the Target Servers of a job. You can add your servers to this portion so that the job is run against your desired servers.

BOL documentation on Target Servers: http://msdn.microsoft.com/en-us/library/ms189553.aspx

share|improve this answer
Thanks. I though about this, but I want to save the results to a table on the "Master Server". From what I understand, this will not work, baring in mind I cannot create linked servers. Is my assumption correct? – Pete Carter Nov 24 '12 at 14:39
2  
@Pete why can you not create linked servers? – Aaron Bertrand Nov 24 '12 at 14:40
@PeteCarter I'm with Aaron on this one. Why can't you create linked servers?? – Thomas Stringer Nov 24 '12 at 17:57
@AaronBertrand & Thomas - I can't use linked servers because of unfounded security concerns. I may need to resort to SSIS I guess – Pete Carter Nov 24 '12 at 21:11
2  
Another possibility is to write a PowerShell script and using SMO do all of this logic, then just call the script from a SQL Server Agent job. – Thomas Stringer Nov 24 '12 at 21:48
show 3 more comments
up vote 3 down vote

Management Studio is the glue that makes that work. To use SQL Server Agent you'd have to set up Target Servers manually as Thomas suggested, or add Linked Servers and manually loop through each server in your job step(s).

Note that your local CMS may have access to servers that your server might not (e.g. consider the scenario where most of your managed servers are in the data center, but you also have dev/test servers in the local domain - you can manage all of these from the client tools on your desktop but probably not from the data center).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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