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:
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:
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:
Policy-Based Management blog: http://blogs.msdn.com/sqlpbm/
Books Online: Policy-Based Management How-to Topics
Books Online: Administering Servers by Using Policy-Based Management
Enjoy and thanks for reading,