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

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


Hi everyone! In the first part of the series, I introduced
you to Registered Servers and Central Management Servers and how to set up the server lists.
Today in the second part, I’m going to show you how to put these features to work. If
you’re not familiar with the basics of these features, please watch the first part of the
series by clicking the annotation here. The link will also be in the video description. So, let’s get right into the demo now. After the server hierarchy has been set up
initially, we can start using it to manage the servers. I’m first going to show you the
functionality of merely having the servers in the list, and then we’ll take a deeper
look at how to use the core features to manipulate multiple servers at once. The most obvious feature is service control.
When you have enough permissions on the servers, you can quickly see the state of the services
just by looking at the icons. There’s also the ability to start and stop the services
directly from the tree. This functionality doesn’t apply to multiple servers for obvious
reasons, but it’s handy to see the state of all the services in one spot. When a Central Management Server is registered
in the list, it acts primarily as a Group, just like the Local Server Groups folder does.
To manage the Central Management Server instance itself, there’s an extra item in the right-click
context menu called Central Management Server Actions. This gives you the same options as
the other context menus, but these items only apply to the one Central Management Server
instance, not to the entire tree. One of the right-click context menu options
is Object Explorer, which will connect Object Explorer to all the servers contained in the
selected node in the tree. If a single server appears multiple times in that subtree, it
will only be connected to once, unless different login credentials are used, which is only
possible using Registered Servers. In this case, each unique combination of server and
credential is connected to. If a server/credential combination is already connected, it won’t
be connected to a second time. Without Registered Servers or Central Management
Servers set up, and you open a query file from Windows Explorer or click the New Query
button, the query window automatically connects to the server-in-context in Object Explorer.
When you’re using Registered Servers or Central Management Servers, you have to start paying
much more attention to which server — or Group of servers — is selected before you
take an action that will use the server-in-context. This is a typical power-versus-danger trade-off.
You’re given the power to connect a query window to multiple servers simultaneously,
but it’s dangerous if you don’t realize you happened to have the wrong node in context
in the tree. I’ve found it useful to get into the habit of when I’m done using Management
Studio, switching either to an empty Object Explorer pane, or selecting a single local
server (not the Central Management Server) from the Registered Servers pane. When a query window is connected to multiple
servers, by default the status bar turns pink where it’s usually yellow, and you can see
an indicator of how many servers are connected. These numbers are important because sometimes
one or more of the servers will fail to connect, and you’ll be able to see that here, where
you might not just in the server hierarchy. If you’re just starting out with multi-server
queries, or if you have trouble distinguishing between the yellow and pink colours, it may
be helpful to change the colours of the status bar so they’re more visible. You can do so
by going to Tools, Options, Text Editor, Editor Tab and Status Bar, and then selecting appropriate
colours in the Status Bar Layout and Colors section. While I personally prefer to keep
as many default options as possible, on machines I use a lot for management, I set the status
bar to be at the top of the query window rather than the bottom for better visibility. Now that we have all the interface stuff out
of the way, let’s dig into how multi-server queries work. I already have a query window
open that’s connected to two servers, and I’m going to run a simple SELECT statement
that will return a result set that has one column and one row. Looking at the results,
obviously there’s more going on here. Not only did we get back two columns, but there
are two rows as well. What happened? Management Studio is connected to two servers, so it
ran the query we provided on each of them. In total, that’s two rows. When the results
came back, Management Studio automatically added the extra Server Name column to let
us distinguish between the result sets returned by each server. This behaviour of merging
the result sets together is configurable, which I’ll demonstrate a little later on. It’s important to remember that the queries
you write are executed independently on each of the servers. It’s easiest to see this behaviour
by running a different query that sets the order of the results. You can see that the
results from each server were ordered correctly, but the result sets from each server are independent:
they aren’t sorted together. If you want to do that, the only recourse at the moment is
to copy-paste the entire result set into a program like Excel, and sort the results there.
Also, if you’re running a process that encapsulates a chunk of work in a transaction, each server
will have its own transaction: the operation will not be atomic across all the servers
at the same time. Because the queries run independently, if
you aren’t careful about which servers you’re connected to, there are several issues with
queries that you could run into. The two servers I’m connected to right now have a different
collation — one is case insensitive, and the other is case-sensitive. I’m going to
run a basic query, and we can see this executes correctly on both servers. Now I’m going to
make a slight change to the query and run it again. This time, we get one row back,
and also an error. The query ran successfully on the case-insensitive instance, but not
on the case-sensitive instance. When you’re writing code, I consider it a best-practice
to always use the correct case for identifiers, particularly for management queries, even
if you don’t normally use case-sensitive instances in your environment. I’m going to undo the
last change, and make a different change. When I run this query, now there are no errors,
but this time we only got back a single row, again from the case-insensitive server. You
can see how tiny little details like this can introduce really insidious bugs if you
aren’t careful. For the next example of what can go wrong,
I’m going to connect to servers of two different versions. In this case, the PRODSQL02 instance
is 2008 R2 and PRODSQL03 is 2012. I’m going to run a simple SELECT statement from one
of the DMVs. Of course, I designed this to happen, and the problem is that this particular
DMV had a column added to it in 2012. The queries on each server executed successfully,
but when Management Studio tried to put them together into a single result set, all it
did was take the schema from the first server that responded and expected the same schema
for all the other responses. In this case, though, it was different enough that it couldn’t
be merged, and so it was actually Management Studio that generated this error, not one
of the SQL Server instances. You can see now how separating servers by version in your
server hierarchy, and also explicitly specifying column names in your queries can help avoid
this type of situation. If you needed to see this column while running on servers that
don’t support it, a couple of options are to use dynamic SQL to artificially add a NULL
column on the lower version, or disable the option to merge the result sets by going to
Tools, Options, Query Results, SQL Server, Multiserver Results, and then disabling the
Merge Results setting. Now the query executes successfully, and Management Studio doesn’t
have a problem showing two separate results sets with different schemas. At the far right,
we can see the new column returned from the 2012 server. So that’s multi-server queries. Let’s finish
up by taking a look at using Policy-based Management with a Central Management Server.
As I mentioned in Part 1 of the series, you can store all your policies and conditions
in the Central Management Server instance. Just like the server hierarchy, this is stored
and managed through msdb, which is complete with views, stored procedures, and a database
security role. You can manage the policies by using Object
Explorer to connect directly to the Central Management Server and editing them that way,
or you can import some of the Microsoft-provided best-practices policies, which is what I’ve
already done on my instance. Where the multi-server ability comes into
play is back in the Registered Servers pane in the right-click context menu. We can select
Evaluate Policies, and in the policy source dialog box, instead of selecting a set of
files, we can choose to connect to a server — in this case our Central Management Server
— and it will read all the policies stored in that instance from msdb. I’m just going
to select one of the policies here. When I click Evaluate, Management Studio loops through
the selected policies and evaluates them against all of the servers within the original selection
context. As with the pitfalls I mentioned when talking about multi-server queries, if
you’re developing your own policies and conditions, be aware that they should work correctly in
as many situations as possible. Also feel free to take advantage of the policy Category
field: sometimes you want to evaluate a subset of policies against one group of servers,
and this is a handy way to let you sort the list of policies, and select the appropriate
ones without a lot of effort. One other thing to note about multi-server
policy evaluation is that at least as of making this video, unlike multi-server queries, the
evaluation of policies on objects is done completely synchronously, which may take a
tremendous amount of time if you’re evaluating against many objects. In cases like this,
you can partly parallelize it yourself by splitting up large groups of servers into
smaller groups. This isn’t ideal, of course, but at the moment, it’s the best you can do. As you just saw, we took some of the features
you already knew, and used them to manage multiple servers at the same time. While Registered
Servers and Central Management Servers don’t offer a complete solution to server management,
they’re still powerful tools that come out-of-the-box with SQL Server. 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!

2 Comments

  • ravirajch1

    It's interesting and very useful video. Is it possible to upload SQL server isolation levels and locks related videos?

  • bak bakayoko

    Hello,
    Thank you for your deep coverage of subjects and your ability to simply them.
    You are gifted!
    Suggestions for Videos:
    1) End to end Performance Tuning Using Wait Statistics with step by step example.
    2) En to end Query Tuning with step by step example.
    Thank you very much for your time and effort!

Leave a Reply

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