How to Retrieve SQL Server Instance Details from SQL IaaS Agent Extension Using Azure CLI

Question

Hello Azure Community,

I am working with SQL Virtual Machines in Azure and using the SQL Server IaaS Agent Extension to manage my SQL Server instances. In the Azure portal, under the "SQL Virtual Machine" service, I can view detailed SQL Server instance information by clicking the "SQL configuration: Click here to view SQL extension configuration" button. This provides a JSON report similar to the following:

{
    "RpPluginReport": {
        "InstalledSqlSkusAndInstances": [
            {
                "Instance": "STD02",
                "Sku": "Enterprise"
            }
        ],
        "IsSqlManagement": false,
        "LastUpdatedTimeUtc": "/Date(1721814009515)/",
        "LeastPrivilegeModeSettings": false,
        "OSVersion": "WS2022",
        "SqlInstance": "STD02",
        "SqlManagementType": "OK",
        "SqlSku": "Enterprise",
        "SqlVersion": "SQL2022",
        "TargetSqlInstances": "STD02"
    },
    "ProvisioningState": "Succeeded"
}

I would like to retrieve this same information using Azure CLI commands. I have tried using the following command to get details about the SQL Server IaaS extension:

az vm extension show --resource-group rg-7699-xf-prod-std-paris-001 --vm-name FITTOR10SQCP060 --name SqlIaasExtension --subscription m34-p0ll4-m4gn4-35t-8768070cbf28 --output json

However, this command does not provide the detailed SQL Server instance information.

Is there a way to obtain this specific information about the SQL Server instance using Azure CLI commands? Any guidance or suggestions on how to achieve this would be greatly appreciated.

Thank you for your help!

Best regards,

Fernando A.

Answer 1

Hi,

This is what I got from Copilot:

To retrieve instance details from the SQL IaaS Agent Extension using Azure CLI, you can follow these steps:

1. Register your subscription with the Microsoft.SqlVirtualMachine resource provider if you haven’t already. This is necessary to allow the SQL IaaS Agent extension to create resources within your subscription1.
2. Use the az vm extension show command to get details about the SQL Server IaaS extension. Here’s an example of how you might use the command:

az vm extension show \

  --resource-group <your-resource-group> \

  --vm-name <your-vm-name> \

  --name SqlIaasExtension \

  --subscription <your-subscription-id> \

  --output json

Replace <your-resource-group>, <your-vm-name>, and <your-subscription-id> with your actual resource group name, virtual machine name, and subscription ID, respectively.

1. The output will be in JSON format, which will include the details of the SQL Server instances managed by the SQL IaaS Agent Extension.

For a more detailed guide and additional options, you can refer to the official Microsoft documentation on how to register with the SQL IaaS Agent Extension and manage your SQL Server instances on Azure VMs2. If you encounter any issues or have specific requirements, these resources provide comprehensive information and troubleshooting tips.