Being a database administrator I am constantly connecting to multiple databases in order to get things done. For instance say I wanted to turn on compression in each of the backup jobs across my entire SQL infrastructure, I would need to manually connect and execute a block of SQL code for each instance on each server. If you only have couple of servers doing it this way might be ok but any more then say 4 and it begins to get old very quickly. Lucky for us the folks at Microsoft felt our pain and answered it with what is known as Registered Servers starting back with the release of Microsoft SQL 2005.
The first thing you need to do is open up SSMS.
Then click on View and select Registered Servers.
Expand out Database Engine.
The first order of business is to create a server group. Think of these as the logical container that all of your soon to be registered servers will be in. You may have multiple groups and each server has the potential to belong to more than a single group.
To create the group right click on Local Server Groups and choose New Server Group
You will give your group a name and a brief description.
The next step is where we will begin adding servers to our group. Right click on your newly created server group and select New Server Registration.
You should now be looking at the New Server Registration dialog box.
You will populate the information about your server. Since this is a demo I set the authentication method to SQL Server and chose to have it to remember my password. You can click on TEST to validate your connection information. Then click save.
You would repeat this process until you have all the servers you need registered. Then once complete it should look something like this.
Once you have all your servers registered you going to want to test them out. In my example I’m going to run a query that will tell me the current version of SQL on each of the servers in my group.
The code I’m going to use is very simple.
By most accounts this looks just like a normal query window inside of SSMS but look very closely at the bottom. This shows you the number of servers you are currently connected to and how many servers will execute this code. If you had a server offline in your group then it would read (5/6).
Once you have executed your SQL you will see the results as you normally would. The thing to point out here is notice each row represents one server in our group. If you had 20 servers you would have 20 rows.
In this post I walked you through on how to setup Registered Severs in SSMS. If you would like a much more detailed outline of what you can and can’t do with these please take a look at this information from Microsoft by click here.