Never Say Never

Here are a couple more SSMS tricks I’ve stumbled upon recently.  Both involve the functionality of registered servers.  If you really only deal with 1 or 2 servers in your environment, these probably won’t help you out much.  But for those of us who have to deal with a large number of servers, this can be a real time saver. First, for anyone unfamiliar with Registered servers, you get to the option by going to the view menu and can then add individual servers as ‘registered’ servers which you can easily get back to, complete with login information.  You can then add these individual server registrations to ‘groups’.  (The move options are under tasks when you right click on a server.)

    

The first trick is using these registered server groups to open up connections to all the servers you have to deal with constantly.  I use this to keep them ordered in my SSMS Object Explorer so that I know right where to go to get to each one and I don’t have to waste time looking through a long list of servers each time to find a server or see if I already have a connection to it via OE.  If you right click on the Object Explorer Group, one of the options is Object Explorer.  This will open up a connection to each server in the group (in order).

         

The second is using these registered server groups to execute a piece of code on all servers simultaneously.  When you right click on a group and choose New Query, a new window will open up and you’ll notice the status bar is now pink and <multiple> is used for the Server Name.  This is limited in many instances because the databases need to exist on all servers with the same names or it won’t work properly, but it is still quite useful on occasion.  I use it most often for creating linked servers, creating/altering jobs or collecting general system information.  It could also be great if you had a ‘Utility’ database on every server containing things like a Tally table or utility functions.