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!