Register an Azure SQL Server VM with the SQL IaaS Agent Extension

by

How to Register an Azure SQL Server VM with the SQL IaaS Agent Extension Using PowerShell and CLI When we install SQL Server in Azure VM.

The SQL IaaS Agent Extension enables advanced management capabilities for SQL Server Virtual Machines (VMs) in Azure, such as automated backups, patching, and simplified license management. In this article, we’ll guide you through registering an existing SQL Server VM with the SQL IaaS Agent Extension using both PowerShell and Azure CLI.

What is the SQL IaaS Agent Extension?

The SQL IaaS Agent Extension is a lightweight agent that brings Azure SQL management features to SQL Server instances running on Azure VMs. Key benefits include:

  • Automated Backup: Configure backups with defined retention periods.
  • Automated Patching: Apply SQL Server updates with minimal intervention.
  • License Optimization: Choose between Pay-as-you-Go (PAYG) or Azure Hybrid Use Benefit (AHUB).
  • Monitoring and Reporting: Seamless integration with Azure Monitor for SQL.

Prerequisites

  1. Azure CLI or PowerShell:
    • Install Azure CLI: Installation Guide
    • Install Azure PowerShell Module:powershell
    • Install-Module -Name Az -AllowClobber -Force
  2. Permissions: Ensure you have Contributor access or higher to the resource group or subscription.
  3. Microsoft.SqlVirtualMachine Resource Provider:
    • Register the provider if not already enabled:powershell
    • Register-AzResourceProvider -ProviderNamespace "Microsoft.SqlVirtualMachine"
  4. SQL Server VM: The VM must exist, be running, and have SQL Server installed.

Option 1: Register Using PowerShell

PowerShell Script

powershell# Variables
$ResourceGroupName = "MASDAR-UAEN-FP-RG"  # Replace with your Resource Group name
$VMName = "AEADFPSQLDB01"                # Replace with your VM name
$LicenseType = "AHUB"                    # Use "PAYG" for Pay-as-you-Go or "AHUB" for Azure Hybrid Use Benefit

# Register SQL VM with SQL IaaS Agent Extension
Register-AzSqlVM -ResourceGroupName $ResourceGroupName -VMName $VMName -LicenseType $LicenseType

Verifying Registration

Use the following PowerShell command to check the registration status:

powershell

Get-AzSqlVM -ResourceGroupName $ResourceGroupName -VMName $VMName

Option 2: Register Using Azure CLI

If you prefer using Azure CLI, the command to register the SQL Server VM is straightforward:

CLI Command

bash
az sql vm create --name <VM Name> --resource-group <VM RG> --location "VM Deployed location" --license-type AHUB

Parameters Explained

  1. --name: Name of the Azure VM.
  2. --resource-group: Resource Group where the VM resides.
  3. --location: Azure region where the VM is deployed. Use quotes if the region contains spaces.
  4. --license-type: Select between PAYG or AHUB for license management.

Verifying Registration

Check the status of the registered VM using:

bash
az sql vm show --name <Vm Name> --resource-group <VM RG>

Benefits of Using PowerShell and CLI

  1. Consistency: Both methods allow repeatable and reliable registration processes.
  2. Flexibility: CLI is ideal for cross-platform environments, while PowerShell integrates seamlessly with Azure automation workflows.
  3. Scalability: Automate the registration of multiple VMs by iterating through a list in a script.

Troubleshooting Tips

  1. Resource Provider Issues:
    • If you encounter errors, ensure the Microsoft.SqlVirtualMachine resource provider is registered:
    • bash az provider register --namespace "Microsoft.SqlVirtualMachine"
  2. Invalid License Type:
    • Ensure the license type is either PAYG or AHUB. Other values will cause the command to fail.
  3. Provisioning State:
    • Use the Get-AzSqlVM or az sql vm show command to confirm the Provisioning State is Succeeded.

Conclusion

Registering SQL Server VMs with the SQL IaaS Agent Extension unlocks a range of advanced features that enhance manageability, cost optimization, and monitoring. Whether you choose PowerShell or Azure CLI, these tools provide robust automation options to streamline your Azure SQL environment.

Leave a Comment