Configure SQL best practices assessment - SQL Server enabled by Azure Arc

Applies to: SQL Server

Best practices assessment provides a mechanism to evaluate the configuration of your SQL Server. After you enable best practices assessment, an assessment scans your SQL Server instance and databases to provide recommendations for things like:

• SQL Server and database configurations
• Index management
• Deprecated features
• Enabled or missing trace flags
• Statistics
• & more

Assessment run time depends on your environment (number of databases, objects, and so on), with a duration from a few minutes, up to an hour. Similarly, the size of the assessment result also depends on your environment. Assessment runs against your instance and all databases on that instance. In our testing, we observed that an assessment run can have up to 5-10% CPU impact on the machine. In these tests, the assessment was done while a TPC-C like application was running against the SQL Server.

This article provides instructions for using best practices assessment on an instance of SQL Server enabled by Azure Arc.

Prerequisites

• Your Windows-based SQL Server instance is connected to Azure. Follow the instructions at Automatically connect SQL Server machines to Azure Arc.

  Note

  Best practices assessment is currently limited to SQL Server running on Windows machines. The assessment doesn't apply to SQL on Linux machines currently.

• If the server hosts a single SQL Server instance: Make sure that the version of Azure Extension for SQL Server (WindowsAgent.SqlServer) is "1.1.2202.47" or later.

• If the server hosts multiple instances of SQL Server: Make sure that the version of Azure Extension for SQL Server (WindowsAgent.SqlServer) is greater than "1.1.2231.59".

  Tip

  To check the version and update to update to the latest, review Upgrade extension.

• If the server hosts a named instance of SQL Server, SQL Server browser service must be running.

• A Log Analytics workspace must be in the same subscription as your SQL Server enabled by Azure Arc resource.

• The user configuring SQL best practices assessment (BPA) must have the following permissions.

  • Log Analytics Contributor role on resource group or subscription of the Log Analytics workspace.
  • Azure Connected Machine Resource Administrator role on the resource group or subscription of the Arc-enabled SQL Server.
  • Monitoring Contributor role on the Resource group or subscription of Log Analytics Workspace & Resource group or subscription of Arc Machine.
  • Users assigned to built-in roles such as Contributor or Owner have sufficient permissions. For more information, review Assign Azure roles using the Azure portal for more information.

• The minimum permissions required to access or read the assessment report are:

  • Reader role on the resource group or subscription of the Arc-enabled SQL Server resource.

  • Log analytics reader.

  • Monitoring reader on resource group/subscription of Log Analytics workspace.

  • The SQL Server built-in login NT AUTHORITY\SYSTEM must be the member of SQL Server sysadmin server role for all the SQL Server instances running on the machine.

  • If your firewall or proxy server restricts outbound connectivity, make sure they allow to Azure Arc over TCP port 443 for these URLs.

    • global.handler.control.monitor.azure.com
    • *.handler.control.monitor.azure.com
    • <log-analytics-workspace-id>.ods.opinsights.azure.com
    • *.ingest.monitor.azure.com

• Your SQL Server instance must have the TCP/IP protocol enabled.

• SQL BPA uses Azure Monitor Agent (AMA) to collect and analyze data from your SQL servers. If you have AMA installed on your SQL servers before enabling BPA, BPA uses the same AMA agent and proxy settings. You don't need to do anything else. However, if you don't have AMA installed on your SQL servers, BPA installs it for you. BPA will not set up proxy settings for AMA automatically. You need to re-deploy AMA with the proxy settings that you want. Review AMA Network Settings and Proxy Configuration for more information on AMA network and proxy settings.

• If you use Configure Arc-enabled Servers with SQL Server extension installed to enable or disable SQL best practices assessment Azure policy to enable assessment at scale, you need to create an Azure Policy assignment. Your subscription requires the Resource Policy Contributor role assignment for the scope that you're targeting. The scope may be either subscription or resource group. Further, if you are going to create a new user assigned managed identity, you need the User Access Administrator role assignment in the subscription.

Enable best practices assessment

1. Sign into the Azure portal and go to your Arc-enabled SQL Server resource

2. Open your Arc-enabled SQL Server resource and select Best practices assessment in the left pane or Best practices assessment tab in the Capabilities tab of the Overview page.

   

3. If the Log Analytics workspace is not created or the current user does not have Log Analytics Contributor role assigned for the resource group or subscription, you can't initiate the on-demand SQL Assessment. Review the Prerequisites.

   

4. Select the Log Analytics workspace from the drop-down menu and select Enable assessment.

   

   Note

   After you enable the assessment, setup and configuration can take a few minutes.

   Best practices assessment is enabled for all SQL Server instances running on the machine and assess the SQL Server host comprehensively.

5. Upon successful best practices assessment deployment, the assessment is scheduled to run every Sunday 12:00 AM local time by default.

   

Enable best practices assessment at scale using Azure policy

You can automatically enable best practices assessment on multiple Arc-enabled SQL Server instances at scale using an Azure policy definition called Configure Arc-enabled Servers with SQL Server extension installed to enable or disable SQL best practices assessment. This policy definition is not assigned to a scope by default. If you assign this policy definition to a scope of your choice, it enables the SQL best practices assessment on all SQL Server instances enabled for Azure Arc within the defined scope, and auto schedule to every Sunday 12:00 AM local time by default.

# Define resource group and policy 
$rg = Get-AzResourceGroup -Name "<Resource Group Name>"
$policyAssignmentName = "SQLBestPracticesAssessmentAssignment"
$policyDefinitionName = "Configure Arc-enabled Servers with SQL Server extension installed to enable or disable SQL best practices assessment."
$policyDefinition = Get-AzPolicyDefinition |
  Where-Object { $_.Properties.DisplayName -eq 'Configure Arc-enabled Servers with SQL Server extension installed to enable or disable SQL best practices assessment.'}

#  Assign policy parameters
$policyParameterObj = @{
    "effect" = "DeployIfNotExists"
    "laWorkspaceId" = "<Log Analytics Workspace ID>"
    "laWorkspaceLocation" = "<Log Analytics Workspace Location>"
    "isEnabled" = $true 
}

# Assign the policy
New-AzPolicyAssignment -Name $policyAssignmentName `
    -DisplayName $policyDefinitionName `
    -PolicyDefinition $policyDefinition `
    -Scope $rg.ResourceId `
    -PolicyParameterObject $policyParameterObj `
    -IdentityType 'SystemAssigned' `
    -Location $rg.Location

# Verify the policy assignment
Get-AzPolicyAssignment -Name $policyAssignmentName -Scope $rg.ResourceId

See Azure Policy documentation for general instructions about how to assign an Azure policy using Azure portal or an API of your choice.

Modify license type

If an instance of SQL Server is configured with a license only type of license, you need to change the license type to configure best practices assessment. For more information, see Manage SQL Server license and billing options.

Manage best practices assessment

After you have enabled the best practices assessment, you can run, or configure the assessment as required.

• To run the assessment on demand from the portal, select Run assessment.

  

  Note

  When you perform any of the following tasks on a specific SQL Server instance, the task is applied to all SQL Server instances running on the machine.

  The View assessment results is inactive until the results are ready in Log Analytics workspace. This process might take up to two hours after the data files are processed on the target machine.

  

• To schedule assessments, select Configuration > Schedule assessment.

  

• To disable an assessment select Configuration > Disable assessment.

View best practices assessment results

• On the Best practices assessment pane, select any of the individual row items to view the results.

Results page

The Results page reports all the issues categorized based on their severity for all the SQL Server instances running on the machine. You can switch the results view between the SQL Server instances running on the machine and assessment execution times using the top-down menus "Instance name" and "Collected at" respectively. The recommendations are organized into All, New, and Resolved tabs. The tabs can be used to view all the recommendations from the currently selected run, the newer recommendations compared to the previous run, and the resolved recommendations from the previous runs respectively. The tabs help to keep track of the progress between the runs. The Insights tab identifies the most recurring issues and the databases with the maximum number of issues.

The graph groups assessment results in different categories of severity - high, medium, low, and information. Select each category to see the list of recommendations, or search for key phrases in the search box. It's best to start with the most severe recommendations and go down the list.

The first grid shows each recommendation and the affected instances in the environment with the reported issues. When a row is selected in the first grid, the second grid lists all the affected instances for that particular recommendation. If no recommendation is selected, then the second grid shows all the recommendations. If the assessment reports a large number of recommendations, you can filter the results.

To filter results, use the drop-down menu above the grid. Namely:

• Name
• Severity
• Check Id.

To download results, use Export to Excel.

To open the results in Log Analytics, use Open the last run query in the Logs view.

The passed section of the graph identifies recommendations your system already follows. View detailed information for each recommendation by selecting the Message field, such as a long description, and relevant online resources.

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 decrease while the number of resolved issues increases. The legend shows the average number of issues for each severity level. Hover over the bars to see the individual values for each run.

If there are multiple runs in a single day, only the latest run is included in the graphs on the Trends page.

Known issues

• Best practices assessment is currently limited to SQL Server running on Windows machines. The assessment doesn't work for SQL on Linux machines.
• It may take a few seconds to populate the history of the previous execution of the assessments on the best practices home page.
• The assessment results can also be viewed by directly querying the Log Analytics workspaces. For example queries, see Best practices assessment - Arc-enabled SQL Server.
• Do not make any other extension configuration changes while the Azure policy is remediating the noncompliant Arc-enabled SQL Server resources. Track Azure policy remediation task progress.

Troubleshooting

For more information, see the troubleshooting guide.

Related content

• Review the rich set of nearly 500 rules best practices assessment applies.

• To obtain comprehensive support of the best practices assessment feature, a Premier or Unified support subscription is required. For details, see Azure Premier Support.

• View SQL Server databases - Azure Arc