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

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.

    Screenshot showing a File Manager window displaying processed data files.

Next steps