Update: Based on Bill Ramos’ comment below and a note on Twitter (thanks!!) I have added some more details which hopefully make the situation clearer.
When setting up data collection using the GUI, configuration failed halfway through setup. At the point of failure I found there were SQL Agent jobs for collections visible, but nothing appearing in Management Studio under Management\Data Collection. The Data Collection icon still had the ‘off’ down red arrow. (I manually refreshed it for good measure to make sure.)
Beware: this isn’t an officially endorsed workaround. It’s just how I found I could make it work. I am testing this on a server which isn’t in production use.
I was configuring the data collector on a SQL 2008 R2 instance (clustered), and pointing to a SQL 2008 (NOT R2) Management Data Warehouse. The MDW is on another server of course– that one isn’t clustered (although I don’t think it matters). Since the MDW server isn’t R2 it doesn’t (and can’t) have a Utility Control Point installed, but when you set up data collection on an R2 target, it automatically includes some of the collection sets for the UCP. That’s fine, they should just stay off when they don’t communicate with a UCP.
In the past I have successfully configured Data Collection at least 10 times without seeing this issue, however at that point my Data Collection targets and my Management Data Warehouse were all on SQL Server 2008. This is the first time I have configured data collection on a SQL Server 2008 R2 target using a MDW which is SQL Server 2008. Basically, I am bringing this R2 box up for performance testing and burn-in now, and later on will be doing a separate change to bring the MDW up to SQL Server 2008 R2.
The Point of Failure
Setting up data collection with the GUI failed half way through. Everything was fine walking through the GUI, but when I told it to set up collection it failed halfway through the setup process and could not complete.
The failure report showed the following error [excerpted]:
Unable to start collection set Utility Information.
Caught error#: 14693, Level: 16, State: 1, in Procedure: sp_syscollector_start_collection_set, Line: 108, with Message: A collection set cannot start without a schedule. Specify a schedule for the collection set. (Microsoft SQL Server, Error: 14684)
Looking at the system configuration, jobs had been created in the SQL agent for data collection, but no system collections were visible under the Data Collector in management studio.
At this point I was not able to uninstall and reinstall– currently Data Collection can be disabled, but it can’t be uninstalled.