Voluntary DBA

Edit 2013-10-14: This post is now available in video form.

Whether you manage one SQL Server instance or hundreds, you need to have a way to keep track of them all.

Just like best-practices when it comes to security, it would be nice to create logical groups of instances and manage them based on their group membership. Perhaps also inspect, or perform actions against groups of instances:

• Run queries, including management-type activities like creating or running jobs
• Check if the settings match a pre-determined configuration, and if not, fix the settings

While there isn’t really an inbuilt group-based way to manage these kinds of things, there are two out-of-the-box solutions to mimic many of the advantages, and which help you accomplish these tasks. These solutions are, of course, Registered Servers, and Central Management Server.

In terms of requirements, Registered Servers was available starting with the Management Studio packaged with SQL Server 2005, and support for registering an instance as a Central Management Server was added in SQL Server 2008. I don’t believe there’s any limitation on version of the instances you can register within either system (and I can’t find it in the documentation); off the top of my head I would say SQL Server 2000 and higher sounds pretty safe. From personal experience, 2005 and higher definitely works.

The UI to access both of these features is through the View | Registered Servers menu item in Management Studio. While it might be a bit strange that both of them are contained in one UI, we’ll see in a second that that’s actually an advantage, because it’s likely you’ll need to use both in your environment.

What is a Registered Server?

While Registered Servers and Central Management Server have different names, both ultimately do the same thing: store a user-defined hierarchy of SQL Server instance connection strings. In other words, a tree view that lists and categorizes your instances. The behind-the-scenes way this is accomplished is very different between the two systems, and this means there are advantages and disadvantages to each.

Although both names contain the word “servers,” what we’re actually dealing with are instances of SQL Server. I guess it was a design decision by the SQL Server team to call them Registered Servers instead of Registered Instances. It’s also a bit confusing because Registered Servers refers to one user-defined hierarchy, while Central Management Servers (note the plural) would refer to multiple hierarchies.

Differences

The main difference is that a Registered Servers list is only visible to one Windows user, while a Central Management Server list is publicly available to anyone with enough permissions to view the list.

The best way to show all the differences is probably just to put them in a table, so let’s do that:

	Registered Servers	Central Management Server
Storage Method	XML file in Windows roaming profile	msdb database
List Visibility	Current user only	Everyone who is authorized (view nothing, view all, edit all)
Security of Instance List	Based on Windows user context	Managed by SQL Server permissions, and msdb database roles
Types of Instances	Database engine, SSAS, SSIS, SSRS	Database engine only
Connection String Authentication	Windows Authentication and SQL Server authentication	Windows Authentication only

So now you can see the big tradeoffs between the two systems, and why I said it’s likely you’ll need to use both. Central Management Servers should in theory be able to support all types of instances, but sadly, it just isn’t implemented for some reason. My fingers are crossed that it’s implemented in the next version.

Setup

Since the storage method of the two system is very different, there are very different concerns when it comes to setting them up. Really there isn’t much to Registered Servers because of the fact that the list is isolated to a single user. With a Central Management Server, however, there are many more parts in play — the price we have to pay for power and flexibility — and so there are many more guidelines.

Briefly, the way I like to set things up is as follows:

• Use Registered Servers for instances that I keep on my local machine (for development, or playing around) that aren’t usually accessible from the rest of the network, and for SSAS/SSIS/SSRS because there’s currently no other choice.
• Set up a dedicated SQL Server instance just to be the Central Management Server instance, and register all publicly-available instances within a given domain. (One Central Management Server instance per domain is a good idea, particularly if that boundary separates development and production, for example.) The instance doesn’t have to be powerful in terms of either CPU or memory, but it does need to be very reliable because we will be using it to manage our servers in good times and bad. Even Express Edition can be used to set up a Central Management Server, but for obvious reasons, you will want to take regular backups of at least the msdb database by some means.
• I recommend installing a Central Management Server instance on a separate management machine (again, probably one per domain), and not on a production machine. (I wrote a bit about this in the comments on my answer here). Definitely do not register a Central Management Server on an existing production instance — not only is that a bad practice in terms of separation of concerns for many reasons, but there’s also a limitation that a Central Management Server instance cannot appear in the hierarchy within “itself” as would happen in this case.
• (If necessary) Configure security to determine who can view, and who can edit a Central Management Server list. As eluded to in the table, this is accomplished by assigning membership to the msdb fixed database roles ServerGroupReaderRole and ServerGroupAdministratorRole (MSDN page) as appropriate. It may also be desirable to use multiple Central Management Server instances to separate tiers of registered servers, such as development, test/QA, and production, because there isn’t any other way to control instance-level visibility besides the aforementioned roles.

Organizing the List

One of the great features of both Registered Servers and Central Management Server is that registered instances can appear in the hierarchy multiple times. Why is that great? Because it allows us to organize the instances in multiple ways within the same hierarchy.

We may want to group our instances together by:

• Version and/or edition
• Criticality (24/7, business working day, etc.)
• Business area or department (development, test/QA, production, etc.)

And I’m sure you can think of a few others for your environment as well.

In fact, we may wish to categorize by more than one of these things for frequently-used parts of the hierarchy. When arranging the list for maximum usefulness, think about how to organize it such that if a single group is selected in the tree, changes would happen to all the instances within that group folder.

If you’re setting this up for the first time, and assuming there aren’t too many instances in play, it may just be a good idea to start with a relatively flat hierarchy, and build the groups as you find they’re needed. The reason why this strategy isn’t a good idea with many servers is because the ability to quickly move servers between groups in the UI is extremely limited. For a Central Management Server, the system tables in msdb could probably be edited directly, but I can’t say I’d recommend that, and I haven’t tried it myself either.

 

In a future post (likely a video demo), we’ll dive into using and leveraging Registered Servers and Central Management Server to their full potential through the UI.

To leverage Central Management Servers externally, such as through PowerShell or an external application, we can run queries against the following two views in msdb: dbo.sysmanagement_shared_registered_servers and dbo.sysmanagement_shared_server_groups, or use the various stored procedures to manage the list.