I have a nice little SQL Server Agent Job that gets some statistical data and stores it in a database table for me.
Here’s the delimma: I currently have this JOB on each server where I want to pull these statistics. I don’t want to have to maintain code on every server that I am managing. Therefore, I want to run this job against other servers remotely.
Here’s the general idea…
[ol]
[li]Query local sql server table for list of servers[/li][li]for each item in list (cursor?) I want to execute certain commands (i.e.various dbcc commands)[/li][li]store results locally into a table.[/li][/ol]
Is there anyway to run DBCC remotely and pull back data? It sounds like I’m going to have to either write a C# app, or jump into DTS a little more. Currently most of what I want done is through OSQL.
You might be able to accommplish this using linked servers.
If you link each of the servers to a single “hub” one you will be able to access items on the other servers using a four-part name in the form of “server.database.user.item”. e.g. “server1.master.dbo.sysdatabases”
I haven’t tried issuing DBCC commands over a server link, but it should work. You can open a cursor over a table specifying which databases to run it in and then use the EXECUTE command to fire of the DBCCs.
The only other way I can think of would be to use an external app, or to keep the jobs local to the server.
That’s what I thought too, but I must not have the syntax right. For example, I can easily enough access data on another server via the method you describe…
i.e. select * from [server1].master.dbo.sysdatabases, but that’s actually running the select command locally.
How would I do the above command with, for example: ‘dbcc sqlperf (logspace)’?
I tried using OPENQUERY to trick SQL Server into running a DBCC command on the remote linked server but I couldn’t fool it, even by constructing a single stored proc that returned a recordset output from the DBCC command.
I think your best bet would be to have a hub server that defines all the other ones that need to be connected to and then use an external app to run the relevant SQL on all of them.
If you want to avoid having out of date code you could get the source of stored procs on the hub server and replicate them to the other servers as you process them.
I can’t rule out the possibility that a linked server solution is possible but, for my money, I passed the point at which it becomes an egregious hack some while ago while looking for an answer.