Continuing Series: System Center for the SQL Server DBA (SCOM Data Warehouse)

Not really a new part to my multi-part series on System Center for the SQL Server DBA … I’m going to post more on the other products in the System Center Suite shortly, but I want to go back to the bread & butter tool for the SQL Server DBA in System Center: Operations Manager. OpsMgr (or SCOM) is the tool where SQL Server DBAs can view alerts, events and overall health monitoring of your entire environment including application & web monitoring, server monitoring as well as databases. With the SQL Server Management Pack add-on to SCOM, you get a large set of SQL Server counters added to your monitoring tool that you can put into dashboards and reports.

Where I want to focus today is the data warehouse feature that ships with SCOM. When events come into the SCOM server from agents, they are logged in both the operational SQL Server database schemas as well as the data warehouse schema. The DW schema keeps history that gives you a wealth of information to report against from recent performance and health to historic reports.

In the box with System Center and the SQL Server MP, you get several reports already built into the catalog for you. To extend those, you use a SQL Server reporting tool like Report Builder against the data warehouse schema. For the current project that I’m working on to extend SCOM for SQL DBAs, I used the new SQL Server 2012 reporting tools with PowerPivot and Power View (see below).

In order for you to build reports from the SCOM DW, you are going to have to poke around at the documentation for the data model, reverse engineer existing RDL reports (which you will find on the SCOM server) and read the reporting guide and blogs at these links:

http://technet.microsoft.com/en-us/library/gg508713.aspx

http://blogs.technet.com/b/momteam/archive/2008/02/26/operations-manager-report-authoring-guide.aspx

Once I had a good enough understanding of the data model, I was able to build a tabular model cube in PowerPivot by connecting directly to the SQL Server DW tables and then stored the cube in SSAS. You don’t need to use the in-memory analytics tools that I show above to report against a cube, but having some sort of a semantic model in tabular, PowerPivot, SSAS UDM, report models, etc. will make the process of building and maintaining SCOM reports much easier. Otherwise, you are relegated to creating on-the-fly joins or using the data model views, which limits you to what is presented. Certainly, in my mind, building a cube will provide much more meaningful business intelligence based on events & alerts that you collect into System Center from your entire SQL Server environment.