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.
Your SQL Server instance must be connected to Azure Arc. For instructions, see the Connect your SQL Server to Azure Arc article.
The Microsoft Monitoring Agent (MMA) extension 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
Open your SQL Server – Azure Arc resource and select Environment Health in the left pane.
Specify a working directory on the data collection machine. By default,
C:\sql_assessment\work_diris used. During collection and analysis, data is temporarily stored in that folder. If the folder doesn't exist, it's created automatically.
Select Download configuration script. Copy the downloaded script to the target machine.
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>
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.
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.
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.