SQL Server with Mr. Denny

Jul 29 2010   11:00AM GMT

Running a query against multiple servers at one

Denny Cherry Denny Cherry Profile: Denny Cherry

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  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Retracement
    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
    0 pointsBadges:
    report
  • Stejones13
    I can't believe I overlooked this for so long! Thank you for posting it.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: