Best practices assessment -Arc-enabled SQL Server
Published Jan 18 2023 04:12 PM 1,770 Views

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.

pie chart for categories.png
 

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.

pie chart for area.png

 

 

 

 

 

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.

 

issue grids.png

 

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

detailed issue.png

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.

resolve trend.png

 

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

 

Activate query window.png

 

  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

 

scope.png

 

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

 

Version history
Last update:
‎Jan 18 2023 05:34 PM
Updated by: