SQL SERVER – Tools for Proactive DBAs – Central Management Server – Notes from the Field #009

[Note from Pinal]: This is a ninth episode of Notes from the Fields series. If you are DBA and Developer there is always a situation when you want to execute the same query on multiple servers. This can work out very well if you have a servers in single digit, however, there are cases when we have hundreds of the server and each require multiple queries to be executed. In this blog post John explains how we can achieve this goal with the help of CMS.

SQL SERVER - Tools for Proactive DBAs - Central Management Server - Notes from the Field #009 JohnSterrett

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces in their career – how you perform a performance related root cause analysis. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


One of the mistakes I see repeated in the field is not leveraging included features inside of SQL Server. Today, we’re going to talk about one of my favorite tools to help DBA’s be proactive. This tool is Central Management Server “CMS.” In a nutshell, the central management server allows you to run queries or policies against a group of servers. CMS is included in standard and enterprise edition and utilizes an msdb database to configure servers and groups of servers. CMS requires windows authentication and the windows users account connected to CMS is authenticated against all the instances in a group when you execute queries or policies.

In todays, example we will look at general database properties across many servers in a production group.

In order to take advantage of CMS you will need to enable a CMS server. Typically, I would use a non-production server for this task. To start click on view in the menu bar and select Registered Servers.

SQL SERVER - Tools for Proactive DBAs - Central Management Server - Notes from the Field #009 nftf9-1

Once you have the Registered Servers window open you can right click on the Central Management Server to create your CMS.

SQL SERVER - Tools for Proactive DBAs - Central Management Server - Notes from the Field #009 nftf9-2

In this example we are using a server named “PBMDEMO” as our central management server. Once the CMS is configured you can create folders to group servers. In this example we have a Development group and a Production group.

SQL SERVER - Tools for Proactive DBAs - Central Management Server - Notes from the Field #009 nftf9-3

Now that you have imported your instances into your groups you can right click on a group and say new query. This will create a connection to each instance using your windows authenticated account.

SQL SERVER - Tools for Proactive DBAs - Central Management Server - Notes from the Field #009 nftf9-4

Now you can type a query. In this example we are using “Select * from dbo.sysdatabases.” You will see that it fired on each instance and returned the results into a single result set.

SQL SERVER - Tools for Proactive DBAs - Central Management Server - Notes from the Field #009 nftf9-5
In a future tip from the field we will cover using Policy-Based Management with a Central Management Server. In the meantime, you can watch a video showing you how to monitor missing backups or check out my several blog posts on Central Management Server.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

Notes from the Field, SQL Backup and Restore
Previous Post
SQLAuthority News – Happy New Year 2014 – Resolution of the New Years
Next Post
Developers – Seven Foods Developers Love

Related Posts

3 Comments. Leave new

Leave a Reply