Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Job to query multiple instances at once Expand / Collapse
Author
Message
Posted Friday, December 7, 2012 8:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 29, 2014 12:09 PM
Points: 65, Visits: 401
Like most environments, we have a bunch of SQL servers. I would like to set up some jobs to query all of the instances for certain things and then email me the results. I have a Central Management Server setup and it works great and my queries get me what I need, but I can't schedule a job through the CMS, so it's still a manual process. Does anyone have something set up to do this? I thought about one instance with a ton of linked servers but I'm not sure if that's the best direction to go. Just wondering if anyone has a better solution.

Post #1394075
Posted Friday, December 7, 2012 8:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 226, Visits: 2,149
You could use an SSIS package, as described here:

http://www.sqlservercentral.com/blogs/sqlstudies/2012/10/31/looping-through-multiple-servers-in-ssis/
Post #1394100
Posted Friday, December 7, 2012 1:13 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:38 AM
Points: 1,898, Visits: 2,048
scogeb (12/7/2012)
...I would like to set up some jobs to query all of the instances for certain things and then email me the results...

I don't know how many instances you have, but I've setup something for myself that collects some daily information about each instance and stores the data in one central database that only I use. The SQL Agent job I created reaches the other servers using SELECT ... FROM OPENROWSET () methodology so I don't need linked servers. You can create the commands from a table with a list of instances, and execute it as dynamic SQL with sp_executesql.
Post #1394215
Posted Monday, December 10, 2012 5:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:05 AM
Points: 47, Visits: 236
hi,

you can use sql server administration wizard this is new feature

right click on sql server agent you will get multi server administration wizard,

select master server (where you can create job)

select target server (where job automatically replicate)

please find below link

http://msdn.microsoft.com/en-us/library/ms180992(v=sql.100).aspx

http://msdn.microsoft.com/en-us/library/ms191305.aspx
Post #1394564
Posted Tuesday, December 18, 2012 8:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 29, 2014 12:09 PM
Points: 65, Visits: 401
Thanks guys! I've done some more research and Redgate's SQL Monitor might be what I'm looking for. I'm surprised no one recommended it though, so I'm a bit leary. I'll start a new thread on it.
Post #1397855
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse