SQL Server Farm Management Techniques

Lots of DBAs are using third party software to control and manage their SQL Server farm within their company data center, in this post I would like to highlight few SQL Server features regarding this matter. SQL Server Management Studio is not just an application to run your queries, instead it is a powerful application that can help you to manage and control the SQL Server instances and resources used by them.
SQL Server provides the following features to DBAs for better manageability:-

  • Registered Servers
  • Utility Control Point (UCP)
  • Policy Based Management
  • Multi Server Administration (SQL Server Agent)

Registered Servers:

By using this feature, DBA can run same query in every single registered SQL Server instance from a workstation and retrieve the result set as well, for example: the SQL Server wait stats check script can be run at same time in every registered server without connecting to each instance one by one.

regservers

Utility Control Point (UCP): By using this feature, DBA can monitor resource usage such as CPU, Storage and Database File Utilization by SQL Server instances. For example: DBA can set a threshold for CPU utilization and Data file utilization, if it hits the threshold, then the UCP dashboard indicate the SQL Server instance.

The following figures shows the UCP dashboard:-

ucp3

And the following figure is regarding Storage utilization in UCP:-

ucp6

Policy Based Management: By using this feature, DBA can set few policies and run them on multiple SQL Server instance by using Registered Servers feature and try to rectify the minor issues automatically. For example: there should not be any database with SIMPLE recovery model within the SQL Server farm.

policymanagement

Multi Server Administration (SQL Server Agent): By using this feature, DBA can create a single master Agent Job and deploy it on every registered SQL Server instance from a workstation. For example: DBA can create a script to take transaction log backup and deploy it within the SQL Server farm.

 

 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan