Monday, November 28, 2011

Registered Servers 101

Registered Servers are a great tool for keeping a list of all the servers you work with. This can be useful both for the servers you use every day, and for those servers you only occasionally touch. How often have you had to check a rarely used server only to forget the exact name of the server and instance?

Registered Servers come in two varieties, Local Server Groups and Central Management Servers. For today, I will cover Local Server Groups. In a future post, I will discuss Central Management Servers.

Local Server Groups

Local Server Groups are great for keeping a list of all the servers in your environment. You can group the servers by project, OS version, SQL version, time zone, or any other grouping that makes sense to you. Additionally, a server may be registered in more than one group, and groups can be nested within one another.

Creating Local Server Groups

View - Registered Servers

The first step is to tell SQL Server Management Studio that you would like to work with Registered Servers. Go to the View menu and select Registered Servers. Alternatively, you could use the Keyboard Shortcut Ctrl+Alt+G.

Once you do that, you will see two choices; Local Server Groups and Central Management Servers. As I stated previously, for this post we will focus on Local Server Groups. The good news is, everything we learn about Local Server Groups will be applicable to Central Management Servers, as well.

If you select New Server Group, you will be presented with a simple dialog to enter your group details.

New Server Group - Step 1

New Server Group - Step 2

After you have created you Server Group, you can start registering servers into it.

New Server Registration - Step 1

New Server Registration - Step 2

Here is an example with several groups and servers...

Local Server Groups Example

Exporting and Importing Servers

Now that you've spent all this time building up your server list, wouldn't it be great if you could share it with others? Thankfully, this is very easy. To export your server list, just select on one of the folders or sub-folders, right-click, and follow the prompts.

Export Server List - Step 1

Export Server List - Step 2

To import the server list, follow similar steps. Right-click on Local Server Groups and follow the prompts.

Import Server List - Step 1

Import Server List - Step 2

Multi-Instance Queries

Now that we've gone through the hassle of registering all our servers, it is time to put that to good use. The primary reason to create Registered Server lists is to run multi-instance queries. These are great for auditing systems or performing administrative tasks.

To do so, simple right-click on one of your groups and select New Query. Alternatively, you can select the group, then go double-click on one of your query.sql files.

Multi-Instance Query - Connection

There are a few things to notice that are different from a normal query window.

First, the footer is pink; this alerts you to the fact that you are connected to multiple servers at the same time. SELECTs are generally fine, but double-check before you run any INSERTS, UPDATEs, or DELETEs.

Second, it tells you how many servers were in the group it attempted to connect to, and how many it was able to reach. In this example, I was able to connect to ten out of ten. If you see less than the number you were expected, make sure you find out why those servers were not available.

Third, you will see an indicator telling you which Server Group you have connected to. This is where a good naming convention comes into play. When you are creating your groups make sure the names make sense both to you and to your coworkers.

Multi-Instance Query - Results

Here, you will notice that the results from each server has been stitched together in one results window. From here, you can save the results to Excel for better formatting or reporting.

Pros and Cons

The pros are great. You can keep track of all your servers. You can run queries or updates against multiple servers at the same time. Groups can be nested to mimic the hierarchy of your organization. Servers can even be in multiple groups at the same time.

So what's not to love? Local Server Groups are just that, local. That means, all of your hard work means nothing if you happen to logon to a different server. Or to your coworkers. Or...

Of course, you can export you list and import on other servers, and share it with your coworkers. But just like anything you print these days, it is immediately obsolete. Any time you add or update a new server, you will need to export that change to every other server or coworker that is using your list.

A Better Way

Never fear, there is a better way. Central Management Servers take everything good from Local Server Groups and none of the bad. In a nutshell, these are Server Lists that you can share among your organization and are always kept up to date.

Next time, we will look at Central Management Servers.

No comments:

Post a Comment