Configuring Data Collection and UCP

Introduction

The Configuring Data Collection and UCP module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Configuring the management data warehouse
• Managing System Data Collection Sets
• Working with utility control point

Lab time: It will take approximately 60 minutes to complete this lab.

Exam Objectives

Four exam objectives are covered in this lab.

• Configure data collector
• Enable or disable data collection
• Start or stop a collection set
• Create a SQL Server Utility Control Point

Exercise 1 - Configuring the Management Data Warehouse

The management data warehouse is a relational database. This database contains data collected from the server. Using the collected data, you can generate customized reports and reports for System Data Collection Sets.

In this exercise, you will perform the following tasks:

• Create a management data warehouse using SQL Server Management Studio.
• Start the data collection.

Exercise 2 - Managing System Data Collection Sets

You can configure System Data Collection Sets based on your requirements. The data collector installs the following three System Data Collection Sets in SQL Server 2016:

• Disk Usage: This data collection set collects information about the disk and log usage of all the existing databases on the server.
• Query Statistics: This data collection set collects information about the query plans, statistics, text, and so on.
• Server Activity: This data collection set collects information on all the server related activities such as resource consumption, performance, and so on.

In this exercise, you will perform the following tasks:

• Start or stop a collection set.
• Enable or disable the data collection.

Exercise 3 - Working with Utility Control Point

SQL Server Utility provides a united view of SQL Server related entities. There can be multiple SQL Server Utilities in a facility. Each SQL Server Utility can manage multiple instances of SQL Server with the help of a single utility control point or UCP. The UCP provides an overall picture of SQL Server’s resource health collected from managed instances of SQL Server in SQL Server Utility. In other words, the UCP manages the instances of SQL Server in SQL Server Utility. The UCP collects configuration and performance related information from managed instances every 15 minutes. The collected information is stored in the utility management data warehouse (UMDW). The UMDW filename is sysutility_mdw.

In this exercise, you will perform the following tasks:

• Start SQL Server services.
• Create a utility control point.
• View the SQL Server utility objects in Utility Explorer Content pane.