| title | Configure on-demand SQL Assessment on an Azure Arc enabled SQL Server instance |
|---|---|
| description | Configure on-demand SQL Assessment on an Azure Arc enabled SQL Server instance |
| author | anosov1960 |
| ms.author | sashan |
| ms.reviewer | mikeray |
| ms.date | 09/10/2020 |
| ms.topic | conceptual |
| ms.prod | sql |
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.
-
Open your SQL Server – Azure Arc resource and select Environment Health in the left pane.
[!div class="mx-imgBorder"]
Important
If MMA extension is not installed, you will not be able to initiate the on-demand SQL Assessment.
- 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 will activate the Configure SQL Assessment button so you could initiate the assessment from the Portal by deploying a CustomScriptExtension. Because only one CustomScriptExtension can be deployed 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.
-
Specify a working directory on the data collection machine if you want to change the default. 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. -
If you initiate SQL Assessment from the Portal by clicking Configure SQL Assessment, a standard deployment bubble will show up.
[!div class="mx-imgBorder"]
-
If you prefer initiating 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.
-
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.
[!div class="mx-imgBorder"]
-
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.
[!div class="mx-imgBorder"]
-
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.
[!div class="mx-imgBorder"]
-
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.