One of my favorite features of SQL Server Management Studio (SSMS) is the ability to run a script on multiple servers at once. This feature came in handy recently when I was installing some maintenance scripts and jobs on about a dozen servers. One of the challenges I faced was that my servers were running either SQL Server 2000, 2005 or 2008 R2.

To do this, you will need to find the Registered Servers window in SSMS. If you don’t see it, click View --> Registered Servers. In the old Enterprise Manager days, we used Registered Servers all the time, but we were not able to run scripts on multiple servers at once if my memory serves me. If you are using Policy Based Management, you can also use this feature to import or evaluate policies on groups of servers.

Once you see the Registered Servers, you can create groups to organize your servers. One feature I really like is the ability to add a server to multiple groups. I might have a server in a SQL2008R2 folder and have the same server also found in a QA folder. I can nest folders, too, so I could have a QA folder and under I could have SQL2000, SQL2005 and a SQL2008R2.

To add servers to the groups, just right-click the group and select New Server Registration. Add the connection information. If the server ends up in the wrong group, you can right-click the registered server and choose Tasks and Move To. Of course, you can delete a server or a group as required.

These registered servers are local to the computer you are running. For even more power, you can use the Central Management Server. By designating a server as the Central Management Server, your whole team can see the same set of registered servers.

Once the groups are set up, just right-click on a group and select New Query. Now any statement you run in the window will actually be run on all the servers in the group. You could check for the existence of a particular maintenance job, run an alter table statement, create a stored procedure…well anything you need to do.

In the results window, you’ll see the name of each server added to the left in the grid. If, for some reason, an error results on one of the servers, you’ll see an error message, but the error won’t affect the other servers.

Using the Registered Servers functionality really saved me some time when running the scripts and checking the servers to make sure everything is in place. It’s just one of those little things that can make a big difference if you know about it.