Multi-Server Query Execution in SQL 2008

As a DBA, have you ever needed to run a query against one of the system databases in all of your servers? The tedious process of opening a Query in SSMS and then running it, changing the connection and running it again are over. SQL 2008 now has the ability to run multi-server queries. To utilize this new feature, requires at least one registered server group to run a script against multiple server. To setup a registered server group and servers, refer to

http://msdn2.microsoft.com/en-us/library/ms181228(SQL.100).aspx
http://msdn2.microsoft.com/en-us/library/ms183353(SQL.100).aspx

Once you have your group created, you can right click on it and select the New Query Option:

When you do this, some specific environmental changes will occur. First, the Database Dropdown box will only have the common databases to all servers listed in it.

Second, the the status bar will turn from beige to Pink to show that this window is in multi-server mode.

Now we can run a simple database script. For the purposes of demonstration I am going to use the script from the following SQL Examples article:

Find Last BackUp Date Of All Databases On Your Server

What you will notice when your run this command is that you now have an added column in the output, ServerName:

You can configure the output of this columns in the SSMS Options under Query Results -> SQL Server -> Multiserver Results.

What is really nice is that as long as your query can execute on all of the servers, this works with SQL 2000 and SQL 2005 Registered Servers as well. Yet another tool available to ease the tasks as a DBA.