SQL Server Central Management Server (CMS)
Microsoft always comes up with some really interesting features in its every release. Central Management Server popularly known as CMS was one such interesting and useful feature which was introduced in SQL Server 2008. So what is Central Management Server, is this a New Server, New license required, a new product or what? Answer is no.
Is just a pretty plain simple feature like a Registered Server in earlier versions which is shipped with SQL Server software. CMS is nothing more but a Plain simple Central Repository kind of thing which holds in all the servers which you add in. You can create Groups such as DEV, TEST, ACC, PRD, WHITEBOX, BLACKBOX etc and add the respective servers in the respective group and manage them Centrally. It will also serve you as an SQL Server Inventory of your organisation and there is no need to Maintain it seperately. All you need to do to get list is Register this Central Management on you local Machine in Management Studio and Enjoy all the features.
Some of the important use of CMS are
- You can Store all of your policies under this one CMS and then apply/Evalute these policies at once on all servers or specific groups under CMS.
- Run a single Query against multiple Servers at a time and get the results in single Output Window
- You can Import or Export all the servers or groups to different CMS or Different server at ease simply by Right Clicking, going to Tasks and then select Import or Export as shown below.
- Manage all of your SQL Server Remotely from one CMS, No need to Connect to Each Instance, or RDP to Machine and Manage services. You can simple right click on Server and go to Service Control and then Stop, Restart or Start the SQL Services as shown below.
- Couple of things to keep in Mind is that, it works only with Windows Authentication and Central Management Server cannot be a member of a group that it maintains.
How to configure a Central Management Server ?? You can find information in this link: How to Configure Central Management Server in SQL Server