Evaluate SQL Server configuration using Best practices assessment for Azure Arc Enabled SQL Server

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

 

While Azure brings most benefits to customers via its PaaS offerings, we understand that not every workload could move to PaaS, some can’t even move to the cloud. You may have regulations to comply with, specific application latency requirements or simply a need to keep up with competition – whichever your reason, it’s clear that hybrid deployments are here to stay. You’re going to run workloads in the cloud, and outside of public cloud datacenters. But you still want access to all the benefits the cloud provides. At the same time, innovation should not be restricted just to the public cloud. At Microsoft, we understand that hybrid cloud capabilities must evolve to enable innovation anywhere, while providing a seamless development, deployment and ongoing management experience.

Azure Arc-enabled SQL Server extends Azure services to SQL Server instances hosted outside of Azure: in your data center, in edge site locations like retail stores, or any public cloud or hosting provider. Azure ARC provides a single pane of glass for all SQL deployments irrespective of their location.

 

Azure Arc-enabled SQL Server architectureAzure Arc-enabled SQL Server architecture

 

You might be asking yourself, whether SQL server on premises is configured correctly following best practices. You can now evaluate if your SQL Server on premises or any public cloud is following configuration best practices using the SQL best practices assessment feature.

 

In this post, I will demonstrate how to setup and use the SQL best practices assessment feature for Azure Arc Enabled SQL Server.

 

Best practices assessment

 

Best practices assessment provides a mechanism to evaluate the configuration of your SQL Server. Once the best practices assessment feature is enabled, your SQL Server instance and databases are scanned to provide recommendations for things like SQL Server and database configurations, index management, deprecated features, enabled or missing trace flags, statistics, etc. 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.

 

SQL Server Best practices assessment powered by Azure Arc Enabled SQL Server provides following benefits:

 

  • Extended diagnosis from Azure to any SQL Server anywhere
  • Scanned in intervals for most up to date results
  • Increases operational stability while reducing routine workloads from DBAs

Prerequisites

 

  1. Make sure your SQL Server instances are connected to Azure.

  2. A Log analytics workspace in the same subscription as your Arc-enabled SQL Server resource to upload assessment results. Refer to documentation on how to create a Log Analytics workspace.

  3. The user configuring SQL BPA must have 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 can be assigned to built-in roles such as Contributor or Owner. These roles have sufficient permissions. For more information, review Assign Azure roles using the Azure portal for more information.

  4. 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.

  5. If outbound connectivity is restricted by your firewall or proxy server, make sure the URLs from target SQL Server machine, make sure the URLs listed below allowed access to Azure Arc over TCP port 443.

    • global.handler.control.monitor.azure.com

    • <virtual-machine-region-name>.handler.control.monitor.azure.com

    • <log-analytics-workspace-id>.ods.opinsights.azure.com

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

  7. The SQL Server browser service must be running if you're operating a named instance of SQL Server.

The following step-by-step instructions help you enable and run your first SQL Server best practices assessment.

Enable and Run SQL Server Best Practices Assessment

 

  1. Go to your Arc-enabled SQL Server resources and open one of these resources. Under the setting menu click on the “Best Practices Assessment”

Ganapathivarma_44-1679340856416.jpeg

 

  1. Select the log analytics workspace that you have in the same subscription as your Arc-enabled SQL Server resource and click on “Enable Assessment”

Ganapathivarma_45-1679340856420.jpeg

 

  1. Repeat these steps for all other Arc-enabled SQL Servers.

  2. Wait for couple of minutes to finish the best practice assessment deployment.

  3. To schedule or to disable an assessment click on “Configuration

Ganapathivarma_46-1679340856425.jpeg

 

  1. Choose Frequency, Day of Week, Recurrence and Assessment start (local machine time) then click “Schedule assessment”.

Ganapathivarma_47-1679340856431.jpeg

 

  1. Wait a couple of minutes and the hit refresh to see your scheduled assessment.

Ganapathivarma_48-1679340856437.jpeg

 

  1. (Optional) To disable assessment, hit configuration and then click on “Disable assessment

Ganapathivarma_49-1679340856441.jpeg

 

Ganapathivarma_50-1679340856446.jpeg

 

  1. To manually run, click “Run assessment” (If you disabled your assessment before running it, follow the steps 1 and 2 to re-enable it.). It will take some time to complete.

 

Ganapathivarma_51-1679340856450.jpeg

 

Ganapathivarma_52-1679340856456.jpeg

 

View Results

 

  1. To view assessment result, click on “View Assessment Results

Ganapathivarma_53-1679340856463.jpeg

 

If there several assessments that are completed, you can choose from “collected at” dropdown menu.

 

Ganapathivarma_54-1679340856468.jpeg

 

The Results page reports all the issues categorized based on their severity. 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.

 

Ganapathivarma_55-1679340856478.jpeg

 

 

  1. To filter the issues by high severity, click on “High” on the Total Issues chart. To clear the filter click on high once again. Filtering can be applied by categories as well.

Ganapathivarma_56-1679340856484.jpeg

 

  1. To view details for a recommendation, in the recommendation summary menu click on high severity issue and in the recommendation, details click on the message.

Ganapathivarma_57-1679340856492.jpeg

 

  1. To Fix “Disable 'AUTO_UPDATE_STATISTICS_ASYNC' option” issue, logon to ARCBOX-CLIENT machine an connect to JSLU-Win-SQL-02 instance and run the statement below.

 

Ganapathivarma_58-1679340856502.jpeg

 

  1. Rerun assessment and check the resolved issues.(To re-run the assessment, Go to Step 8 in Enable and run SQL Server Best Practices assesment part)

Ganapathivarma_59-1679340856507.jpeg

 

  1. To get more insights click on "Query logs" on the top of the grid in assessment results, this will take you to query editor.

Ganapathivarma_60-1679340856513.jpeg

 

  1. Hit “Select scope” and then Select the specific “Log Analytics workspace” used for uploading assessment data from Arc-enabled SQL Server and click “Apply”.

Ganapathivarma_0-1679350618800.png

 

  1. Then query the logs to gain more insights about your SQL Server environments.

  2. To view all unused indexes in a specific instance:

Ganapathivarma_1-1679350650837.png

 

 

 

let selectedCategories = dynamic([]); let selectedTotSev = dynamic([]); SqlAssessment_CL
| where _ResourceId =~ "/subscriptions/<subscription-id>/resourcegroups/ArcSql- Levelup/providers/Microsoft.HybridCompute/machines/JSLU-Win-SQL-02"
| 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 == "UnusedIndex"
| project TargetType, TargetName, Severity,
Tags=strcat_array(array_slice(TagsArr, 1, -1), ','), CheckId, Message
| distinct *

 

 

 

In this blog post, I discussed how to evaluate SQL Server configuration and setup using SQL Server Best Practices Assessment.

 

Thanks for reading this blog post. If you have any comments or questions, don’t hesitate to leave them in the comments section. To learn more about the services used in this post, check out the following resources:

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.