Running Administrative Queries Across Multiple Servers

As a DBA, sometimes we are asked questions, such as "What is total size of databases used by Application A?" or "Does Application A have any databases with a mismatched collation?"

If Application A has a single database server, then these questions are straight forward to answer. If Application A has databases dispersed across multiple instances, however, then the question becomes more time consuming to answer. The temptation is to connect to each instance individually, and copy/pasting the query.

It is possible to make use of Central Management Servers, to simplify answering this kind of question, however. For example, if you are supporting a large, dispersed data-tier application, you can create a server group, containing each of the instances that support the application.

For example, in the image below, you can see that I have created two folders; one for the EMEA region and another for the US.

We can now register our servers\instances under the relevant group. Below, you can see that I use the New Server Registration dialog box, to add servers. In this example, I will add two servers, nammed ESPROD1 and ESPROD2, to the Production | NTAM server group.

Imagine a scenario where you have a number of SQL Server instances, where the database files are hosted on a SAN (Storage Area Network). You come into work one morning, to find that there has been an issue with the production SAN and you need to check all databases, on all instances, for corruption. This can be easily achieved, using central management servers.

Because you have a separate SAN for each environment, in each data center, you will only want to check the servers in the NTAM server group, as this is where the SAN issue occurred. Therefore, select New Query, from the context menu of the NTAM server group. This will cause a new query window to be displayed and we will use DBCC CHECKDB to check the consistency of all databases on both instances. This is demonstrated below:

EXEC sp_MSforeachDB 'DBCC CHECKDB (?)' ;

This command executes the undocumented sp_MSForeachDB system stored procedure. We pass the command that we want to run against each database, into this procedure and use ? as a placeholder for the database name. This is automatically populated by sp_MSForeachDB, for each database in turn.

Below is a partial output. You can easily see which results relate to each server, along with the execution context. You may also notice the query status bar is pink instead of yellow and displays the name of the server group that we can the command against.

To learn much more about Central Management Servers, including further examples of useful queries to run across instances, full details of how to configure them and a demonstration of how to evaluate Policies against Central Management Servers or Server Groups, please refer to my new book, "Expert Scripting and Automation for SQL Server DBAs"