如何安装SQL Server实用程序控制点并配置多个实例进行监视
As we progress in our career as a SQL Server Database Administrators we encounter situations where managing and monitoring a large number of SQL Servers is the daily job requirement. Monitoring SQL Server is a very vast and detailed area of study. Out of all the monitoring the very basis and critical monitoring in the Resource Utilization Monitoring for SQL Server.
随着我们作为SQL Server数据库管理员的职业发展，我们遇到了日常工作中需要管理和监视大量SQL Server的情况。 监视SQL Server是一个非常详尽的研究领域。 在所有监视中，SQL Server资源利用监视中的基础监视和关键监视。
Keeping an eye on the SQL Server Resources might not look like a big task but it becomes near to impossible if you have 100s of instances and you need a historical analysis of the resources while not spending huge amount of money on third party tools. Also, wherever we have high volume of instances and computers to monitor we always want to have a Dashboard to have a summarized holistic view of the servers being monitored. All of the above requirements are really simple yet critical and are easily fulfilled by the SQL Server rarely used feature known as “SQL Server Utility”.
密切关注SQL Server资源可能看起来不是一项艰巨的任务，但是如果您有100个实例，并且需要对资源进行历史分析，而又不花大量金钱在第三方工具上，那么这几乎是不可能的。 另外，无论我们在要监视大量实例和计算机的任何地方，我们始终希望拥有一个“仪表板”以对被监视的服务器有一个整体的整体视图。 以上所有要求确实很简单但很关键，并且可以通过很少使用SQL Server功能（称为“ SQL Server Utility”）轻松实现。
In this article I will let you know the benefits, usage and the way to install the Utility Control Point for multiple instances. This article does not cover each and every detail of the said feature but lists out most important features and critically important that “how to install” the Utility Control Point and configure multiple instances for monitoring.
在本文中，我将让您知道为多个实例安装实用程序控制点的好处，用法和方式。 本文并未涵盖上述功能的每一个细节，但列出了最重要的功能，并且对于“如何安装”实用程序控制点和配置多个实例进行监视至关重要。
重要性和效用 (Importance and Utility)
SQL Server Utility Control Point is a feature which is easily configurable and will give you the ability to view the historical information about the SQL Server Storage Information Categorized by the Logical Drives as well as by the Databases, SQL Server CPU Instance Utilization and the Computer/Machine level CPU utilization. The detailed information becomes critically important when you can view the historical records by day/week or month. The summary is useful when you want to review all the instances at a glance and wants to have a quick view about any alerts for any instances at CPU/Storage level.
SQL Server实用程序控制点是一项易于配置的功能，它将使您能够查看有关由逻辑驱动器以及数据库，SQL Server CPU实例利用率和计算机/服务器分类SQL Server存储信息的历史信息。机器级别的CPU利用率。 当您可以按日/周或月查看历史记录时，详细信息变得至关重要。 当您希望一目了然地查看所有实例并希望快速查看有关CPU /存储级别上任何实例的任何警报时，摘要非常有用。
SQL Server Utility Control Point needs to be installed on an Instance first which will host the UCP Repository Database containing all the historical data about the enrolled instances. The database is named as “sysutility_mdw”. If you want to have a description of the name of the said database, it will be like “System Utility Management Data Warehouse”.
首先需要在实例上安装SQL Server实用程序控制点，该实例将承载包含有关已注册实例的所有历史数据的UCP存储库数据库。 该数据库名为“ sysutility_mdw”。 如果要描述所述数据库的名称，它将类似于“系统实用程序管理数据仓库”。
The SQL Server Utility Control Point is a single instance data warehouse where all enrolled instance related data will be available and the dashboard will display data from this UCP. Also, the installation of the UCP will create multiple jobs on the UCP Instance which will collect Resource Utilization data after a specified interval (normally 15 minutes). When you have installed a single UCP now you are ready for enrolling the Instances which you need to monitor and you can add all the instances one by one and it will start storing data for all those instances and will display the information after 15 minutes of enrollment.
SQL Server实用程序控制点是一个单实例数据仓库，所有已注册的实例相关数据都将可用，并且仪表板将显示来自此UCP的数据。 另外，安装UCP还会在UCP实例上创建多个作业，这些作业将在指定的时间间隔（通常为15分钟）后收集资源利用率数据。 现在，当您安装了一个UCP之后，就可以注册需要监视的实例了，您可以一个一个地添加所有实例，它将开始存储所有这些实例的数据，并在注册15分钟后显示信息。 。
So, after the summary let us get started about the detailed installation guide about how to Install SQL Server Utility Control Point and enrolling SQL Server Managed Instances.
因此，在总结之后，让我们开始了解有关如何安装SQL Server实用程序控制点和注册SQL Server托管实例的详细安装指南。
实用程序控制点安装分步指南 (Step by Step guide for Utility Control Point Installation )
-
To start eh installation, in the SQL Server Management Studio “View” menu you will see the option for “Utility Explorer”, click it and it will open up the splash screen.
-
Click on “Create a Utility Control Point”, it will open up the Wizard for installing the UCP and the MDWD (Management Data Warehouse).
-
Click Next, and it will open up the next window in which you can connect to the instance on which you need to install the UCP and mention a name for your Utility Control Point like “ProdUCP” or whatever naming convention you feel it would be suitable for your organization. And click “Next”.
ProdUCP ”，或者您认为合适的命名约定为您的组织。 然后点击“下一步”。
-
In this windows, you can enter a domain account which will be used to run the collection set. This account is usually the account which is running the SQL Server Agent. You can either mentioned a valid domain account or use the SQL Server Agent Account. Enter the details and click “Next”.
-
The wizard will run a validation which must pass. The most common errors are described below with solutions:
The above mentioned error will be thrown if you have SQL Server Agent disabled. The SQL Server Agent must be enabled and in Automatic State to pass the validation. So enable it and make it automatic. In case of the Clustered Instance the manual state will be passed as well.
The above-mentioned error is thrown if you have mentioned an account which is not authorized like using a Local Account or Network Service. It’s always a good approach to create a separate domain account for SQL Server Services and mention that account credentials in the wizard at the start.
如果您禁用了SQL Server代理，则会引发上述错误。 必须启用SQL Server代理并使其处于“自动”状态才能通过验证。 因此，启用它并使其自动。 如果是群集实例，则手动状态也将被传递。
如果您提到一个未经授权的帐户（如使用本地帐户或网络服务），则会引发上述错误。 为SQL Server服务创建一个单独的域帐户并在开始时在向导中提及该帐户凭据始终是一种好方法。
-
After fixing all the errors (if found any) click the Re-Run Validation and if this is passed you will be presented with the screen mentioned below, for a summary to review. This means you are all set for the installation so click “Next” to start the installation.
-
After finishing the installation, you can go ahead and connect to the newly installed Utility Control Point on your instance by going to Utility Explorer.
-
When you connect to the UCP you will see a Dashboard with all the fancy graphs and it will show you only ONE instance which is currently being monitored. This instance is the one on which the UCP is installed. Now you can enroll more instances to monitor.
-
To enroll an instance, click on the “Managed Instances” on the left-hand side in the explorer pan and it will display the managed instances details on the right-hand side. The default view will show you the CPU utilization of the SQL Server Instance “Selected” in the Managed Instance Pan. Now you can view the Resource Utilization and change the interval on the left-hand side from a day (default) to week/month or year.
-
Right click on the black area in the pan under the manager instances and click on “Enroll Instance”. You will have to go through the similar steps which you have seen above while installing the UCP to enroll the new instance.
NOTE: Please note that currently you will only see one instance and it will show you icons (Instance CPU, Computer CPU, File Space & Volume Space) in gray colors as its still collecting the information and it will display the results after the first cycle has completed the data collection which is fifteen minutes by default.
注意：请注意，当前您只会看到一个实例，并且它将以灰色显示图标（实例CPU，计算机CPU，文件空间和卷空间），因为它仍在收集信息，并且在第一个周期后将显示结果已完成默认为十五分钟的数据收集。
-
The second view is Storage Utilization which can be viewed by day, week, month or year interval and can be group by Database or the Volume.
-
Mentioned below are the Policies you need to view and configure if you need to change the threshold for the UCP. Generally, the default policies are good enough but you can review and change the policies as per your criteria in your organization.
-
Mentioned below policies are for the Volatile Resource Utilization and also you can set the frequency of how you want
To conclude this article, the discussed feature is one of the very rarely used feature but has a lot of power to give you good power of doing historical analysis about the Resource Utilization. Now you can Instance the UCP in your environment and enroll multiple instances.
综上所述，所讨论的功能是很少使用的功能之一，但是具有强大的功能，可以为您提供有关资源利用率的历史分析的强大功能。 现在，您可以在您的环境中实例化UCP并注册多个实例。