So you’ve got a bunch of machines that you want to run a quick query against. SQL Server 2008’s Management Studio gives you a quick and easy. Open up the Registered Servers in Management Studio and select a group of servers. Then right click on the group and click on the “New Query” option.
This will open a new query window where you can run a query against all the servers that are online in that group. In my sample query shown below you’ll see that I ran SELECT @@VERSION against all the servers. When I ran this 3 of the 6 servers in the group were online so three servers were able to return data.
Now if you look at the messages tab (look down) you’ll see which servers the query ran against, and which servers it failed against. It also tells you what accounts the query was run using (based on the connection info for each server).
Now you can’t merge data together in a temp table as everything in the query window will be run against each server. It simply displays the information together. You can pull a single value like I showed above, or you can query a table.
Now when querying from a tables on multiple servers you’ll need to make sure that the schema for those tables are identical otherwise it will only return the data for the first table that it queries. An error will be shown on the messages tab telling that you that the schemas don’t match if this is the problem.
Denny
2 Responses
Denny this is awesome, this has (for some reason) passed me by, so much so that I even wrote a .NET GUI to do something similar. The only reason the latter is better is that the server list data source is more flexible, however once I identify the reg server keys I can resolve this and use SSMS.
Thanks.
Regards,
Mark Broadbent @retracement
I can’t believe I overlooked this for so long! Thank you for posting it.