SQL Server 2008 Central Management Servers – have you seen these?

There's a new feature of SQL Server 2008 that during beta was called a Configuration Server… In RTM it was renamed to a Central Management Server. It's not overly obvious and without knowing how cool it can be – you might skip right by it. And, if you're using SQL Server 2008 to manage 2000, 2005 and 2008 servers – this actually works for all of those (and can be quite cool).

In the "Registered Servers" pane, expand the "Database Engine" option to see this feature. It's basically a separate option in addition to Local Server Groups. Right-click to register a Central Management Server.

A Central Management Servers (at first glance) seems as though it's solely a way to store server groups and registered servers – centrally – so that you (and your team) have easier access to the registration properties of a group of servers. And, in setting it up, adding groups, and registering servers – well, it doesn't seem like it is any different. In fact, that's all it is – in terms of creation. However, there are new options in terms of how to use it. It definitely IS different! In fact, it's in this simplicity (and the options it exposes) that lies its coolness. Let me show you :)

On a VPC (that we typically use for demos/labs, etc. for SQL Server 2008), we have 5 instances installed (SQLDev01, SQLDev02, SQLDev03, SQLExpress and ConfigServer). All instances (except Express) are SQL Server 2008 RTM Developer Edition and they're just simply named instances (there is no special designation (other than its name) for "ConfigServer"). I created the "ConfigServer" instance as a "server to hold configuration and general centralized management features – like Policy-Based Management, the management data warehouse for Performance Data Collection, a Master Server (for Master Server/Target Server Administration), and even a centralized management server. For many of these things you'll want to use a non-Express Edition of SQL Server as some features won't work on SQL Express (I'll explain more on this later). So, for this example, I'm going to use the ConfigServer instance (note: Centralized Management Servers *DO* work with SQL Express – something I previously thought was not possible… and, this is WAY cool). Regardless, the overall features that I plan to use with this server (like Policy-Based Management) require a version other than SQL Express so I'm going to use a Developer Edition instance for this centralized management server.

OK, so in this case, I created two groups (Development and Production) and each group has servers…

If I right-click on ANY level (a specific server, a group, or the Centralized Management Server itself) then you get multiple options as seen above:

  • New Query
  • Object Explorer
  • Evaluate Policies
  • Import Policies

In this case, I'm going to right-click on the Production Group and choose New Query – this opens a query window with one slight difference. The status bar at the bottom has a different color. OK, I bet you won’t even really notice this but a regular query window has a pale yellow status bar. A centralized management server query window has a pale pink status bar. I find that this isn’t overly noticeable – so I change it to a much more bold color (fuschia!). To change this, use Tools, Options, Text Editor, Editor Tab and Status Bar and then change the setting for the Group Connections option under the Status Bar Layout and Colors section. Anything that stands out is preferred. The reason why will be apparent soon…Within this query window, anything I execute will be executed against each of the servers in this group. I can even choose to execute a query against the centralized management server itself and this will execute against every server in every group. Powerful, but potentially-too-easily dangerous.

Here, I’ll execute SELECT @@version against the Production group:

The default behavior is that this is executed against all servers and the results are unioned (or merged). If you want to change this behavior you can also set this in Tools, Options under Query Results, SQL Server, Multiserver Results – “Merge Results” equals True. You can also set whether or not the login name is appended to the results (this defaults to false) in addition to whether or not the server name is appended (this defaults to true). You can change all three of these settings.

So, why did I make the status bar fuschia… what if I execute DROP DATABASE dbname… yes, it will try and drop this database from all servers in the group. While very powerful, this is something of which you should be careful.

OK, so here are the most interesting things about centralized management servers:

1) To designate a server as a CMS – that server must have an msdb. Originally, I thought that you couldn't use SQLExpress but because SQLExpress *does* have an msdb, this does work. However, SQLExpress lacks many other features/capabilities so outside of simplifying connectivity and query access, I'd probably target a higher edition of SQL Server. Even more specifically, if you're interested in where all of the server group information is stored:

Local Server Groups stored in: C:\Documents and Settings\<user>\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSvr.xml

Centralized Management Server details are stored within the msdb in:

dbo.sysmanagement_shared_registered_servers_internal

dbo.sysmanagement_shared_server_groups_internal

2) Not only can you execute queries but you can right-click and choose Object Explorer and each server will be connected to and entered into your Object Explorer window. This will minimize your manually connecting/opening each server one by one.

3) AND, the servers can be servers other than SQL Server 2008… I've connected to 2005 and 2008 and you shouldn't have a problem adding any other SQL Servers – even 7.0…but, I haven't tried that (let me know if it works for 6.5 :).

3) Finally, in addition to executing queries, you can also Evaluate or Import Policies. This is something that can be really powerful. In addition to executing something directly, you can evaluate a policy against a number of servers in one step. Policy-Based Management is something that you'll start (if you haven't already) hearing a lot more about. And, since I'm always behind at blogging – check out these other links/blogs for more info on PBM:

Enjoy and thanks for reading,

kt

12 thoughts on “SQL Server 2008 Central Management Servers – have you seen these?

  1. Mmmm… just a few comments.

    You can also execute a query (or open object explorer, evaluate policies, etc )against multiple servers creating a group directly from "Database Engine"

    And, with the version of SSMS I have (10.0.1600.22 (SQL_PreRelease)), I can’t register servers below 2008 version through "Central Management Servers"

    Do you have another version?

  2. The centralized management server itself must be SQL Server 2008. However, the registered server group and servers can be any version (well, again, I haven’t tested pre-2000 but I can’t see why they wouldn’t work). And, yes, you can do this with local server groups as well so the "execute against multiple" does work with local OR CMS but the benefit of using a CMS is that other folks on your team can get easy access to this arrangement and grouping of servers centrally!

    Have fun!
    kt

  3. I LOVE the CMS functionality… but there seems to be some functionality missing. (Or maybe I’m just missing it…)

    Is there any way to query these groups through TSQL, without opening a window in SSMS?
    For example – to use the CMS in a job instead of linked servers etc.
    This would be a massive help from an administration point of view….

    B

  4. Kim,

    I just added all of the servers from my SSMS 2005 list into the SQL 2008 CMS. When I ran the query SELECT @@SERVERNAME, some of the servers were unable to connect as they have been decommissioned. I removed these from CMS and reran the query, but received the exact same error for the same servers that I just removed. I verified than none of these servers were in the sysmanagement_shared_registered_servers or sysmanagement_shared_registered_servers_internal tables in msdb, and made sure they were not in the RegSvr.xml file.

    I noticed that the first time you run a query against all servers, it takes longer than subsequent queries. I’m guessing the list of servers got cached so I cycled the services on instance running CMS, but still receive the same results.

    Any idea how to remove a server completely from CMS?

    It

  5. I love this new ability to run queries againt group of registered sql server. The best thing is that it allows to register sql servers of lower version and it still works (too bad intellisense does not work with lower versions :( )

  6. RE: Aaron’s comment – you’re right, it keeps the server list based on when the session connected to the group. If a server was not running then it fails – and continue’s to fail even after the server is restarted. So, the way to fix this is to remove the server (from the CMS) and, to disconnect the session. It should work/be removed when you reconnect a new session!

    Have fun! This is a great feature!!
    kt

    PS – Sorry for the delay in my comments. I *just* found out that I had a bad setting and a HUGE number of unapproved comments. I’m going through and approving them as well as trying to comment on them. Again, sorry for the delay! Cheers.

  7. I think it is neat that the database collections are available in queryable form in the msdb or in readable form in the RegSrv xml file. It seems to me that this would open the possibility of looping through in, say PowerShell, and performing actions with each server… such as for each server, query sys.databases with the where clause of choice and backup them up, as was suggested above.
    Very nice feature, thank you for your post!

  8. Is there a way to order the result set? For example, when I run a little query to pull all databases’ sizes, I can’t seem to order the results. Also, it seems as though the results are ordered by when the CMS receives the responses from each server. I would like a way to order the results.

    I like that I can have servers show up in multiple CMS groups.

  9. Maybe I misunderstand but: what is the difference between the "New Query", "Evaluate Policy" or "Object Explorer" menu option started from the "CMS" versus the same actions fired from "Database Engine\Local Server Group" ??

Leave a Reply

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

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.