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.
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.
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.
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