New Intelligence Pack for SQL Server Assessment is Available in Azure Operational Insights

Adding to the robust set of features and assessment scenarios that System Center Advisor provides by means of Intelligence packs for capacity planning, configuration assessment, security, system update, malware assessment, log management and change tracking, the Advisor team at Microsoft has released a new Intelligence Pack for SQL Server assessment. This marks an evolution in the configuration assessment scenarios that Advisor typically provides by, in this case, providing assessment information for a specific workload/ technology – SQL Server. I imagine that this is the first of many such workload/ technology specific Intelligence packs.

Let’s take a tour and see what this Intelligence Pack entails.

The intelligence pack provides deep operational insight into various key focus areas as shown below. Security and compliance, Availability and business continuity, performance and scalability, upgrade, migration and deployment, and operations and monitoring, among others. I enabled this intelligence pack and let it aggregate data over a couple of hours. Let’s take a peek into each focus area to see what insight is provided.

The security and compliance insight shows detailed results of various best-practices checks that are run against your workloads, and provides recommendations on how to address configurations that fall short of the best-practices recommendations.

Clicking on the security and compliance tile takes you to the search field where you can view search results, and modify your queries to obtain more relevant result sets. Typing in the query below returns the results seen in the following figure.

Type:SQLAssessmentRecommendation IsRollup=true RecommendationPeriod=2014-10 FocusArea=”Security and Compliance” RecommendationResult=Failed | sort RecommendationWeight desc

More on this below.

From the SQL Assessment overview page, clicking on the Availability and business Continuity option takes you into the specific SQL Focus area for availability and business continuity. As seen below you’ll get a quick overview of the risk and health profile of your environment, with weighted and prioritized recommendations on how to remediate the identified issues. See the figures for the various focus areas views below.

Note that you can interact with the various results on the assessment of your environment by clicking on the tiles within the various focus areas or specific links to flagged objects as seen below.

In the search result field, you can interact with your search results by selecting various timeframes, reviewing historical search results, and drilling further into the returned results. The query below returns SQL Assessment Intelligence pack specific information for checks that were failed by a specific database object in [Server] \Management Group; Object Name.

IsRollup=false RecommendationPeriod=”2014-10″ AffectedObjectName=”ServerFQDN\Management Group Name;ReportServerTempDB” AffectedObjectResult=Failed | sort RecommendationWeight desc

If for instance I’d like to determine what recommendations were actually passed by reviewed objects across all focus areas, I could run a query as such:

Type:SQLAssessmentRecommendation IsRollup=true RecommendationPeriod=2014-10 FocusArea=* RecommendationResult=Passed | sort RecommendationWeight desc

In sum, this intelligence pack will enable you to quickly review the health and risk profile of your SQL Server environment, easily and expeditiously take action to remediate identified issues, and enable you to be more productive. I look forward to more of such assessment scenarios in Advisor.