Troubleshooting multi-server instances with utility administration
Monitoring and managing multiple instances of SQL Server and databases is a very challenging task. SQL Server tools are easy to use and help to manage multi-server management through automated multi-server jobs, event forwarding and the ability to manage multiple instances from a single-machine console. Using SQL Server 2008 R2, database administrators can define and manage such tasks centrally using Utility Control Point (UCP).
UCP is a model that will represent the organization's SQL Server entities as a unified view. The viewpoints are key sectors to identify which applications use that instance. This can be handled using the SQL Server Management Studio (SSMS) tool. The following entities can be viewed using UCP tool:
- Instance names of SQL Server (multiple)
- Data-tier applications
- Database files
- Resource utilization dimension
- CPU utilization
- Storage space utilization
- Storage volume information
This Utility architecture provides capabilities such as dashboard, viewpoints, and resource utilization policies, which can be classified as utility administration. Using SQL Server 2008 R2 Enterprise Edition UCP tool, we can manage 25 instances.
In this recipe, we will look at how to enroll multiple SQL Server instances and create a central dashboard view as a single-point troubleshooting tool.
Getting ready
Open up the SQL Server Management Studio (SSMS), next to Object Explorer, a new tab Utility Explorer will appear. Similarly, the Utility Explorer can be presented by navigating View—Utility Explorer option. On the right-hand side, towards Object Explorer Details, you will see multiple options that comprise the utility configuration steps (see the next screenshot).
How to do it...
In order to begin enrolling multiple SQL Server instances and create a central dashboard view as a single-point troubleshooting tool, you will need to complete the following steps:
- If you click on the Create a Utility Control Point (UCP), it opens up the Getting Started window that includes shortcuts to wizards that you can use to set up a UCP.
- This acts a single-point of server to manage other multiple servers in a centralized manner.
- The primary step is to specify the SQL Server instance, which will be designated as a control point—UCP to host the central management servers.
- Once the UCP is created, click on Enroll instances of SQL Server with a UCP to enroll additional server instances that will enable us to monitor and manage them efficiently.
- Specify the SQL Server instance that is to be designated as a UCP and that will host the central system management data warehouse.
- This data warehouse will record all the instance's resource utilization and health information. To set up the SQL Server Utility you need to:
- Create a UCP from the SQL Server Utility
- Enroll instances of SQL Server with the UCP
- Define Global and Instance level policies, and manage and monitor the instances
- By default, the UCP instance itself becomes a managed instance. Once the wizard has successfully finished, the process will be directed to the Utility Explorer Content page, as shown in the following screenshot:
- The Utility administration option page (as seen in the next screenshot) can be used to modify or view the global policy settings, which are effective across the SQL Server utility.
- The Utility Administration page is very useful in defining the global policies for Data-tier applications, managed instances, and validation of the resource policy evaluation.
- To enroll the multiple SQL Server instances, using the SSMS Utility Administration pane, right-click on Managed instances and click Add.
How it works...
SQL Server Utility is instrumented to manage instances that are registered with a data collection set, which sends configuration and performance data to UCP every 15 minutes. DAC applications are automatically managed by UCP, if they belong to a managed instance. UCP also supports other monitoring parameters such as database file space utilization, CPU utilization, and storage volume utilization.
Health policies can be defined globally for all data-tier applications and managed instances of SQL Server in the SQL Server Utility, or they can be defined individually for each data-tier application and for each managed instance of SQL Server in the SQL Server Utility.
Note
Using SQL Server 2008 R2 SSMS tool, an SQL Server 2008 instance can be enrolled with a UCP.
The evaluation time period and tolerance for percent violations are both configurable using the Policy tab settings in the Utility Administration node of Utility Explorer. You can also restore default values or discard changes using buttons at the bottom of the display.
The summary and detailed data is presented in Management Studio for each instance of SQL Server and data-tier application, SQL Server Utility dashboard in SSMS presents an at-a-glance summary of performance and configuration data for managed instance and data-tier application CPU utilization, database file utilization, storage volume utilization, and computer CPU utilization. Data displays provide separation of over-utilized and under-utilized resources, as well as graphs of CPU utilization and storage utilization over time. Each instance of SQL Server and data-tier application managed by the SQL Server Utility can be monitored based on global policy definitions, or based on individual policy definitions.