Running a script on multiple servers

, 2012-03-02

 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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads