At first, let’s start with a cool relaxation exercise. Close your eyes and imagine that your DBA work environment includes only one server. Nice, isn’t it? Now let’s wake up to real life. As you all know typical, small, simple environment will include about 10 servers and in big companies it can reach to thousands or more servers (I can’t imagine how many Microsoft has). Therefore raises the need to monitor, manage and organize all servers.
Examples of organized group of servers can be by their work environment (dev, qa, production), “SQL Server” versions and even by geographic spreading.
As part of the DBA work routine we would like to collect info to our servers on a daily basis. We can do it by connecting to each one of server separately or by third part applications, but that can be very inconvenient and can take a lot of time.
It would be very nice if in our basic tool the “management studio” we can execute one command and “SQL Server” will connect to each server and collect all the data.
It would also be nice if it will combine all the data in one table with a new “server’s name” column.
Since 2008 it can be done, and it’s called “Central management servers”, also known as CMS. With that new feature we can create groups of servers from all kinds of “SQL servers” versions (old and newer). You have to choose one server (2008 version) to be the central management server and it will hold the list of the server’s groups. The list will be available for other users for as long as the server service is running. That way we will be able to query each of the groups.
In order to setup CMS there are few requirements:
You will need at least one instance of “SQL Server” 2008 that will be the management server.
All the connection to the servers should be with windows authentication because CMS cannot hold the connection details for SQL authentication.
The server that was defined to manage the list of servers cannot participate in the registered servers.
This is how to create CMS:
“BOL” manual
In SQL Server Management Studio, on the View menu, click Registered Servers.
In Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
In the New Server Registration dialog box, register the instance of SQL Server that you want to become the central management server.
In Registered Servers, right-click the central management server, point to New, and then click New Server Group.
Type a group name and description, and then click OK.In Registered Servers, right-click the central management server group, and then click New Server Registration.
In the New Server Registration dialog box, register one or more instances of SQL Server that you want to become members of the server group.
And here is an example of CMS creation:
Here are some examples for queries on multiple servers:
Limitations and cons:
It is impossible to save the table data because each row represent different server. If you try to insert the rows to another table it will create separate table for each server with only its rows.
You cannot join the data with other tables.
You cannot sort the data. The order by command will sort the rows by each server.
“SQL Server” connects to each server and run your commands. If one of the servers is “case sensitive” and your commend is not, then you will get an error.
You must confirm that all servers return the same number of columns. If that is not the case, you can change the setting of CMS to return separated tables instead of one big table.
Note about security:
As you imagine, if you don’t have access to read the data of one of the servers you will not be able to run query on a server that is listed in the group.
For conclusion, CMS can save you a lot of expansive time and make your multiple servers much more manageable and easy to use and monitor.