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)
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.
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:-
And the following figure is regarding Storage utilization in UCP:-
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.
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.