Specify the service account to run the utility collection set.Specify the instance name of SQL Server to enroll.Right-click on Managed Instances and choose Enroll instance (refer to the next screenshot).To implement the utility data collection sets, connect to the Utility Explorer where the UCP is registered.Using SQL Server Management Studio, these are the steps to implement the utility and nonutility data collection sets: It is essential to check that you are using a SQL Server 2008 R2 instance to register the UCP to design the multi-server management feature. Once the instances are enrolled, the default capacity policies of utilization across the instances or applications are set. The new feature of SQL Server 2008 R2-Utility Control Point (UCP)-allows DBAs to set up and collect the utility collection sets. This ensures that the data flow can be executed repeatedly, which enables continuous data collection. The packages collect and cache data until they receive a signal to exit from a loop control-flow task. Cached mode: Data collection and upload are on different schedules.After the packages finish, they are unloaded from memory. The packages start, collect, and upload data at their configured frequency, and run until they are finished. Non-cached mode: Data collection and upload are on the same schedule.The MDW collection set is run in one of the following collection and upload modes: This database can be on the same system as the data collector, or it can be on another computer. The Management Data Warehouse is a relational database that contains all the data that is retained. For this recipe, it is necessary to introduce the MDW feature that stands as a non-utility collection set. Similarly, SQL Server 2008 manages the data collection to monitor CPU, disk, and memory resources of an instance using a Data Collector that is managed by Management Data Warehouse (MDW). SQL Server 2008 R2 introduces the Utility Control Point (UCP) with a set of pre-defined utility collection sets that are managed by UMDW. In this recipe, we will go through the implementation tasks to set up the UCP data collection sets for utility and non-utility categories.
The SQL Server utility collection set is supported side-by-side with Utility collection sets and non-SQL Server utility collection sets. The data is stored in the UMDW database, which is created during the UCP creation. The Utility information data collection set is installed and automatically started on each instance of SQL Server 2008 R2 when you complete the Utility Control Point (UCP) as we have seen in the previous article. Implementing Utility & Non-utility collection sets The reader would benefit by referring to the previous article on Managing the Core Database Engine since the following recipes are related to it. Over 70 practical recipes for administering a high-performance SQL Server 2008 R2 system with this book and eBook Microsoft SQL Server 2008 R2 Administration Cookbook