Configure SQL Assessment | SQL Server on Azure Arc-enabled servers

SQL Assessment provides a mechanism to evaluate your configuration of SQL Server. This article provides instructions for using SQL Assessment on an instance of SQL Server on Azure Arc-enabled servers.

Prerequisites

• Your Windows-based SQL Server instance is connected to Azure Arc. Follow the instructions to onboard your SQL Server instance to Arc-enabled SQL Server.

  Note

  On-demand SQL Assessment is currently limited to SQL Server running on Windows machines. This will not work for SQL on Linux machines.

• The Microsoft Monitoring Agent (MMA) must be installed and configured on the machine. View the Install MMA article for instructions. You can also get more information on the Log Analytics Agent article.

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

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

• Make sure you've reviewed the SQL Server document at Services Hub On-Demand Assessments Prerequisites.

Run on-demand SQL Assessment

1. Open your SQL Server – Azure Arc resource and select Environment Health in the left pane.

   

   Important

   If the MMA extension is not installed, you can't initiate the on-demand SQL Assessment.

2. Select the account type. If you have a Managed service account, it will allow you to initiate SQL Assessment directly from the portal. Specify the account name.

   Note

   Specifying a Managed service account activates the Configure SQL Assessment button so you can initiate the assessment from the portal by deploying a CustomScriptExtension. Because you can only deploy one CustomScriptExtension at a time, the script extension for SQL Assessment will be automatically removed after execution.

   If you already have another CustomScriptExtension deployed to the hosting machine, the Configure SQL Assessment button will not be activated.

3. Specify a working directory on the data collection machine if you want to change the default. By default, C:\sql_assessment\work_dir is used. During collection and analysis, the assessment temporarily stores data in that folder. If the folder doesn't exist, the assessment creates it automatically.

4. If you initiate SQL Assessment from the portal by clicking Configure SQL Assessment, the portal presents a standard deployment bubble.

   

   Alternatively, you can initiate SQL Assessment from the target machine. Click Download configuration script, copy the downloaded script to the target machine and and execute one of the following code blocks in a admin instance of powershell.exe:

   • Domain account: You'll be prompted for the user account and password.

     Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass -Force
     & '.\AddSqlAssessment.ps1'
     

   • Managed Service Account (MSA)

     Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass -Force
     & '.\AddSqlAssessment.ps1' -ManagedServiceAccountName <MSA account name>
     

   Note

   The script schedules a task named SQLAssessment, which triggers data collection. This task executes within an hour after you've run the script. It then repeats every seven days.

   Tip

   You can modify the task to run on a different date and time or even force it to run immediately. In the the task scheduler library, find Microsoft > Operations Management Suite > AOI*** > Assessments > SQLAssessment.

View SQL Assessment results

• On the Environment Health pane, select the View SQL Assessment results button.

  Note

  The View SQL Assessment results button remains disabled until the results are ready in Log Analytics. This process might take up to two hours after the data files are processed on the target machine.

  

• You can see the state of data processing on the collection machine by checking the files in the working folder. After the scheduled task is completed, you should see several files with the new. prefix in the working directory.

  

• The Microsoft Monitoring Agent scans the working folder every 15 minutes. It looks for new.* files and sends the data to the Log Analytics workspace. After MMA uploads the file, it changes the prefix change from new. to processed.

  

Next steps

• Get more information by viewing the prerequisite documents at Services Hub On-Demand Assessments.

• To obtain comprehensive support of the on-demand SQL Assessment feature, a Premier or Unified support subscription is required. For details, see Azure Premier Support.