This week's CMS tips 1

With CMS (Central Management System) we can easily keep track of and easily manage a large number of instances, we will post some tips but we will start by explaining what CMS is. CMS is a central repository where we can build a folder structure with our instances.

We can then run queries against all instances or a specific instance, run policies, connect against or simply find a single instance, it is stored centrally on a SQL instance, the so called CMS instance, so all DBAs reach the same structure. CMS is useful when you have a number of instances that you connect to within the same domain because unfortunately you can't use different logins, if you need to connect with different logins you can instead of CMS use "Local Server Groups", it offers similar functionality but if there are several DBAs we have to distribute the RegSrvr.xml file from the profile folder where the "Local Server Groups" are stored, for example they are retrieved via loginscript from a share itself.

Getting started with CMS
You need to use an instance to save folders and instances in the CMS, this is saved in the MSDB in the sysmanagement tables. Keep in mind that you cannot include the CMS instance in the folder structure, so please use a management/utility instance that is not otherwise used in production.

• I Management Studio, Click View, Registered Servers
• Right-click on "Central Management Server" and select Register Central Management Server
• In New Server Registration, specify the instance where the CMS should be saved and click Save

The CMS instance will then appear, right-click and select "New Server Group" to create a new folder and "New Server Registration" to add a new instance. The folder structure can consist of geographical regions, type of instances, SQL version etc. If it is common to have more instances / server, you can create a folder for each server and then add instances under the folder

Each DBA must repeat steps 1-3 in their Management Studio for the first time, then the CMS is displayed each time
It is now possible to connect to individual instances, group of instances or run queries on many instances which we will look at more in future blog posts

Tips!
It is possible to use different colours for instances saved in CMS/Local Server Groups, this is visible when running a query against the instance in the status line at the bottom. For example, you can give all production instances a red color.