The most obvious advantage of a CMS is that it holds an inventory of all the SQL servers in our environment. We can now connect to a single place and get a quick view of every server in our environment without having to remember each server and instance name.
We also have the flexibility to arrange our server connections in folders. Doing so allows us to get an even better picture of our environment at a glance. We can arrange our connections in any manner that we choose. This is entirely dependent on your business model so that you can match your folder structure to your business support model. Common examples are to model your folder structure in a logical manner by server function, geographic location, SQL Server version, or any combination thereof.
Let’s say our company has their support model broken up into regions, so if an administrator supports the West region he can easily discover and manage the servers he supports. Our business model also dictates that every production environment should have both a test and development environment. That encompasses our business model, but as the administrator you also want to separate and identify different versions of SQL Server. This helps avoid running a query that may be supported on one version but not another. Here is what that implementation might look like:
Beyond housing a server inventory and being able to arrange it according to our business needs, a CMS gives us the power to run a query against multiple servers. This is the most powerful feature of CMS and the biggest reason to utilize it. If your manager comes up to your desk and says that the company has acquired a budget to upgrade your SQL Servers and he has to submit the request within two hours, how long would it take you to find and evaluate the version of every SQL Server in your environment? What if a new cumulative update patch comes out that fixes a security issue and you need to know which servers need the patch? Using our example, we can find out the version of all SQL Servers in the West region by right clicking the West folder and selecting “New Query”. In the new query window we type in “SELECT @@VERSION” and this is what we get.
That was fast and easy, and we can instantly report to our manager that we have 3 instances of SQL Server running SQL 2008 RTM SP2. We can also see that one of them is running 64bit and the other two are running 32bit.
There are several other things to make a note of here. First is that there is an added column returned named “Server Name”. CMS adds that column to every multi-server query so you know which result came from which server. You will also notice in the lower right corner that the name of the group (West) we queried is displayed to show what the query was executed against in our CMS.
The last thing to note is what you will see in the query window prior to executing your query. The status bar at the bottom will be a pink color as opposed to the usual yellow color. This lets you know that the query window is attached to multiple connections. You will also notice that it says “Connected. (3/3)”. This lets you know how many connections any queries in this window will be executed against.
The last advantage of CMS comes when using it conjunction with Policy Based Management. It allows you to evaluate policies against multiple SQL instances simultaneously, much like using the multi-server query feature. It’s as simple as right clicking on a server connection or folder and selecting “Evaluate Policies”. PBM and CMS complement each other very well.