How many times you have been into a situation where you need to run one query across many servers? I have asked his question from one of the DBA and his answer was I will create a linked server and then modify the query to refer linked server. Well this needs modification to server configuration and most of the production server don’t allow changed without change request.
The better approach would be to use an under-estimated feature of SQL Server Management Studio called as Multiserver query. This can be done via by creating a group and registering one or more registered servers within the groups. Once group is created then we can query the complete group and the result format can be customized. Pinal (b|t) has written a blog here. This is a extension of that blog in little more details.
To get to below option we need to open “Registered Servers” Window. If you don’t see it hit Ctrl+Alt+G or go to View > Registered Servers. I have registered two server under a group called “Fake Production”. We can created your own hierarchy to logically group the servers. Display Name can also be changed in this windows. I have given a name as “HiddenName” to one of my instance for display purpose. Effectively I have 4 Servers under “Local Server Groups” and 2 Servers under “Fake Production Group”
We can right click on any of the node and choose “New Query” as shown below.
First thing you would notice is that the color of status bar is changed. Also notice that we can see how many servers are connected out of total servers. For demo purpose I have used different account to connect each server that’s why we are seeing “<various logins>”
Color Customization can be done by going to Tools > Option > Text Editor > Editor Tab and Status Bar as shown below.
From above, we can also change other setting for status bar but they are not specified to multi-server.
If we ran a query in this query window, it would run on all the servers and give results.
We are seeing same number of databases on 1st and 4th row in my lab because they are same servers. dot (.) and (local) both means local server.
This result can be customized using “Tools > Option > Query Results > SQL Server > Multiserver Results” as below
Note: We can’t join two tables across the servers using multiserver query feature.
Hope this would help in getting some quick reports from various servers.
Help: SQL Server 