Registered Servers and Central Management Servers in SQL Server (Part 1)
Articles,  Blog

Registered Servers and Central Management Servers in SQL Server (Part 1)


Hi everyone! Today I’m going to introduce you to Registered
Servers and Central Management Servers. These are two features built into SQL Server and
Management Studio to organize and manage your server infrastructure. They help you by scaling
some of the features you’re already familiar with from one server to multiple servers.
While neither Registered Servers nor Central Management Servers are what I might call a
“complete solution” to server management, they’re still useful and powerful, and most
importantly, they’re available automatically when you install Management Studio. At their core, both Registered Servers and
Central Management Servers are designed around the task of saving and organizing connection
strings to your servers. The basic difference between the two is where the connection strings
are stored, and this has a big impact on how each of the two features is used, and by who.
It’s also worth noting that both features can be used at the same time, and for some
environments you may have to, specifically because of the current state of implementation
of Central Management Servers, as I’ll explain shortly. In this video, I’m going to first give you
a full picture of Registered Servers, and then we’ll move on to talk about Central Management
Servers, and compare them to Registered Servers. In the second part of the series, I’ll show
you some of this functionality in action as you might use it in the real world. As I said before, both Registered Servers
and Central Management Server are available out-of-the-box with SQL Server, but unfortunately,
the user interface isn’t visible by default. To show it in Management Studio, we go to
the View menu, and then Registered Servers, and you can see a Registered Servers pane
has appeared. I like to dock it with the Object Explorer pane, because I frequently go back
and forth between the two. Across the top of the pane is a set of icons that represents
the type of server being shown in the tree view below. These icons work like a group
of radio buttons. When we think about multi-server management, usually database engine instances
come to mind first, but it’s very possible that you can have multiple instances of other
types of servers in your environment too, such as Analysis Services or Reporting Services.
Registered Servers allows you to save the connection strings to all of the different
types of servers. If the Database Engine node is expanded, we’re
presented with two different folders: first is Local Server Groups and the other one is
Central Management Servers. The latter, of course, we’ll ignore for the moment. The Local
Server Groups node is the root node of a tree that consists of folders, which are also called
Groups, and Servers. Both Groups and Servers can go inside other Groups, so you can set
up any hierarchy you want. The hard part is figuring out how best to
organize the servers for your environment, because you want to think about how to manage
the servers in groups, as opposed to individually. A nice feature is that the same server can
appear multiple times in the hierarchy: this means you can set up multiple branches that
takes the same set of servers, and groups them differently. Some common ways to the
group servers are by criticality — development, QA, and production; by department — sales,
marketing, finance, etc., and also by version. Grouping by version deserves some special
attention if you’re going to be working with more than one, which chances are you will
be. You can be super-granular about it, but most of the time, grouping by major and minor
is fine. It really depends on the feature you’re using: sometimes a service pack will
introduce new features, and if you’re using those, you’ll want to separate the servers
that support the feature from the servers that don’t. I recommend setting up the tree
so that you can select all the servers of a particular version, and also of a particular
version and higher. This is so servers that don’t support a particular feature can be
easily excluded. To add a new Group to the tree, it’s really
easy — just right-click the parent, and select New Server Group. To add a server to the tree,
there are a few different ways. You can right-click the Group folder where you want to put the
server, and select New Server Registration. An empty dialog box will pop up, and this
is where you can enter all your connection information. If you’re already connected to
a server in Object Explorer, you can right-click the server node, and click Register. This
will pop up the same dialog box, but now it’s populated with all the connection information
that was used originally. When you Save using this method, the server will be placed in
the Local Server Groups folder, in the root. Finally, if you’ve used Registered Servers
before, there’s Import/Export functionality that can be accessed by right-clicking any
node in the tree, going to Tasks, and then either Import or Export. I’m not going to
cover the basic functionality in more detail because it’s pretty self-explanatory. At the beginning of the video, I mentioned
that Registered Servers uses a different method to store the connection strings than Central
Management Servers. Registered Servers saves this information as an XML file in your Windows
roaming profile. This means that if you’re using a domain account, your server list will
be available anywhere you log in as long as you have Management Studio installed on the
local machine. The downside to this approach is that the list is visible only to you. If
you’re managing many different servers, chances are there’s a team of people that are managing
the same set of servers. In this case, it would be nice if everyone had the same list
of servers, and that list was stored and managed in one central location. It wouldn’t be a
great solution if everyone has a copy of the same master list, or worse, if everyone was
maintaining their own list. Central Management Server, which was introduced
in SQL Server 2008, solves this problem. That version number is important because the connection
information is stored in the msdb system database, and those structures only exist in SQL Server
2008 and higher. With this storage method comes an important best-practice, which is
to install a completely separate instance of SQL Server to be used only as a Central
Management Server. This instance won’t take up much memory or storage, and it doesn’t
have to be insanely fast, it just has to be very reliable and accessible. The database
structures in msdb are available in every edition, so even Express will work, but I
do recommend backing up msdb so the list is recoverable. If you have a separate Windows
server that you use only for management purposes — and I recommend that you do do this — this
is the ideal place to put the Central Management Server instance. Regardless, there’s a limitation
that a Central Management Server itself can’t be registered in the tree. This means that
if you try to use an existing instance that you also want to manage, you’ll come up against
a dead end pretty quickly. Even if you could do that, it’s not necessarily a good idea.
You don’t want to lose the ability to manage all the other servers in your infrastructure
if one of the servers is having a problem. Storing the server registrations in msdb has
some advantages and it has some caveats. Because the list is now public, this introduces the
need to secure the list. Microsoft was kind enough to provide two database security roles
in msdb that can be used to grant access using the standard SQL Server methods. Reading the
list of servers is done through views and managed by a set of stored procedures. So
not only could you write your own queries to look at this information, but you could
also construct a client application or even integration components that either read or
modify the list. If you’re using Policy-based Management, your policies and conditions can
also be stored in the Central Management Server instance, and made common across your entire
infrastructure. Now, the caveats. First and foremost, at least
as of making this video, Central Management Servers only support registering database
engine instances. This is really unfortunate, because if you inspect the msdb tables where
the data is stored, you’ll find that it does support all the different types of servers
— it’s just not implemented in Management Studio. Second, only Windows Authentication
is supported: no user names or passwords are stored in msdb. You should be managing your
infrastructure using Windows Authentication, so I don’t consider this a drawback — it’s
more like enforcement of best-practices — so it’s just something to be aware of. In order to set up an instance as a Central
Management Server, it’s really simple. You go to the Registered Servers pane, and you
right-click the Central Management Servers node, and select Register Central Management
Server. You’re presented with the familiar connection settings dialog box, and you enter
all the information you need to. Once that’s saved, using the server tree is exactly the
same as Registered Servers before, but this time with the Central Management Server as
the root node. Registering a Central Management Server is
just a special case of a Registered Server, so it will automatically take advantage of
that storage mechanism and store the list of Central Management Servers in your roaming
profile. When I say “setting up an instance of a Central Management Server,” that’s a
little deceptive, because every instance can be a Central Management Server. By registering
an instance to be one, all you’re saying is, “hey! store my server list here.” Nothing
actually changes in the instance to say that it’s acting as your Central Management Server.
Really, it’s an agreement among people that the server list will be stored in a particular
instance. So, that was a pretty thorough comparison
of Registered Servers and Central Management Servers, and a quick tutorial about how to
get started with them. To the right, I’ve compiled a table that summarizes the features
of the two systems. In the second part of this series, I’ll put
what we’ve learned here into practice and show you how to use these features to more
easily and effectively manage your server infrastructure. If you have any questions about this video,
or if you’d like to suggest topics for future videos, please leave me a comment below. Thanks for watching, and I’ll see you in the
second part of
the series!

One Comment

  • Rob Nicholson

    Hi, I have created a Connect Item in hope that Microsoft will add the ability to filter Registered Servers (that way you could run queries on a subset of instances instead of whole folders).  https://connect.microsoft.com/SQLServer/Feedback/Details/1051746

Leave a Reply

Your email address will not be published. Required fields are marked *