Run a query against multiple instances using SSMS

Yesterday I was part of a discussion related to SQL Server Management Studio. Since I had not posted about one of the good features in SSMS (2008), here it is.

How many times as a DBA we had to prepare an inventory of all the databases in the enterprise, check if a particular setting is enabled in all the instances. We had to resort to osql, sqlcmd or some VB script to get this done. Not any more! SQL Server 2008 had introduced a new feature to execute any query against multiple instances. No need to install the database engine for SQL Server 2008, only the SSMS would do this for us. Moreover it is so effortless.

Here is how to do it. Open SSMS and navigate to View –> Registered Servers.

Enter the server details in the next screen and click Save.

Once all the servers are registered, Right Click on the Local Server Groups and click New Query.

In the new query window type any query that needs to be executed against the instances and hit execute. Thats all!

Note that the ServerName column automatically gets populated in output even though I did not specify that in my query. If one of the instances is down, even then the query is executed against the remaining online instances. Along with the output one error message stating that the query could not be executed against the offline instance is displayed.

One more good feature is that this is really portable. Suppose you had registered all the instances on one server. For some reason you want to move those registered servers to some other server. Even this feature is also incorporated. This option is under Tasks when you right click on the Local Servers Group.

Select what to export and give the output file name. All the registered instances will be exported to a .regsrvr file.

Use the Import option on the server where you want this to be imported and all the registered servers get saved.

If you are not using this feature yet, please go ahead and help yourself.

2 thoughts on “Run a query against multiple instances using SSMS

    1. PradeepAdiga Post author

      Mark,

      As far as I know this feature of SSMS cannot be scheduled in a job. We may need to do it through a custom package running through a job.

Comments are closed.