Group Server Queries: ad hoc management

Recently, I worked on a project which involved auditing a dozen of SQL server instances. A process I usually script upfront however, due to constraints on my time and some local challenges (a very “opinionated” customer) wasn’t able to do so. Fortunately I discovered this somewhat unknown “Group Server Queries” option a while ago, so gave it a try and was pleasantly surprised.

Group Server Queries in a nutshell

For those who never heard of Group Server Queries before, Group Server Queries basically allow you to execute a single statement over multiple SQL server instances. Every record will be prefixed with the server name\instance name which makes it easy to identify where the data comes from.

The only requirements are that; The SQL server target instances need to be grouped within Management Studio (Server Group) first and every target will need to have the same database objects available.

Creating a Server Group

A group of instances can be created by opening the Registered Servers pane in SQL Server Management Studio. Local Server Group –> New Server Group and add the desired registrations.

Executing the query

To execute a new query/open a new query window, simply select the “new query” option.

To keep thing simple, I’m just listing SQL server’s version information in the sample below however, I don’t think it’s hard to imagine how useful this could be when performing some ad hoc dynamic management views querying.

SELECT @@Version

Results: