SQL Server Farm Management Techniques

The estimated reading time for this post is 1 minutes

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.

 

 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz