Enroll an Instance of SQL Server (SQL Server Utility)

Enroll an instance of SQL Server into an existing SQL Server Utility to monitor its performance and configuration as a managed instance of SQL Server. The utility control point (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.

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 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.

• The SQL Server instance to enroll cannot be SQL Azure.

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.

For more information about SQL Server Utility concepts, see SQL Server Utility Features and Tasks.

Wizard Steps

The following sections provide detailed information about each page in the Wizard work flow. Click on a link to navigate to details for a page in the Wizard. For more information about a PowerShell script of this operation, see the PowerShell example.

• Introduction to Enroll Instance Wizard

• Specify the Instance of SQL Server

• Connection Dialog

• Utility Collection Set Account

• SQL Server Instance Validation

• Summary of Instance Enrollment

• Enrolling the Instance of SQL Server

Introduction to Enroll Instance Wizard

To launch the Wizard, expand the Utility Explorer tree on a utility control point, right-click on Managed Instances, and select Add Managed Instance….

To continue, click Next.

Specify the Instance of SQL Server

To select an instance of SQL Server from the connection dialog box, click Connect…. Provide the computer name and the SQL Server instance name in the format ComputerName\InstanceName. For more information, see Connect to Server (Database Engine).

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).

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.

SQL Server Instance Validation

In this release, the following conditions must be true on the instance of SQL Server to be enrolled into the SQL Server Utility:

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 of Instance Enrollment

The summary page lists the information about the instance of SQL Server to add to the SQL Server Utility.

Managed Instance Settings:

• SQL Server Instance Name: ComputerName\InstanceName

• Utility Collection Set Account: DomainName\UserName

To continue, click Next.

Enrolling the Instance of SQL Server

The Enrolling page provides status of the operation:

• Preparing the instance for enrollment.

• Creating the cache directory for the collected data.

• Configuring the utility collection set.

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

To complete the Wizard, click Finish.

After completing the Enroll Instance Wizard, click on the Managed Instances node in the Utility Explorer Navigation pane in SSMS. Enrolled instances of SQL Server are displayed in the list view in Utility Explorer Content pane.

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 one time every 15 minutes. To refresh data, right-click the Managed Instances node the Utility Explorer Navigation pane, then select Refresh, or right-click on the SQL Server instance name in the list view, then select Refresh.

To remove managed instances from the SQL Server Utility, select Managed Instances in the Utility Explorer Navigation 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.

Enroll an Instance of SQL Server using PowerShell

Use the following example to enroll an instance of SQL Server into an existing SQL Server Utility:

> $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]::Connect($SqlStoreConnection);
> $Instance = new-object -Type Microsoft.SqlServer.Management.Smo.Server "ComputerName\ManagedInstanceName";
> $InstanceConnection = new-object -Type Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection $Instance.ConnectionContext.SqlConnectionObject;
> $ManagedInstance = $Utility.EnrollInstance($InstanceConnection, "ProxyAccount", "ProxyPassword");

See Also

Concepts

SQL Server Utility Features and Tasks

Troubleshoot the SQL Server Utility

Other Resources

Monitor Instances of SQL Server in the SQL Server Utility