Create a SQL Server Utility Control Point (SQL Server Utility)

  • Article

An enterprise can have multiple SQL Server Utilities, and each SQL Server Utility can manage many instances of SQL Server and data-tier applications. Every SQL Server Utility has one and only one utility control point (UCP). You must create a new UCP for each SQL Server Utility. Each managed instance of SQL Server and every data-tier application is a member of one and only one SQL Server Utility, and is managed by a single UCP.

The UCP collects configuration and performance information from managed instances of SQL Server every 15 minutes. This information is stored in the utility management data warehouse (UMDW) on the UCP; the UMDW file name is sysutility_mdw. SQL Server performance data is compared to policies to help identify resource use bottlenecks and consolidation opportunities.

Before You Begin

Before you create a UCP, review the following requirements and recommendations.

In this release, the UCP and all managed instances of SQL Server must satisfy the following requirements:

  • SQL Server must be version 10.50 or higher.

  • The SQL Server instance type must be Database Engine.

  • The SQL Server Utility must operate within a single Windows domain, or across domains with two-way trust relationships.

  • The SQL Server service accounts on the UCP and all managed instances of SQL Server must have read permission to Users in Active Directory.

In this release, the UCP must satisfy the following requirements:

  • The instance of SQL Server must be a supported edition. For a list of features that are supported by the editions of SQL Server, see Features Supported by the Editions of SQL Server 2012.

  • We recommend that the UCP is hosted by a case-sensitive instance of SQL Server.

Consider the following recommendations for capacity planning on the UCP computer:

  • In a typical scenario, disk space used by the UMDW database (sysutility_mdw) on the UCP is approximately 2 GB per managed instance of SQL Server per year. This estimate can vary depending on the number of database and system objects collected by the managed instance. The UMDW (sysutility_mdw) disk space growth rate is highest during the first two days.

  • In a typical scenario, disk space used by msdb on the UCP is approximately 20 MB per managed instance of SQL Server. Note that this estimate can vary depending on the resource utilization policies and the number of database and system objects collected by the managed instance. In general, disk space usage increases as the number of policy violations increases and the duration of the moving time window for volatile resources increases.

  • Note that removing a managed instance from the UCP will not reduce the disk space used by UCP databases until expiration of data retention periods for the managed instance.

In this release, all managed instances of SQL Server must satisfy the following requirements:

  • We recommend that if the UCP is hosted by a case-insensitive instance of SQL Server, then managed instances of SQL Server should also be case-insensitive.

  • FILESTREAM data are not supported for SQL Server Utility monitoring.

For more information, see Maximum Capacity Specifications for SQL Server and Features Supported by the Editions of SQL Server 2012.

Remove Previous Utility Control Points Before Installing a New One

If you are installing a utility control point (UCP) on an instance of SQL Server that was ever configured as a UCP, you must remove all managed instances of SQL Server and remove the UCP before doing so. You do this by running the sp_sysutility_ucp_remove stored procedure.

Before you run the procedure, note the following requirements:

  • This procedure must be run on a computer that is a UCP.

  • This procedure must be run by a user with sysadmin permissions, the same permissions required to create a UCP.

  • All managed instances of SQL Server must be removed from the UCP. Note that the UCP is a managed instance of SQL Server. For more information, see How to: Remove an Instance of SQL Server from the SQL Server Utility.

Use this procedure to remove a SQL Server UCP from the SQL Server Utility. After the operation is complete, a UCP can be created on the instance of SQL Server again.

Use SQL Server Management Studio to connect to the UCP, then run this script:

EXEC msdb.dbo.sp_sysutility_ucp_remove;

Note

If the instance of SQL Server where the UCP was removed has a non-Utility data collection set, the sysutility_mdw database is not dropped by the procedure. If this is the case, the sysutility_mdw database must be dropped manually before the UCP can be created again.

Each managed instance of SQL Server and every data-tier application is a member of one and only one SQL Server Utility, and is managed by a single UCP. For more information about SQL Server Utility concepts, see SQL Server Utility Features and Tasks.

A UCP is the central reasoning point of the SQL Server Utility. Using the UCP, you can view configuration and performance information collected from managed instances of SQL Server and SQL Server data-tier applications, and perform general capacity planning activities. The UCP is the launch point for enrolling and removing instances of SQL Server from the SQL Server Utility.

After enrolling instances of SQL Server in the SQL Server Utility, you can monitor resource health for managed instances of SQL Server and data-tier applications to identify consolidation opportunities and isolate resource bottlenecks. For more information, see Monitor Instances of SQL Server in the SQL Server Utility.

Important

   The SQL Server Utility collection set is supported side-by-side with non- SQL Server Utility collection sets. That is, a managed instance of SQL Server can be monitored by other collection sets while it is a member of a SQL Server Utility. Note, however, that all collection sets on the managed instance will upload their data to the SQL Server Utility management data warehouse. For more information, see Considerations for Running Utility and non-Utility Collection Sets on the Same Instance of SQL Server and Configure Your Utility Control Point Data Warehouse (SQL Server Utility).

Wizard Steps

Create_UCP

The following sections provide information about each page in the wizard work flow to create a new SQL Server UCP. To launch the wizard to create a new UCP, open the Utility Explorer pane from the View menu in SSMS, then click on the Create_UCP Create UCP button at the top of the Utility Explorer pane.

Click on a link in the list below to navigate to details for a page in the Wizard.

For more information about a PowerShell script of this operation, see the example.

  • Introduction to Create UCP Wizard

  • Specify Instance

  • Connection Dialog

  • Utility Collection Set Account

  • Validation Rules

  • Summary

  • Creating the Utility Control Point

Introduction to Create UCP Wizard

If you open Utility Explorer and there is no connected utility control point, you must connect to one or create a new one.

Connect to existing UCP - If there is already a utility control point in your deployment, you can connect to it by clicking the Connect_to_Utility Connect to Utility button at the top of the Utility Explorer pane. To connect to an existing UCP, you must have administrator credentials or be a member of the Utility Reader role. Note that there can only be one UCP per SQL Server Utility and you can only be connected to one UCP from an instance of SSMS.

Create a new UCP - To create a new utility control point, click the Create_UCP Create UCP button at the top of the Utility Explorer pane. To create a new UCP, you must specify the SQL Server instance name and provide administrator credentials in the connection dialog. Note that there can only be one UCP per SQL Server Utility.

Specify Instance

Specify the following information about the UCP you are creating:

  • Instance Name - To select an instance of SQL Server from the connection dialog, click Connect…. Provide the computer name and the SQL Server instance name in the format ComputerName\InstanceName.

  • Utility Name - Specify a name that will be used to identify the SQL Server Utility on the network.

To continue, click Next.

Connection Dialog

On the Connect to Server dialog box, verify the server type, computer name, and SQL Server instance name information. For more information, see Connect to Server (Database Engine).

Note

If the connection is encrypted, the encrypted connection will be used. If the connection is not encrypted, SQL Server Utility will reconnect using an encrypted connection.

To continue, click Connect….

Utility Collection Set Account

Specify a Windows domain account to run the SQL Server Utility collection set. This account is used as the SQL Server Agent proxy account for the SQL Server Utility collection set. Alternatively, you can use the existing SQL Server Agent service account. To pass validation requirements, use the following guidelines to specify the account.

If you specify the SQL Server Agent service account option:

  • The SQL Server Agent service account must be a Windows domain account that is not a built-in account like LocalSystem, NetworkService, or LocalService.

To continue, click Next.

Validation Rules

In this release of SQL Server, the following conditions must be true on the instance of SQL Server where the UCP will be created:

Validation rule

Corrective action

You must have administrator privileges on the instance of SQL Server where the utility control point will be created.

Log on with an account that has administrator privileges on the instance of SQL Server.

The SQL Server version must be 10.50 or higher.

Specify a different instance of SQL Server to host the UCP.

The instance of SQL Server must be a supported edition. For a list of features that are supported by the editions of SQL Server, see Features Supported by the Editions of SQL Server 2012.

Specify a different instance of SQL Server to host the UCP. 

The instance of SQL Server must not be an instance of SQL Server enrolled with any other SQL Server UCP.

Specify a different instance of SQL Server to host the UCP, or unenroll the instance of SQL Server from the UCP where it is currently a managed instance of SQL Server.

The instance of SQL Server cannot already be host to a utility control point.

Specify a different instance of SQL Server to host the UCP.

The specified instance of SQL Server should have TCP/IP enabled.

Enable TCP/IP for the specified instance of SQL Server.

The instance of SQL Server cannot have a database named "sysutility_mdw."

The create UCP operation will create a utility management data warehouse (UMDW) named "sysutility_mdw." The operation requires that the name does not exist on the computer at the time that validation rules are run. To continue, you must remove or rename any database named "sysutility_mdw." For more information about renaming operations, see ALTER DATABASE (Transact-SQL).

Collection sets on the specified instance of SQL Server must be stopped.

Stop pre-existing collection sets while the UCP is created on the specified instance of SQL Server. If the data collector is disabled, enable it, stop any running collection sets, then re-run validation rules for the Create UCP operation.

To enable the data collector:

In Object Explorer, expand the Management node.

Right-click Data Collection, and then click Enable Data Collection.

To stop a collection set:

In Object Explorer, expand the Management node, expand Data Collection, and then expand System Data Collection Sets.

Right-click the collection set that you want to stop, and then click Stop Data Collection Set.

A message box will display the result of this action, and a red circle on the icon for the collection set indicates that the collection set has stopped.

The SQL Server Agent service on the specified instance must be started. If the specified instance of SQL Server is a SQL Server failover cluster instance, the SQL Server Agent service must be configured to start manually. Otherwise, the SQL Server Agent service must be configured to start automatically.

Start the SQL Server Agent service. If the specified instance of SQL Server is a SQL Server failover cluster instance, configure the SQL Server Agent service to start manually. Otherwise, configure the SQL Server Agent service to start automatically.

WMI must be configured correctly.

To troubleshoot WMI configuration, see Troubleshoot the SQL Server Utility.

The SQL Server Agent proxy account cannot be a built-in account, like Network Service.

If the SQL Server Agent proxy account is a built-in account, like Network Service, re-assign the account to a Windows domain account that is sysadmin.

If you select the proxy account option, the SQL Server Agent proxy account must be a valid Windows domain account.

Specify a valid Windows domain account. To ensure that the account is valid, logon to the specified instance of SQL Server using the Windows domain account.

If you select the service account option, the SQL Server Agent service account cannot be a built-in account, like Network Service.

If the SQL Server Agent service account is a built-in account, like Network Service, re-assign the account to a Windows domain account.

If you select the service account option, the SQL Server Agent service account must be a valid Windows domain account.

Specify a valid Windows domain account. To ensure that the account is valid, logon to the specified instance of SQL Server using the Windows domain account.

If there are failed conditions in the validation results, correct the blocking issues and then click Rerun Validation to verify the computer configuration.

To save the validation report, click Save Report then specify a location for the file.

To continue, click Next.

Summary

The summary page displays the information that you provided about the UCP:

  • The SQL Server instance name that hosts the UCP.

  • The name of the SQL Server Utility.

  • The name of the account that will be used to run jobs for SQL Server Utility data collection.

To change UCP configuration settings, click Previous. To continue, click Next.

Creating the Utility Control Point

During the operation to create the UCP, the wizard will display the steps and provide status:

  • Preparing the SQL Server instance for UCP creation.

  • Creating the utility management data warehouse (UMDW).

  • Initializing the SQL Server UMDW; the UMDW file name is sysutility_mdw.

  • Configuring the UCP.

  • Configuring the SQL Server Utility collection set.

To save a report about the create UCP operation, click Save Report then specify a location for the file.

To complete the wizard, click Finish.

After completing the Create UCP Wizard, the Utility Explorer navigation pane in SSMS displays a node for the UCP with nodes under it for Deployed Data-tier Applications, Managed Instances, and Utility Administration. The UCP automatically becomes a managed instance.

The data collection process begins immediately, but it can take up to 30 minutes for data to first appear in the dashboard and viewpoints in the Utility Explorer content pane. Data collection continues once every 15 minutes. Initial data will be from the UCP itself. That is, the UCP is the first managed instance of SQL Server in the SQL Server Utility.

To display the dashboard, click View then select Utility Explorer Content from the SSMS menu. To refresh data, right-click the utility name in the Utility Explorer pane, then select Refresh.

For more information about how to enroll additional instances of SQL Server into the SQL Server Utility, see Enroll an Instance of SQL Server (SQL Server Utility). To remove the UCP as a managed instance from the SQL Server Utility, select Managed Instances in the Utility Explorer pane to populate the list view of managed instances, right-click on the SQL Server instance name in the Utility Explorer Content list view, then select Make Instance Unmanaged.

Create a New Utility Control Point Using PowerShell

Use the following example to create a new utility control point:

> $UtilityInstance = new-object –Type Microsoft.SqlServer.Management.Smo.Server "ComputerName\UCP-Name";
> $SqlStoreConnection = new-object -Type Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection $UtilityInstance.ConnectionContext.SqlConnectionObject;
> $Utility = [Microsoft.SqlServer.Management.Utility.Utility]::CreateUtility("Utility", $SqlStoreConnection, "ProxyAccount", "ProxyAccountPassword");

See Also

Concepts

SQL Server Utility Features and Tasks

Troubleshoot the SQL Server Utility