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

Chiyo OdikaAZURE, SCOM, SYSTEM CENTERLeave a Comment

 

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.

image

 

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.

image

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.

image

 

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.

image

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.

image

image

image

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.

image

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

image

 

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

 

image

 

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.

The following two tabs change content below.
Chiyo's expertise spans multiple platforms. He holds Microsoft Private Cloud, and Server Infrastructure certifications, and avidly enjoys working on deployments of Microsoft's Server and Cloud Platforms, including Microsoft Systems Center, Windows Server, and Microsoft Azure.

Latest posts by Chiyo Odika (see all)

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