In a couple of my previous blogs I have labored on about the trouble I sometimes encounter when starting a new role. In my efforts to make sure I can reduce this stress level for my fellow DBAs, either once I have moved on, or as helpers join me in my current role, I have made as much use as possible of a feature introduced in SSMS 2008, the Central Management Server (CMS).
From its name, it should be obvious what this is, it is a central point for managing servers. It allows you to select a single instance and under it, register all the servers on your network. This is not just limited to SQL 2008, but all versions of SQL past and present. Once you have configured all of your servers, anyone else who connects using SSMS and registering the server, will have the same list available to them, without the hassle of importing and exporting .regsvr files, or even worse, the brutal process of finding servers on the network manually.
So how does it work?
In SSMS go to your registered server list (via view or Ctrl+Alt+G) and you will see your Local Server Groups and Central Management Servers listing:
If you are new to this and you know no one has done this before, then you will have to set up your registered server first. You can do this by Right Clicking the Central Management Servers and selecting ‘Register Central Management Server’.
In the following dialog add your server:
You should be careful when you are adding this, make sure you do not register one of your production ERP servers as the CMS, choose a small system that all of the DBA group can easily access. In my current environment I am lucky enough to have a dedicated server just as my playground, so I registered this as my CMS (names have been changed to protect the innocent):
Now that you have registered your CMS, you can begin to logically separate your servers into separate groups. You can do this whichever way you choose, be it, geographical location, company division, server version etc. I selected to separate mine by work area as below:
This allows me to split my Lync and Blackberry servers from my ERP systems, from my Share Point servers etc. The order and grouping is entirely personal, just try and not make it too cryptic, remember, we are doing this to make others’ lives easier, not more confusing.
Adding a new group is as simple as right clicking either on the top level CMS or any existing group and selecting ‘New Server Group..’. Same with your actual servers, simply right click on your group and selecting ‘New Server Registration…’.
You will notice that I have sub divided the groups into SQL versions as well, and I do this for a very good reason. One of the best features available with CMS, is the ability to run one query against all of the servers in a group by right clicking at the groups’ top level and selecting ‘New Query’. You can then run any basic admin queries or new alert pushes, whatever you want, against this. So by splitting by SQL version, I do not need to worry about the different syntax and object names between the versions.
There we have it, as long as the CMS persists, anyone new to the company, or the DBA group can immediately have a full list of servers and their addresses available to them with a few clicks.
Thanks for reading.