SQL Named Instance Monitoring

A far simpler method would be to use AppInsight for SQL.

I agree and we do use AppInsight; however, I have run into at least two cases where the system has too many databases and it causes AppInsight to both have and cause more problems than it solves.  I actually spent weeks working with support on this only to come to the conclusion that there is a point at which a DB has too many databases for AppInsight to function properly.

In theory you should be able to mitigate this by increasing the timeout for the assigned application, as well as a few select components, such as "Database Files", "Top Active Sessions with Statistics", and "Top Tables for Database"

You can also disable certain components, like the "Top Indexes for Database", "SQL Agent Job Info", "SQL Server Events", and Top Expensive Queries" to further improve performance and reduce issues caused by having too many databases on a given SQL server.

At this point I am just looking for a good solid way to monitor SQL Named instances that doesn't require AppInsight.  As a service provider I don't want to use AppInsight for every client that has SQL as that will put a considerable burden on Orion from what I have already seen.  I would like to have a solid way to monitor SQL performance and availability that includes named instances that isn't AppInsight and then we will only use AppInsight when deep dive analytic data is necessary.

Oh we tried all of that.  My issue was escalated up and I spent weeks trying to get it to work.  We changed timeouts, disabled the heavier components and looked through bunches of diag logs.  The database in question had over 700 databases running on it.

Wow, I would imagine the polling interval would also need to be adjusted to accommodate that large of an instance as well since 5 minute intervals are probably too aggressive to query so many individual databases. It's likely the polling never completes before the next poll begins.

To that end I would recommend using the application templates below, assign them individually to each named SQL instance. For multi-instance SQL servers multiple templates may be applied.

SQL Server 2005-2008 Performance (SQL)

SQL Server 2012 Performance (SQL)

Once the template is applied to the node, edit the assigned template, select all components and click "Multi-edit".From the "Edit Multiple Component Monitors" pop-up check the box next to "SQL Server Instance" and populate the name of the SQL Instance you wish to monitor. This should save you from needing to edit each component individually. A real time saver! 

Thanks for that, exactly the level of detail I was looking for! 

aLTeReGo‌ one more question comes to mind here:

How many of the components in the SQL Server User Experience monitors apply to specific instances versus the database environment overall?

All components relate to the SQL Instance. Microsoft does not have a notion of a "SQL Server". It's broken up into Instances. Either the default, unnamed instance, or specifically named SQL instances. An "Instance" is nothing more than a re-install of MSSQL under a different directory path than any others running on the same box.