Best practices assessment -Arc-enabled SQL Server

If you are a Database Administrator (DBA) and responsible for managing, securing, protecting, and governing hundreds of SQL Servers, you are not alone, we are here to simplify these tasks using Azure Arc-enabled SQL Server. Learn more.

In this blog, I will introduce you to the Best practices assessment (BPA) feature powered by Azure Arc-enabled SQL Server. This feature allows you to proactively diagnose potential issues with your SQL Server environment running on-premises and in multi-cloud environments.

This capability in the past was only available to SQL Servers running on Azure VMs, and now we have enabled this capability to SQL Servers deployed outside of Azure enabled by Azure Arc-enabled SQL Servers.

BPA for SQL Server performs a comprehensive evaluation of your Operating system, SQL Server instances, and databases. Proactively identifies any risks that SQL Server deployments may be running into, SQL Server and database configurations, security, performance, index strategy, trace flags, disaster recovery, high availability, and many more.

Once the assessment is enabled and executed, BPA will provide a comprehensive report with a prioritized list of the risks detected, the impacted objects, and step-by-step guidance on how to mitigate the risks reported.

Mitigating these risks will result in improved availability and performance thus optimizing your operational costs running your SQL Server workloads. This promotes managing and operating SQL Server health.

Assessment results

The chart below groups all the issues into impact categories, "High", "Medium", "Low", "Info" and "Passed". You can click on any of these slices in the pie chart to filter by the specific severity for review.

You can also look at the distribution of the issues reported in specific areas of SQL Server implementation, this you to prioritize the areas that pose high risk.

The grid views help you dive deeply into specific impacted areas and objects. The grid shows, severity, area impacted, rule id, and no of impacted objects. The 2nd grid points to the impacted object type, impacted object name, the issue severity, and a pointer to a detailed issue and mitigation guidance.

The issue details provide an impact description and point you to a document for step-by-step mitigation guidance.

Trends page

There are three charts on the Trends page to show changes over time: all issues, new issues, and resolved issues. The charts help you see your progress. Ideally, the number of recommendations should go down while the number of resolved issues goes up. The legend shows the average number of issues for each severity level. Hover over the bars to see the individual values for each run.

This chart shows the number of resolved issues in each assessment run.

Get more insights

To get rich insights, you can also run custom queries against multiple Log Analytical Workspaces that contain the data uploaded by various Arc-enabled SQL Server assessments.

1. Click on the "Query logs" icon on the top of the grid in assessment results, this will take you to query editor
   

1. Select the specific "Log Analytics workspace(s)" used for uploading assessment data from your Arc-enabled SQL Servers and query the logs to gain more insights about your SQL Server environments
   

For example, you can use the query below and get the list of all your SQL Servers across the SQL Server estate for which the "Lock pages in memory" policy is not assigned for SQL Server configuration as per best practices.

let selectedCategories = dynamic([]);

let selectedTotSev = dynamic([]);

SqlAssessment_CL

| extend asmt = parse_csv(RawData)

| extend AsmtId=tostring(asmt[1]), CheckId=tostring(asmt[2]), DisplayString=asmt[3], Description=tostring(asmt[4]), HelpLink=asmt[5], TargetType=case(asmt[6] == 1, "Server", asmt[6] == 2, "Database", ""), TargetName=tostring(asmt[7]),

Severity=case(asmt[8] == 30, "High", asmt[8] == 20, "Medium", asmt[8] == 10, "Low", asmt[8] == 0, "Information", asmt[8] == 1, "Warning", asmt[8] == 2, "Critical", "Passed"), Message=tostring(asmt[9]), TagsArr=split(tostring(asmt[10]), ","), Sev = toint(asmt[8])

| where CheckId == "LockedPagesInMemory"

| project

TargetType,

TargetName,

Severity,

Tags=strcat_array(array_slice(TagsArr, 1, -1), ','),

CheckId,

Message

| distinct *

Get the list of all issues raised against multiple SQL Server and databases

let selectedCategories = dynamic([]);

let selectedTotSev = dynamic([]);

SqlAssessment_CL

| extend asmt = parse_csv(RawData)

| extend AsmtId=tostring(asmt[1]), CheckId=tostring(asmt[2]), DisplayString=asmt[3], Description=tostring(asmt[4]), HelpLink=asmt[5], TargetType=case(asmt[6] == 1, "Server", asmt[6] == 2, "Database", ""), TargetName=tostring(asmt[7]),

Severity=case(asmt[8] == 30, "High", asmt[8] == 20, "Medium", asmt[8] == 10, "Low", asmt[8] == 0, "Information", asmt[8] == 1, "Warning", asmt[8] == 2, "Critical", "Passed"), Message=tostring(asmt[9]), TagsArr=split(tostring(asmt[10]), ","), Sev = toint(asmt[8])

| where (Sev >= 0 and array_length(selectedTotSev) == 0 or Sev in (selectedTotSev))

| project

TargetType,

TargetName,

Severity,

Message,

Tags=strcat_array(array_slice(TagsArr, 1, -1), ','),

CheckId,

Description,

HelpLink = tostring(HelpLink),

SeverityCode = toint(Sev)

| order by SeverityCode desc, TargetType desc, TargetName asc

| project-away SeverityCode

Next steps

Note: Best practices assessment is available only for SQL Servers purchased through either “Software Assurance” or “Pay-as-you-go (PAYG)" licensing options. Billing through Microsoft Azure

Enable "Best practices assessment" on all your Arc-enabled SQL Servers and manage SQL Servers healthy at scale. Learn more.

Learn all the benefits of Arc-enabled SQL Servers

Azure Arc-enabled SQL Server

Continue reading...