Microsoft Azure Operational Insights Preview Series – SQL Assessment (Part 7)


During the last couple of months System Center Advisor or as probably will be known as Microsoft Azure Operational Insights Preview after TechEd Europe 2014 has received a lot of improvements and feature so we are now to Part 7. With this blog post I am also renaming all other blog posts. Here is the full list:

In this post we will have a quick look at a new intelligence pack called SQL Assessment:

image

After you add it to you Operational Insight account the following tile:

image

Keep in mind that the first assessment can take up to 4 hours before you see any data. After data is gathered and assessed you will see similar results. Lets click on the tile and see what surprise awaits us:

image

As you can see we have now the well known graphics in Advisor but you will notice a few differences compared to the other packs. While the other packs focus on providing you the information, so you can make easier assessment and decisions this one gives you a straight knowledge.

During my deployments of System Center I know a thing or two about SQL but I do not consider myself expert. This Intelligence Pack not only can show you potential issues/problems but also can give you some knowledge about SQL. The reason why this pack is different because it tries to provide some continuum to the old Configuration Assessment which I didn’t covered in these series. May be I had the feeling that the old Configuration Assessment will probably be converted to something else. BTW I’ve showed this Intelligence Pack to a SQL MVP and received positive feedback.

Now let’s click on graphic or recommendation and see what we will face:

image

As I’ve said you will be faced with a lot of knowledge just like the knowledge in the SCOM Management Packs. You will see that the knowledge has some rating. I guess that rating is a static number for every recommendation that is available in the IP. I couldn’t find if the rating rises up if you more affected objects. The good part about this recommendations are that they go down to database level making the results more granular.

From here clicking on the affected object or on the graphic will lead you to the search query result:

image

image

As you can see there are a lot of properties that are collected to help you make interesting queries query. Here is a list of those provided by the Advisor team:

image

I will show you a little more. I want to see what are the recommendation result statuses  and how many are available for each of them:

Type=SQLAssessmentRecommendation  | measure count() by RecommendationResult

image

Now rather seeing where I’ve failed I want to see where I’ve done good and for which servers:

Type=SQLAssessmentRecommendation IsRollup=false  RecommendationResult=passed

image

As you will see the Intelligence Pack makes even some server checks that are valid for SQL servers.

Let’s see which server passed most checks:

Type=SQLAssessmentRecommendation IsRollup=false  RecommendationResult=passed | Measure count() by Computer

image

Let’s measure by affected object name which could divide results to granular objects like database:

Type=SQLAssessmentRecommendation IsRollup=false  RecommendationResult=passed | Measure count () by AffectedObjectName

image

I am curious what those inconclusive results are:

Type=SQLAssessmentRecommendation IsRollup=false  RecommendationResult=inconclusive

image

It is interesting that there is another status specific to the affected object:

Type=SQLAssessmentRecommendation IsRollup=false  | Measure count() by AffectedObjectResult

image

Some last tips when you use queries:

  • Use RecommendationPeriod in queries to get more accurate results.
  • Use sorting sort RecommendationWeight desc to get more urgent recommendations on top

Definitely interesting Intelligence Pack and I encourage you to try it. Happy searching.

Tip from Daniele Muscetta:

‘inconclusive’ means that check wasn’t able to complete, hence it can’t really tell for sure if you are affected by an issue, or not. it is a way to mitigate false positives. Often this might have to do with permissions required to collect some config point, check the runas account info in the documentation http://technet.microsoft.com/en-us/library/dn818161.aspx (we are updating this doc as there are a few more permissions currently not listed), and/or let me know if any specific check consistently does not work we can investigate – it’s like the previous alert rules – and escalate to the content owner.

I will be using my SQL monitoring account for this:

SQL Advisor Account

Another tip from me if you want to disable Advisor SQL monitoring for certain servers override this rule for them:

override

Advertisements

13 thoughts on “Microsoft Azure Operational Insights Preview Series – SQL Assessment (Part 7)

  1. ‘inconclusive’ means that check wasn’t able to complete, hence it can’t really tell for sure if you are affected by an issue, or not. it is a way to mitigate false positives. Often this might have to do with permissions required to collect some config point, check the runas account info in the documentation http://technet.microsoft.com/en-us/library/dn818161.aspx (we are updating this doc as there are a few more permissions currently not listed), and/or let me know if any specific check consistently does not work we can investigate – it’s like the previous alert rules – and escalate to the content owner.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s