The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
Setting up a central management server group is a lot like a group of registered servers, with the major exception that once you do you can perform tasks on all servers in the group at once. Today we take a look at setting up a Central Management Server
The user interface for this feature is found under the registered servers window. Yesterday we talked about setting up a collection of registered servers so glance back there if you need a refresher. Setting up a central management server here is basically the same except for in place of a folder node containing a group you designate an actual SQL Server as the group container.
Right click the root ‘Central Management Server’ node and select ‘Register …’.
Now right click the centrally managed server and start adding ‘New Server Registration’s’ underneath it.
Why would you want to have a central management server instead of a simple grouping in folders? Well, take audits as an example. Say you wanted to verify that all your servers are on the same version / service pack / os etc. This is trivial once your boxes are set up under a central management server. Need another great sample usage? How about checking the status of backups across all servers in your enterprise? This is easy with a central mgmt. server.
Quick Server Audit Sample:
To get you familiar with this feature let’s do a quick group query. Open a new query against the central management server that you’ve created following the steps above. Notice in the status bar that it reports you are now connected to all servers within that group.
With the four servers registered under my central management server I now only have to issue a query against the group. The server registered as the central management server will package the query or action up and issue against all servers on my behalf. The results are then merged and returned in a single result set with a leading column identifying which server the results came from.
In an environment like ours, where we have a variety of stacks in various stages of the software life cycle – it also makes testing queries, settings, static data, etc. so much easier. You can issue use statements that will ensure the default database is the same across all servers. As long as the database / object your querying exists on each of the boxes you’re golden.
But what if they are not?
A really merciful feature is that a failure or error on a single server will not keep results from returning on all the other servers. The message window will identify which server failed but all other results are intact.
Now that you’ve scratched the surface of setting up a Central Management Service I highly recommend you take a deep dive. One of my all time favorite bloggers Phil Factor has an excellent article digging into some of the deeper architecture of this feature. There are some security gotcha’s that his article will help you navigate. He also demonstrates how to leverage these servers with powershell and other external tools. Good stuff.