New SQL Server Management Pack: Update 6.6.3.0 & Instance View

This week Microsoft released a new 6.6.3.0 version of SQL Server Management Pack (MP) for System Center Operations Manager (SCOM). The following MPs were updated:

SQL Server MPs for SQL Server 2005/2008/2012/2014
SQL Server Replication MPs for SQL 2008/2012/2014

Below is a list of the most recent changes that were implemented since the last release mainly because of installation issues on some environments:

Implemented batching to all data aggregation mechanisms to ensure low temp database space and log space usage.
Implemented a control bit to divide group from non-group references to save space in aggregated data storage.
Fixed error message in SQL DB Discovery script that erroneously stated “… SQL Agent Job discovery script … failed”.

Notes on these fixes: On some environments, there was an issue with temp db growth and high log usage in v6.6.2.0. We’ve reviewed our approach to work with such environments and implemented batching and protection from the race situation. Log space and temp db usage is now under 500 mb for all the processing period, usually under 100 mb. Also, timeouts are not a problem anymore, because batches are processed and committed one by one. Hanging batches will be reset to new and reprocessed if they were started more than 3 minutes ago. Please note that on large environments initial data collection can take significant time. You can check the installation progress and result using these queries:

select * from sdk.Microsoft_SQLServer_Visualization_Library_DB_Version; -- this should be 7
select count(*) -- this should be 0, but it could be 1-4 if something new is being processed right now
from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches tb
where FinishDate is null;

If you still have an issue with installation, please collect statistics using the following queries and open an issue on the following site with diagnostic info.

select t.[Type], count(tb.BatchId) as Count, avg(DATEDIFF(ms, StartDate, FinishDate)) as AverageTime,sum(DATEDIFF(ms, StartDate, FinishDate)) as TotalTime
from sdk.Microsoft_SQLServer_Visualization_Library_Table_Batches tb inner joinsdk.Microsoft_SQLServer_Visualization_Library_Tables t on t.TableId = tb.TableId
group by t.[Type];
select 'Perf' as Name, count(*) as [Count]
from sdk.Microsoft_SQLServer_Visualization_Library_LastPerfValues
UNION ALL
select 'State', count(*)
from sdk.Microsoft_SQLServer_Visualization_Library_LastMonitorValues
UNION ALL
select 'Alert', count(*)
from sdk.Microsoft_SQLServer_Visualization_Library_LastAlertValues
UNION ALL
select 'RelationshipTypes', count(*)
from sdk.Microsoft_SQLServer_Visualization_Library_RelationshipType_Hierarchy
UNION ALL
select 'Relationships', count(*)
from sdk.Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy;

In this post, I’ll continue overview of the Management Pack with Dashboards functionality on the Instance Level dashboard, because, as you can see, there is only one major fix and there are no new features in 6.6.3.0 MP version. I am also going to repeat the performance test on the 6.6.3.0 version to check if the batching implementation has affected the performance and share the results in one of the next posts.

So what’s new in the Instance View?

You can drill down to the Instance View by a double click on a tile from Datacenter Summary dashboard to investigate the root cause of the issue: The Instance View tiles display information about the current state of the monitors and the latest performance data. Depending on the current state and the configuration, the tiles will have different background colors and layout. Refer to the Dashboard guide – it has a detailed explanation of the tiles functionality and colors. You can also drill down to Instance View dashboard of the related objects by selecting an object from the left panel and double-clicking “Related Objects State” widget. If there are no related objects, “Related Objects State” widget is not visible at all. This is a change from earlier versions. The new “Show alerts from all levels” function allows viewing the list of alerts in the selected object. To activate “Show alerts” function, check the corresponding checkbox, and if there are alerts on any related object, they will be shown. Another new feature is “Copy” buttons. Now you can copy all property values or individual property value on mouse-over: The navigation pane is within reach, smart and simple – click the name of the location in the path, and you will be there. “Back” button (looks like a left arrow in a circle) is also available. This is a typical breadcrumbs implementation.

Create custom dashboard from scratch.

Obviously, you can not only customize the predefined dashboards, but also create your own customized dashboards: just go to “Monitoring” tab and select the unsigned user folder for the new dashboard. Most folders are protected (signed) and cannot be used for custom dashboards. If you do not have any user folders, just start by creating your own empty Management Pack. Right-click the folder, select New –> Dashboard View, and then select Datacenter Dashboard template. An empty dashboard will be created: All you have to do now is select a group to target; it can be any group – for example, All Windows Computers: Click “Add” and you are done! The new Group is displayed on the dashboard: Like us on Facebook and follow us on Twitter!