Registered Servers and Central Management Server: In Action (video)

In a previous post/video, I introduced Registered Servers and Central Management Server, and showed how to set up the server hierarchy.

In this video, I show you how to use these features to manage groups of servers using query windows and Policy-Based Management, and we see some of the pitfalls that may be encountered along the way.



Please subscribe to the YouTube channel to be notified first when I post a new video, and to see extra content that won't appear here on the blog.


Registered Servers and Central Management Server: Setup

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.



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.



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.


Scaling Server Management - Theory

No one wants to constantly monitor and maintain their server farm 24 hours a day, 7 days a week.

In the face of a growing business with a growing number of servers and a growing number of databases to manage, it can be easy to get overwhelmed at times, especially when the business grows fast. This is why scalable management techniques are so important, even if you only have a single server to manage right now.

What would your management techniques look like if you suddenly had to manage 10 times as many servers than you do now?

Trick question. You should already be managing your servers that way.

But how does one accomplish that when it’s so intangible right now?

Ultimately, there is only one concept to get handled: consistency. The other, meaner, uglier, eviler side of that coin is exception, which is something to be avoided like the plague. Interestingly, to explain the concept of consistency, it’s much easier to cite cases of exception; to explain what consistency isn’t. You might even call it... inconsistency. Hmmm.

  • Do you have a database named Production1 that is backed by files named Testing1.mdf and Testing1.ldf? Is it actually a testing database?
  • Are your companys’ server or instance names picked by randomly selecting words from the dictionary?
  • Do you have an instance that contains development, testing and production databases, or any combination of more than one of those? (“...that was the testing database, right?”)

"Wait a second," I hear you say, "that isn’t hard to manage. In fact, it’s easy to remember." Famous last words.

...well, maybe not famous.

...and maybe not last.

My point is that remembering is precisely the problem.

Can you keep track of a single inconsistency for each of 10 databases? Alright, sure, no problem. How about 100 databases? Yeah, it’ll suck, but I can do it. How about 1,000? Uhhh... How about 10,000? No way! -- and please don’t tell my manager.

Clearly, your own memory does not scale as well as technology does.

"Aha! I know, I’ll use automation!"

Now we’re on the right track. But automation is not a silver bullet. You can try to automate your way out of an inconsistent situation (without changing the situation itself). What you’ll end up doing, though, is spending most of your time maintaining the automation system. It’s trading an intense direct effort, for an intense indirect effort with minimal scale, plus a whole lot of opportunity for mistakes, downtime, and even data loss.

If you have a database to manage granular settings of your databases, you’re (probably) doing it wrong.

In the context of database management, automation is really only useful in two ways:

  • Making many things similar. (Set all user databases in an instance to use the FULL recovery model.)
  • Doing something to many similar things. (Take transaction log backups every 15 minutes for all databases using the FULL recovery model.)

That may seem really simple, but it’s also really powerful. This is the key to scale.

The number of production databases is the sum of:

SELECT COUNT(*) - 4 FROM sys.databases

over all of your production servers, not "Umm... give me a few minutes." (Hint: growing more fingers and toes for counting does not scale well.)

Your ability to keep everything consistent directly reflects and affects the extent to which you can leverage automation to manage your servers, and therefore not have to scale yourself.

  1. Eliminate barriers that prevent consistency.
  2. Create consistency.
  3. Create systems to enforce the consistency you worked so hard to achieve.

In a subsequent post, I’ll introduce and explain some of the tools and techniques you can use to accomplish these steps in SQL Server.