- Microsoft SQL Server 2008 R2 Administration Cookbook
- Satya Shyam K Jayanty
- 841字
- 2021-04-02 19:33:45
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.
- Creo Parametric 8.0中文版基礎入門一本通
- 中文版Premiere Pro CC實用教程
- 改變思維：菜鳥也能做出震撼PPT（全彩版）
- AI圖像處理：Photoshop+Firefly后期處理技術基礎與實戰
- 中文版CorelDRAW基礎培訓教程
- CAD/CAM技術與應用
- After Effects CS6入門與提高
- Designing and Implementing Linux Firewalls and QoS using netfilter, iproute2, NAT and l7/filter
- Microsoft SQL Azure Enterprise Application Development
- 魔法詞典：AI繪畫關鍵詞圖鑒（Midjourney版）
- Web Host Manager Administration Guide
- 3ds Max 2014/VRay效果圖制作實戰從入門到精通
- SPSS統計分析從入門到精通（第五版）
- Photoshop人像攝影后期處理技法100問
- 邊做邊學：Photoshop CS6 圖像制作案例教程（第3版）（微課版）