SQL Server 2012 : Central Management Servers

Views 2490

Are you the only Database Administrator in your company or are there a few of you in your DBA Team? How do you keep track of your SQL Server environments? How do you let a new starter or your other colleagues in your DBA team when there is a new SQL Server or when a SQL Server has been decommissioned?

The use of registered servers in SQL Server Management Studio (SSMS) allows you to keep track of the SQL Servers in your environment. However this is for you and you only on your laptop or workstation. Now you can have it setup that the registered servers can be pushed out via group policy to ensure everyone in the team has the same list of servers. To keep this updated you will probably need to raise some change requests etc to get the change put through.

Alternatively you could create a small virtual machine with very little resources and install an instance of SQL Server 2012. In this newly created instance we can configure Central Management Servers (CMS). We do this because we are not able to manage the Central Management Server by itself.

Configuring Central Management Servers

In SSMS we need to view the Registered Servers Explorer. To add this explorer in SSMS Select View | Registered Servers.

To Configure your CMS Right Click on Central Management Servers | Register Central Management Servers. Enter your server details, Click Test to confirm your connection. Click Save.

Windows-Live-Writer-SQL-Server-2012--Central-Management-Serv_462E-image_6

Now that you have created your Central Management Server you are able to create a range of hierarchical groups to make it easier to manage and identify your environment. These hierarchies can be a couple of layers deep. With having set this up you can have everyone in your team access this CMS and everyone is always seeing the same list of servers. The managing of this is easier and everyone in the team sees the changes when they reconnect to the CMS.

Windows-Live-Writer-SQL-Server-2012--Central-Management-Serv_462E-image_10

What can you do with Central Management Servers

With setting up your hierarchies based on versions of SQL Server this will allow you to run queries against all servers registered in each of the versions. Why would you want to separate out running queries against different versions? Well SQL Server 2000 does not have DMV’s, they were introduced in SQL Server 2005. Also some of the DMV’s may have changed between versions with adding or removing columns.

By selecting the Group, you can Right Click | New Query. The query you place in the query panel will then run against all servers in the list returning you the information you require from multiple servers with only having to run a single statement.

Windows-Live-Writer-SQL-Server-2012--Central-Management-Serv_462E-image_12

The first column is always the sever name of the servers that you have registered for the group you are running your query against. A downside to running these queries is that you are unable to perform a sort of the results based on the server names.

If you are utilising the use of Policy-Based Management then having your CMS setup then this makes it very simple for you to ensure all of your environment are meeting the same requirements easily from 1 place. You will be able to quickly send out any new policies or amend any to take into account any change in company standards.

I hope this makes managing your environment easier.

Leave a Reply

Your email address will not be published. Required fields are marked *

Warwick

I am a Microsoft Data Platform MVP as well as a Microsoft Certified Master working as the Principal Consultant here at SQL Masters Consulting. When I am not working with the SQL Server Stack I like to get away to the Snow and spend time Snowboarding.

Search