Setting up Central Management Server in SQL Server

  • Article

Hey SQL Fans,

Today I thought I would write a quick post about Central Management Server for SQL Server. I have been to so many customers who are not making use of this amazng a free feature that ships with SQL Server. I always ask the resident DBA team at a customer,  how they are managing their SQL instances and whether they are using CMS. Surprisingly, they have either never heard about it, or they have, but cannot find a use for it?

I personally feel that CMS should be a part a foundation to your SQL Server Eco System. It makes the life of a DBA so much easier when it comes to your day to day administration, as well as additional free SQL Server features you would like to use, i.e. Policy Based Management, Management Data Warehouse, Multi-Server Queries and Multi Server Administration etc.

Today, I would like to show you how to set this wondeful feature up, how to register your SQL instances within CMS, and set up your master and target SQL agents.

Configuring CMS

Click on View -> Registered Servers.

Registered Servers Navigate

 

 

 

 

 

 

 

 

You will be presented with a view of your local instances and registered servers:
Registered Servers View

 

 

 

 

 

 

 

 

Right click on Central Management Servers -> Register Central Management Server

New CMS

You will be presented with a window to enter a Database Server that will act as the Central Management Server. If you have a dedicated DBA server, this woul d be ideal

New CMS Registration

 

 

 

 

 

 

 

Once this information has been saved, you will see a CMS server registered

CMS Registered

You can now create groups under your CMS to manage different areas of your SQL Eco system.

New Server Group

Below, I have included an example of how you could group your SQL instances, based on how you manage your environment:

NOTE: When creating your various SQL groups, think of the multi-server queries you would run against these groups. A multi-server query is a query you can run against a group of servers. The query will return results from each server registered in a specific group.

New Server Group Example (2)

Well done :-) you have now created a CMS for your SQL environment. This is a great foundation for your SQL environment within your organization. CMS will allow you to run queries across SQL group. CMS will help you find information a lot quicker, as well as help with maintenance queries etc, eg. running DMV queries against CMS groups to find backup information, index usage, query execution information etc.

Next, I would like to show you how to configure the SQL Agent of your CMS for Multi-Server SQL Server Administration.

 

I hope you guys enjoyed this quick post on CMS and that you start making the relevant changes within your SQL eco system.

 

Cheers