Running a query against multiple servers at one

Published On: 2010-07-29By:

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


Contact the Author | Contact DCAC

2 responses to “Running a query against multiple servers at one”

  1. Retracement says:

    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

  2. Stejones13 says:

    I can’t believe I overlooked this for so long! Thank you for posting it.

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via