Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Enroll a Managed Instance into SQL Server Utility in SQL Server 2008 R2

After creating a Utility Control Point (UCP) you will want to enroll some managed instances (up to 25 with SQL Server 2008 R2 Enterprise Edition) into your Utility Control Point. Remember, any managed instances must also be SQL Server 2008 R2 (as of the November 2009 CTP of SQL Server 2008 R2). You also have to have TCP/IP enabled and you must have SQL Agent running with the startup type set to Automatic for each managed instance.

In SQL Server Management Studio (SSMS), go to the View menu, and select “Utility Explorer”. In the left hand tree-view, right-click on Managed Instances, and select “Enroll Instance”. You will see the Introduction screen for the Enroll Instance wizard.

image

Click “Next”, and you will see the “Specify the Instance of SQL Server” screen.

image

Enter the name of the instance you want to enroll, and the click on Connect to enter the credentials needed to connect to that instance. Once this is complete, click “Next”, and you will see the “Utility Collection Set Account” screen.

image

Enter a Windows domain account and password that will be used as the SQL Server Agent proxy account for the utility collection set, and collect “Next”. You will see the the “SQL Server Instance Validation” start, which will take 20-30 seconds to complete.

image

If the instance passes all of the validation tests with no errors (warnings are ok, but you should investigate those), you should click on “Next”. You will see the “Summary of Instance Enrollment” screen.

image

Click on “Next”, and you will see the “Enrollment of SQL Server Instance” screen. It may take a few seconds for this to complete.

image

Once the enrollment process has completed, click on “Finish”. You will see a summary of all of your managed instances (including the UCP itself). You will not see any data from the newly managed instance for at least 10-15 minutes.

image

You can continue to add additional managed instances until you run into the limit of 25 managed instances per UCP. Microsoft estimates that each managed instance will generate roughly 2GB of data per year in the management data warehouse database on the UCP instance.

Comments

Posted by Andy Cudlip on 29 November 2010

When enrolling an instance, the existing collections sets must  be stopped. How do you then see the useful reports that these collection sets provided? Server Activity, Query stats etc. Used to be available from the Management Data Warehouse Overview report.

Leave a Comment

Please register or log in to leave a comment.