Run Query on Multiple Servers

The ability to query multiple servers instantaneously is a welcome addition to SQL 2008. You achieve this by adding the target servers as a group of Registered Servers. Once this has been done, you right click on the group and select ‘New Query’. This action will open a new query window which is ready to execute across all the servers in your registered servers group.

I find this particularly useful after I arrive in the office as the execution of a single script can interrogate the environments contained within your group and give you immediate results.

For example running the following script on a single server returns jobs that failed on the last occasion they were run.

select h.server as [Server],

j.[name] as [Name],

h.message as [Message],

h.run_date as LastRunDate,

h.run_time as LastRunTime

from msdb.dbo.sysjobhistory h

inner join msdb.dbo.sysjobs j on h.job_id = j.job_id

where --j.enabled = 1 and

h.instance_id in

(select max(h.instance_id)

from msdb.dbo.sysjobhistory h group by (h.job_id))

and h.run_status = 0

When executing using this method of querying multiple servers, the results are returned from each server union-ed into one result set. The result set begins with an additional column which lists the ‘Server Name’, before returning the other columns as normal.

Another example is to immediately identify the versions of multiple servers by using the following server properties:

SELECT

SERVERPROPERTY('productversion'),

SERVERPROPERTY ('productlevel'),

SERVERPROPERTY ('edition')

Run this over multiple servers using query window and the results are returned as follows (note the additional Server Name column and how the results appear as subject of a UNION).

Of course the query window which is spread across multiple servers can also be used for any other ad-hoc action you would perform on a single. A very useful feature indeed!