Tip:
Highlight text to annotate it
X
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!