Configure SQL Assessment on an Azure Arc enabled SQL Server instance

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

Prerequisites

Run on-demand SQL Assessment

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

  2. Specify a working directory on the data collection machine. By default, C:\sql_assessment\work_dir is used. During collection and analysis, data is temporarily stored in that folder. If the folder doesn't exist, it's created automatically.

  3. Select Download configuration script. Copy the downloaded script to the target machine.

  4. Open an admin instance of powershell.exe and execute one of the following code blocks:

    • 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 modified 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