Registrar várias VMs do SQL no Azure com a extensão do agente IaaS do SQL

Artigo
05/10/2022
24 minutos para o fim da leitura

APLICA-SE A: SQL Server na VM do Azure

Este artigo descreve como registrar suas VMs (máquinas virtuais) do SQL Server em massa no Azure com a extensão do agente IaaS do SQL usando o Register-SqlVMscmdlet do Azure PowerShell.

Este artigo ensina você a registrar VMs do SQL Server manualmente em massa. Como alternativa, você pode registrar todas as VMs de SQL Server automaticamenteouas VMs do SQL Server individuais manualmente.

Observação

A partir de setembro de 2021, o registro com a extensão IaaS do SQL em modo completo não requer mais a reinicialização do serviço SQL Server.

Visão geral

O cmdlet Register-SqlVMs pode ser usado para registrar todas as máquinas virtuais em uma determinada lista de assinaturas, em grupos de recursos ou em uma lista de máquinas virtuais específica. O cmdlet registra as máquinas virtuais no modo de gerenciamento leve e gera um relatório e um arquivo de log.

O processo de registro não tem riscos, não tem nenhum tempo de inatividade e não reinicia o SQL Server ou a máquina virtual.

Por padrão, as VMs do Azure que têm o SQL Server 2016 ou posterior instalado serão automaticamente registradas com a extensão do Agente de IaaS de SQL quando detectadas pelo serviço do Programa de Aperfeiçoamento da Experiência do Usuário. Consulte o suplemento de privacidade do SQL Server para mais informações.

Pré-requisitos

Para registrar a sua VM do SQL Server na extensão, você precisará de:

Uma assinatura do Azure que foi registrada no provedor de recursos Microsoft.SqlVirtualMachine e que contenha máquinas virtuais não registradas do SQL Server.
As credenciais de cliente usadas para registrar as máquinas virtuais existentes em qualquer uma das seguintes funções do Azure: Colaborador de máquina virtual, Colaborador ou Proprietário.
A versão mais recente do AZ PowerShell (mínimo 5.0).

Introdução

Antes de continuar, primeiro você deve criar uma cópia local do script, importá-la como um módulo do PowerShell e conectá-la ao Azure.

Criar o script

Para criar o script, copie o script completo no fim deste artigo e salve-o localmente como RegisterSqlVMs.psm1.

Importar o script

Depois de criar o script, você poderá importá-lo como um módulo no terminal do PowerShell.

Abra um terminal administrativo do PowerShell e acesse o local em que o arquivo RegisterSqlVMs.psm1 foi salvo. Execute o seguinte cmdlet do PowerShell para importar o script como um módulo:

Import-Module .\RegisterSqlVMs.psm1

Conectar-se ao Azure

Use o seguinte cmdlet do PowerShell para se conectar ao Azure:

Connect-AzAccount

Todas as VMs em uma lista de assinaturas

Use o seguinte cmdlet para registrar todas as máquinas virtuais do SQL Server em uma lista de assinaturas:

Register-SqlVMs -SubscriptionList SubscriptionId1,SubscriptionId2

Saída de exemplo:

Number of subscriptions registration failed for 
because you do not have access or credentials are wrong: 1
Total VMs Found: 10
VMs Already registered: 1
Number of VMs registered successfully: 4
Number of VMs failed to register due to error: 1
Number of VMs skipped as VM or the guest agent on VM is not running: 3
Number of VMs skipped as they are not running SQL Server On Windows: 1

Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
Please find the error details in file VMsNotRegisteredDueToError1571314821.log

Todas as VMs em uma assinatura única

Use o seguinte cmdlet para registrar todas as máquinas virtuais do SQL Server em uma assinatura única:

Register-SqlVMs -Subscription SubscriptionId1

Saída de exemplo:

Total VMs Found: 10
VMs Already registered: 1
Number of VMs registered successfully: 5
Number of VMs failed to register due to error: 1
Number of VMs skipped as VM or the  guest agent on VM is not running: 2
Number of VMs skipped as they are not running SQL Server On Windows: 1

Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
Please find the error details in file VMsNotRegisteredDueToError1571314821.log

Todas as VMs em vários grupos de recursos

Use o seguinte cmdlet para registrar todas as máquinas virtuais do SQL Server em vários grupos de recursos em uma assinatura única:

Register-SqlVMs -Subscription SubscriptionId1 -ResourceGroupList ResourceGroup1,ResourceGroup2

Saída de exemplo:

Total VMs Found: 4
VMs Already registered: 1
Number of VMs registered successfully: 1
Number of VMs failed to register due to error: 1
Number of VMs skipped as they are not running SQL Server On Windows: 1

Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
Please find the error details in file VMsNotRegisteredDueToError1571314821.log

Todas as VMs em um grupo de recursos

Use o seguinte cmdlet para registrar todas as máquinas virtuais do SQL Server em um grupo de recursos único:

Register-SqlVMs -Subscription SubscriptionId1 -ResourceGroupName ResourceGroup1

Saída de exemplo:

Total VMs Found: 4
VMs Already registered: 1
Number of VMs registered successfully: 1
Number of VMs failed to register due to error: 1
Number of VMs skipped as VM or the guest agent on VM is not running: 1

Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
Please find the error details in file VMsNotRegisteredDueToError1571314821.log

VMs específicas em um grupo de recursos único

Use o seguinte cmdlet para registrar máquinas virtuais do SQL Server específicas em um grupo de recursos único:

Register-SqlVMs -Subscription SubscriptionId1 -ResourceGroupName ResourceGroup1 -VmList VM1,VM2,VM3

Saída de exemplo:

Total VMs Found: 3
VMs Already registered: 0
Number of VMs registered successfully: 1
Number of VMs skipped as VM or the guest agent on VM is not running: 1
Number of VMs skipped as they are not running SQL Server On Windows: 1

Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
Please find the error details in file VMsNotRegisteredDueToError1571314821.log

Uma VM específica

Use o seguinte cmdlet para registrar uma máquina virtual do SQL Server específica:

Register-SqlVMs -Subscription SubscriptionId1 -ResourceGroupName ResourceGroup1 -Name VM1

Saída de exemplo:

Total VMs Found: 1
VMs Already registered: 0
Number of VMs registered successfully: 1

Please find the detailed report in  file RegisterSqlVMScriptReport1571314821.txt

Descrição da saída

Um relatório e um arquivo de log são gerados sempre que o cmdlet Register-SqlVMs é usado.

Relatório

O relatório é gerado como um arquivo .txt chamado RegisterSqlVMScriptReport<Timestamp>.txt, em que o carimbo de data/hora é o horário em que o cmdlet foi iniciado. O relatório lista os seguintes detalhes:

Valor de saída	Descrição
Ocorreu falha no registro do número de assinaturas porque você não tem acesso ou as credenciais estão incorretas	Isso fornece o número e a lista de assinaturas que tiveram problemas com a autenticação fornecida. O erro detalhado pode ser encontrado no log ao pesquisar pela ID da assinatura.
Número de assinaturas que não puderam ser testadas, pois não estavam registradas no provedor de recursos	Esta seção aborda a contagem e a lista de assinaturas que não foram registradas na extensão do agente IaaS do SQL.
Total de VMs encontradas	A contagem de máquinas virtuais encontradas no escopo dos parâmetros passados para o cmdlet.
VMs já registradas	A contagem de máquinas virtuais que foram ignoradas porque já estavam registradas na extensão.
Número de VMs registradas com êxito	A contagem de máquinas virtuais que foram registradas com êxito após a execução do cmdlet. Lista as máquinas virtuais registradas no formato `SubscriptionID, Resource Group, Virtual Machine`.
Número de VMs com falha ao registrar devido a um erro	Contagem de máquinas virtuais com falha ao registrar devido a algum erro. Os detalhes do erro podem ser encontrados no arquivo de log.
Número de VMs ignoradas porque a VM ou o agente convidado na VM não estava em execução	A contagem e a lista de máquinas virtuais que não puderam ser registradas porque a máquina virtual ou o agente convidado na máquina virtual não estava em execução. Será possível tentar novamente depois de iniciar a máquina virtual ou o agente convidado. Os detalhes podem ser encontrados no arquivo de log.
Número de VMs ignoradas porque não estavam executando o SQL Server no Windows	Contagem de máquinas virtuais que foram ignoradas porque não estavam executando o SQL Server ou não eram máquinas virtuais do Windows. As máquinas virtuais são listadas no formato `SubscriptionID, Resource Group, Virtual Machine`.

Log

Os erros são registrados no arquivo de log chamado VMsNotRegisteredDueToError<Timestamp>.log, em que o carimbo de data/hora é a hora em que o script foi iniciado. Se o erro ocorrer em nível de assinatura, o log conterá a ID da assinatura e a mensagem de erro separadas por vírgulas. Se o erro ocorrer no registro da máquina virtual, o log conterá a ID da assinatura, o nome do grupo de recursos, o nome da máquina virtual, o código de erro e a mensagem separados por vírgulas.

Comentários

Ao registrar as VMs do SQL Server na extensão usando o script fornecido, considere o seguinte:

O registro com o provedor de recursos requer um agente convidado em execução na VM do SQL Server. As imagens do Windows Server 2008 não têm um agente convidado, portanto, ocorrerá falha nessas máquinas virtuais e será necessário registrá-las manualmente usando o modo de gerenciamento NoAgent.
Há uma lógica de repetição interna para solucionar erros transparentes. Se a máquina virtual for registrada com êxito, a operação será rápida. No entanto, em caso de falha do registro, cada máquina virtual será testada novamente. Portanto, o processo de registro requer um tempo significativo para ser concluído, embora o requisito de tempo real dependa do tipo e da quantidade de erros.

Script completo

Para ver o script completo no GitHub, confira Registrar VMs do SQL em massa com o Az PowerShell.

Copie o script completo e salve-o como RegisterSqLVMs.psm1.

#Requires -Module Az.Compute
#Requires -Module Az.Accounts
#Requires -Module Az.SqlVirtualMachine
#Requires -Module Az.Resources
#Requires -Module Microsoft.PowerShell.Security

<#
    .SYNOPSIS
    Register all Azure VM running SQL Server on Windows with SQL VM Resource provider.

    .DESCRIPTION
    Identify and register all Azure VM running SQL Server on Windows in a list of subscriptions, resource group list, particular resource group
    or a particular VM with SQL VM Resource provider.
    The cmdlet registers the VMs and generates a report and a log file at the end of the execution. The report is generated as a txt file named
    RegisterSqlVMScriptReport<Timestamp>.txt. Errors are logged in the log file named VMsNotRegisteredDueToError<Timestamp>.log. Timestamp is the
    time when the cmdlet was started. A summary is displayed at the end of the script run.
    The Output summary contains the number of VMs that successfully registered, failed or were skipped because of various reasons. The detailed list
    of VMs can be found in the report and the details of error can be found in the log.

    Prerequisites:
    - The script needs to be run on Powershell 5.1 (Windows Only) and is incompatible with Powershell 6.x
    - The subscription whose VMs are to be registered, needs to be registered to Microsoft.SqlVirtualMachine resource provider first. This link describes
      how to register to a resource provider: https://docs.microsoft.com/azure/azure-resource-manager/resource-manager-supported-services
    - Run 'Connect-AzAccount' to first connect the powershell session to the azure account.
    - The Client credentials must have one of the following RBAC levels of access over the virtual machine being registered: Virtual Machine Contributor,
      Contributor or Owner
    - The script requires Az powershell module (>=2.8.0) to be installed. Details on how to install Az module can be found 
      here : https://docs.microsoft.com/powershell/azure/install-az-ps?view=azps-2.8.0
      It specifically requires Az.Compute, Az.Accounts and Az.Resources module which comes as part of Az module (>=2.8.0) installation.
    - The script also requires Az.SqlVirtualMachine module. Details on how to install Az.SqlVirtualMachine can be
      found here: https://www.powershellgallery.com/packages/Az.SqlVirtualMachine/0.1.0

    .PARAMETER SubscriptionList
    List of Subscriptions whose VMs need to be registered

    .PARAMETER Subscription
    Single subscription whose VMs will be registered

    .PARAMETER ResourceGroupList
    List of Resource Groups in a single subscription whose VMs need to be registered

    .PARAMETER ResourceGroupName
    Name of the ResourceGroup whose VMs need to be registered

    .PARAMETER VmList
    List of VMs in a single resource group that needs to be registered

    .PARAMETER Name
    Name of the VM to be registered

    .EXAMPLE
    #To register all VMs in a list of subscriptions
    Register-SqlVMs -SubscriptionList SubscriptionId1,SubscriptionId2
    -----------------------------------------------------------------------------------------------------------------------------------------------
    Summary
    -----------------------------------------------------------------------------------------------------------------------------------------------
    Number of Subscriptions registration failed for because you do not have access or credentials are wrong: 1
    Total VMs Found: 10
    VMs Already registered: 1
    Number of VMs registered successfully: 4
    Number of VMs failed to register due to error: 1
    Number of VMs skipped as VM or the guest agent on VM is not running: 3
    Number of VMs skipped as they are not running SQL Server On Windows: 1
    
    Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
    Please find the error details in file VMsNotRegisteredDueToError1571314821.log
    -----------------------------------------------------------------------------------------------------------------------------------------------

    .EXAMPLE
    #To register all VMs in a single subscription
    Register-SqlVMs -Subscription SubscriptionId1
    -----------------------------------------------------------------------------------------------------------------------------------------------
    Summary
    -----------------------------------------------------------------------------------------------------------------------------------------------
    Total VMs Found: 10
    VMs Already registered: 1
    Number of VMs registered successfully: 5
    Number of VMs failed to register due to error: 1
    Number of VMs skipped as VM or the guest agent on VM is not running: 2
    Number of VMs skipped as they are not running SQL Server On Windows: 1
    
    Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
    Please find the error details in file VMsNotRegisteredDueToError1571314821.log
    -----------------------------------------------------------------------------------------------------------------------------------------------

    .EXAMPLE
    #To register all VMs in a single subscription and multiple resource groups
    Register-SqlVMs -Subscription SubscriptionId1 -ResourceGroupList ResourceGroup1,ResourceGroup2

    -----------------------------------------------------------------------------------------------------------------------------------------------
    Summary
    -----------------------------------------------------------------------------------------------------------------------------------------------
    Total VMs Found: 4
    VMs Already registered: 1
    Number of VMs registered successfully: 1
    Number of VMs failed to register due to error: 1
    Number of VMs skipped as they are not running SQL Server On Windows: 1
    
    Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
    Please find the error details in file VMsNotRegisteredDueToError1571314821.log
    -----------------------------------------------------------------------------------------------------------------------------------------------

    .EXAMPLE
    #To register all VMs in a resource group
    Register-SqlVMs -Subscription SubscriptionId1 -ResourceGroupName ResourceGroup1

    -----------------------------------------------------------------------------------------------------------------------------------------------
    Summary
    -----------------------------------------------------------------------------------------------------------------------------------------------
    Total VMs Found: 4
    VMs Already registered: 1
    Number of VMs registered successfully: 1
    Number of VMs failed to register due to error: 1
    Number of VMs skipped as VM or the guest agent on VM is not running: 1
    
    Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
    Please find the error details in file VMsNotRegisteredDueToError1571314821.log
    -----------------------------------------------------------------------------------------------------------------------------------------------

    .EXAMPLE
    #To register multiple VMs in a single subscription and resource group
    Register-SqlVMs -Subscription SubscriptionId1 -ResourceGroupName ResourceGroup1 -VmList VM1,VM2,VM3

    -----------------------------------------------------------------------------------------------------------------------------------------------
    Summary
    -----------------------------------------------------------------------------------------------------------------------------------------------
    Total VMs Found: 3
    VMs Already registered: 0
    Number of VMs registered successfully: 1
    Number of VMs skipped as VM or the guest agent on VM is not running: 1
    Number of VMs skipped as they are not running SQL Server On Windows: 1
    
    Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
    Please find the error details in file VMsNotRegisteredDueToError1571314821.log
    -----------------------------------------------------------------------------------------------------------------------------------------------

    .EXAMPLE
    #To register a particular VM
    Register-SqlVMs -Subscription SubscriptionId1 -ResourceGroupName ResourceGroup1 -Name VM1

    -----------------------------------------------------------------------------------------------------------------------------------------------
    Summary
    -----------------------------------------------------------------------------------------------------------------------------------------------
    Total VMs Found: 1
    VMs Already registered: 0
    Number of VMs registered successfully: 1
    
    Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
    -----------------------------------------------------------------------------------------------------------------------------------------------

    .LINK
    https://aka.ms/RegisterSqlVMs

    .LINK
    https://www.powershellgallery.com/packages/Az.SqlVirtualMachine/0.1.0
#>
function Register-SqlVMs {
    [CmdletBinding(DefaultParameterSetName = 'SubscriptionList')]
    Param
    (
        [Parameter(Mandatory = $true, ParameterSetName = 'SubscriptionList')]
        [ValidateNotNullOrEmpty()]
        [string[]]
        $SubscriptionList,
        [Parameter(Mandatory = $true, ParameterSetName = 'SingleSubscription')]
        [Parameter(Mandatory = $true, ParameterSetName = 'ResourceGroupList')]
        [Parameter(Mandatory = $true, ParameterSetName = 'VmList')]
        [ValidateNotNullOrEmpty()]
        [string]
        $Subscription,
        [Parameter(Mandatory = $true, ParameterSetName = 'ResourceGroupList')]
        [string[]]
        $ResourceGroupList,
        [Parameter(Mandatory = $true, ParameterSetName = 'VmList')]
        [Parameter(ParameterSetName = 'SingleSubscription')]
        [string]
        $ResourceGroupName,
        [Parameter(Mandatory = $true, ParameterSetName = 'VmList')]
        [string[]]
        $VmList,
        [Parameter(ParameterSetName = 'SingleSubscription')]
        [string]
        $Name)

    # give disclaimer
    $accepted = Get-DisclaimerAcceptance
    if (!$accepted) {
        return
    }

    #get credential for connecting to subscription
    $credential = Get-Credential -Credential $null

    #Update Globals
    update-Globals

    if ($PsCmdlet.ParameterSetName -eq 'SubscriptionList') {
        $subsCompleted = 0
        #loop over all subscriptions to register VMs
        foreach ($SubscriptionName in $SubscriptionList) {
            [int]$percent = ($subsCompleted * 100) / $SubscriptionList.Count
            Write-Progress -Activity "Register SQL VMs in $($SubscriptionName) $($subsCompleted+1)/$($SubscriptionList.Count)" -Status "$percent% Complete:" -PercentComplete $percent -CurrentOperation "RegisterVMsInSub" -Id 1;
            if (assert-Subscription -Subscription $SubscriptionName -Credential $credential) {
                register-SqlVMForSubscription -Subscription $SubscriptionName -Credential $credential
            }
            $subsCompleted++
        }
        Write-Progress -Activity "Register SQL VMs" -Status "100% Complete:" -PercentComplete 100 -CurrentOperation "RegisterVMsInSub" -Id 1 -Completed;
    }
    elseif (assert-Subscription -Subscription $Subscription -Credential $credential) {
        if ($PsCmdlet.ParameterSetName -eq 'ResourceGroupList') {
            $rgsCompleted = 0
            foreach ($RgName in $ResourceGroupList) {
                [int]$percent = ($rgsCompleted * 100) / $ResourceGroupList.Count
                Write-Progress -Activity "Register SQL VMs in $($RgName) $($rgsCompleted+1)/$($ResourceGroupList.Count)" -Status "$percent% Complete:" -PercentComplete $percent -CurrentOperation "RegisterVMsInRG" -Id 1;
                register-SqlVMForSubscription -Subscription $Subscription -ResourceGroup $RgName -Credential $credential
                $rgsCompleted++
            }
            Write-Progress -Activity "Register SQL VMs" -Status "100% Complete:" -PercentComplete 100 -CurrentOperation "RegisterVMsInRG" -Id 1 -Completed;
        }
        elseif ($PsCmdlet.ParameterSetName -eq 'VmList') {
            $vmsCompleted = 0
            foreach ($VmName in $VmList) {
                [int]$percent = ($vmsCompleted * 100) / $VmList.Count
                Write-Progress -Activity "Register SQL VMs $($vmsCompleted+1)/$($VmList.Count)" -Status "$percent% Complete:" -PercentComplete $percent -CurrentOperation "RegisterVMsInList" -Id 1;
                register-SqlVMForSubscription -Subscription $Subscription -Credential $credential `
                    -ResourceGroupName $ResourceGroupName -Name $VmName
                $vmsCompleted++
            }
            Write-Progress -Activity "Register SQL VMs in List" -Status "100% Complete:" -PercentComplete 100 -CurrentOperation "RegisterVMsInList" -Id 1 -Completed;
        }
        else {
            register-SqlVMForSubscription -Subscription $Subscription -Credential $credential `
                -ResourceGroupName $ResourceGroupName -Name $Name
        }
    }

    #Report 
    new-Report
}

#Globals for reporting and logging
$Global:TotalVMs = 0
$Global:AlreadyRegistered = 0
$Global:SubscriptionsFailedToRegister = 0
$Global:SubscriptionsFailedToConnect = [System.Collections.ArrayList]@()
$Global:SubscriptionsFailedToRegister = [System.Collections.ArrayList]@()
$Global:RegisteredVMs = [System.Collections.ArrayList]@()
$Global:FailedVMs = [System.Collections.ArrayList]@()
$Global:SkippedVMs = [System.Collections.ArrayList]@()
$Global:UntriedVMs = [System.Collections.ArrayList]@()
$Global:LogFile = $null
$Global:ReportFile = $null

<#
    .SYNOPSIS
    Reset Global Variables
#>
function update-Globals() {
    [int]$timestamp = Get-Date (Get-Date)  -UFormat %s
    $Global:TotalVMs = 0
    $Global:AlreadyRegistered = 0
    $Global:SubscriptionsFailedToRegister = 0
    $Global:SubscriptionsFailedToConnect = [System.Collections.ArrayList]@()
    $Global:SubscriptionsFailedToRegister = [System.Collections.ArrayList]@()
    $Global:RegisteredVMs = [System.Collections.ArrayList]@()
    $Global:FailedVMs = [System.Collections.ArrayList]@()
    $Global:SkippedVMs = [System.Collections.ArrayList]@()
    $Global:UntriedVMs = [System.Collections.ArrayList]@()
    $Global:LogFile = "VMsNotRegisteredDueToError" + $timestamp + ".log"
    $Global:ReportFile = "RegisterSqlVMScriptReport" + $timestamp + ".txt"
    Remove-Item $Global:LogFile -ErrorAction Ignore
    Remove-Item $Global:ReportFile -ErrorAction Ignore
    $txtLogHeader = "Subscription,[Resource Group],[VM Name],[ErrorCode],Error Message"
    Write-Output $txtLogHeader | Out-File $Global:LogFile -Append
}

<#
    .SYNOPSIS
    Get list of VM in a subscription or resourcegroup

    .PARAMETER ResourceGroupName
    Resource Group whose VMs need to be returned

    .PARAMETER Name
    Name of the VM to be returned

    .OUTPUTS
    System.Collections.ArrayList list of VMs
#>
function getVmList(
    [string] $ResourceGroupName,
    [string] $Name
) {
    $vmList = [System.Collections.ArrayList]@()
    #if resource group is passed, look inside the group only
    if ($ResourceGroupName) {
        if ($Name) {
            $vm = Get-AzVM -ResourceGroupName $ResourceGroupName -Name $Name
            $tmp = $vmList.Add($vm)
        }
        else {
            $vmsInRg = Get-AzVM -ResourceGroupName $ResourceGroupName
            foreach ($vm in $vmsInRg) {
                $tmp = $vmList.Add($vm)
            }
        }
    }
    else {
        $vmsInSub = Get-AzVM
        foreach ($vm in $vmsInSub) {
            $tmp = $vmList.Add($vm)
        }
    }
    return , $vmList
}

<#
    .SYNOPSIS
    Get License Type based on the Publisher of the VM image

    .PARAMETER VmObject
    VM Object

    .OUTPUTS
    System.String License Type
#>
function getLicenseType($VmObject) {
    $License = 'AHUB'

    # If published by SQL Server and is not BYOL then treat as PAYG
    if (($VmObject.StorageProfile.ImageReference.Publisher -eq 'MicrosoftSQLServer') -and ($VmObject.StorageProfile.ImageReference.Offer -notmatch '-BYOL')) {
        $License = 'PAYG'
    }
    else {
        $License = 'AHUB'
    }
    return $License
}

<#
    .SYNOPSIS
    Checks if given error is retryable or not

    .PARAMETER ErrorObject
    Error Object

    .OUTPUTS
    System.boolean True if the error is retryable
#>
function isRetryableError(
    [Parameter(Mandatory = $true)]
    $ErrorObject) {
    $errorCode = $ErrorObject.Exception.Body.Code
    switch ($errorCode) {
        # retryable
        'SqlExtensionNotInstalled' {
            return $true
        }
        'UnExpectedErrorOccurred' {
            return $true
        }
        'Ext_ComputeError' {
            return $true
        }     
        'VmAgentNotRunning' {
            return $true
        }
        'GatewayTimeout' {
            return $true
        }
        'CRPNotAllowedOperation' {
            return $true
        }
        'InternalServerError' {
            return $true
        }

        #else return false
        Default {
            return $false
        }
    }
}

<#
    .SYNOPSIS
    Checks if vm could not be tried to be registered.

    .PARAMETER ErrorObject
    Error Object

    .OUTPUTS
    System.Boolean true if the command did not try registering VM
#>
function isNotTriedRegistering() {
    switch ($ErrorObject.Exception.Body.Code) {
        'VmNotRunning' {
            return $true
        }
        'VmAgentNotRunning' {
            return $true
        }
        Default {
            return $false
        }
    }
}

<#
    .SYNOPSIS
    Check if the error can be ignored

    .PARAMETER ErrorObject
    Error Object

    .OUTPUTS
    System.Boolean True if we can ignore the error, otherwise false
#>
function isIgnorableError($ErrorObject) {
    switch ($ErrorObject.Exception.Body.Code) {
        'NotSupportedSqlVmOSVersion' { 
            return $true
        }
        'Ext_SqlInstanceIsNotInstalled' {
            return $true
        }
        'CannotConvertToAhub' {
            return $true
        }
        Default {
            return $false
        }
    }
}

<#
    .SYNOPSIS
    Check if registration failed because it is not possible to register as AHUB

    .PARAMETER ErrorObject
    Error Object

    .OUTPUTS
    System.Boolean True if failure was due to registering as AHUB, otherwise false
#>
function isUnableToRegisterAsAHUB($ErrorObject) {
    switch ($ErrorObject.Exception.Body.Code) {
        'CannotConvertToAhub' {
            return $true
        }
        Default {
            return $false
        }
    }
}

<#
    .SYNOPSIS
    Logs error and removes dangling SQL VM resources

    .PARAMETER ErrorObject
    Error Object

    .PARAMETER VmObject
    VM for which the error occured
#>
function handleError(
    [Parameter(Mandatory = $true)]
    $ErrorObject,
    $VmObject) {
    $sqlvm = Get-AzSqlVM -ResourceGroupName $VmObject.ResourceGroupName -Name $VmObject.Name  -ErrorAction Ignore
    # delete if a sql vm resource was created before erroring out
    if ($sqlvm) {
        $tmp = Remove-AzSqlVM -ResourceGroupName $VmObject.ResourceGroupName -Name $VmObject.Name -ErrorAction SilentlyContinue
    }

    #if Ignorable error do not log
    if (isIgnorableError -ErrorObject $ErrorObject) {
        $tmp = $Global:SkippedVMs.Add($VmObject)
    }
    else {
        $subID = $VmObject.Id.Split("/")[2]
        $errorMessage = $ErrorObject.Exception.Message

        #modify error message if required
        if ($ErrorObject.Exception.Body.Code -eq 'AuthorizationFailed') {
            $errorMessage += " Client requires either of Virtual Machine Contributor, Contributor or Owner access over the scope."
        }
        Write-Output "$($subID), $($VmObject.ResourceGroupName), $($VmObject.Name), $($ErrorObject.Exception.Body.Code), $($errorMessage)" | Out-File $Global:LogFile -Append
        if (isNotTriedRegistering -ErrorObject $ErrorObject) {
            $tmp = $Global:UntriedVMs.Add($VmObject)
        }
        else {
            $tmp = $Global:FailedVMs.Add($VmObject)
        }
    }
}

<#
    .SYNOPSIS
    Display the disclaimer and ask for confirmation

    .OUTPUTS
    System.Boolean True if accepted, else false
#>
function Get-DisclaimerAcceptance() {
    $confirmation = $null
    new-DashSeperator
    Write-Host "The script will register all Virtual Machines in the provided scope that are running SQL Server, with Azure SQL VM Resource Provider."
    Write-Host "If the Virtual Machine was created from a SQL Server marketplace Pay-As-You-Go image, it will be registered with Pay-As-You-Go License"
    Write-Host "If the Virtual Machine was created using SQL Server marketplace BYOL image Or Customized image Or SQL Server was self-installed on the Azure VM,"
    Write-Host "it will be registered with Azure Hybrid Benefit License."
    Write-Host
    Write-Host "By running this script I confirm that I have sufficient SQL Server license with Software Assurance to apply this Azure Hybrid Benefit for SQL Server on Azure VM"
    Write-Host "Are you sure you want to perform this action?"
    Do {
        $response = Read-Host "[Y] Yes [N] No (default is 'No')"
        switch ($response) {
            'Y' { $confirmation = $true }
            'Yes' { $confirmation = $true }
            'N' { $confirmation = $false }
            'No' { $confirmation = $false }
        }
    } While ($null -eq $confirmation)
    new-DashSeperator
    return $confirmation
}

<#
    .SYNOPSIS
    Creates a new line dashed separator
#>
function new-DashSeperator() {
    Write-Host
    Write-Host "-----------------------------------------------------------------------------------------------------------------------------------------------"
}

<#
    .SYNOPSIS
    Generates the report
#>
function new-Report() {
    new-DashSeperator
    Write-Host "Summary"
    new-DashSeperator

    if ($Global:SubscriptionsFailedToConnect.count -gt 0) {
        $errorMessage = "Number of Subscriptions registration failed for because you do not have access or credentials are incorrect: $($Global:SubscriptionsFailedToConnect.count)"
        show-SubscriptionListForError -ErrorMessage $errorMessage -FailedSubList $Global:SubscriptionsFailedToConnect
    }

    if ($Global:SubscriptionsFailedToRegister.count -gt 0) {
        $errorMessage = "Number of Subscriptions that could not be tried because they are not registered to RP: $($Global:SubscriptionsFailedToRegister.count)"
        show-SubscriptionListForError -ErrorMessage $errorMessage -FailedSubList $Global:SubscriptionsFailedToRegister
    }

    $txtTotalVMsFound = "Total VMs Found: $($Global:TotalVMs)" 
    Write-Output $txtTotalVMsFound | Out-File $Global:ReportFile -Append
    Write-Output $txtTotalVMsFound

    $txtAlreadyRegistered = "VMs Already registered: $($Global:AlreadyRegistered)"
    Write-Output $txtAlreadyRegistered | Out-File $Global:ReportFile -Append
    Write-Output $txtAlreadyRegistered

    #display success
    $txtSuccessful = "Number of VMs registered successfully: $($Global:RegisteredVMs.Count)"
    show-VMDetailsInReport -Message $txtSuccessful -VMList $Global:RegisteredVMs

    #display failure
    if ($Global:FailedVMs.Count -gt 0) {
        $txtFailed = "Number of VMs failed to register due to error: $($Global:FailedVMs.Count)"
        show-VMDetailsInReport -Message $txtFailed -VMList $Global:FailedVMs
    }

    #display VMs not tried
    if ($Global:UntriedVMs.Count -gt 0) {
        $txtNotRunning = "Number of VMs skipped as VM or the guest agent on VM is not running: $($Global:UntriedVMs.Count)"
        show-VMDetailsInReport -Message $txtNotRunning -VMList $Global:UntriedVMs
    }

    #display VMs skipped
    if ($Global:SkippedVMs.Count -gt 0) {
        $txtNotSql = "Number of VMs skipped as they are not running SQL Server On Windows: $($Global:SkippedVMs.Count)"
        show-VMDetailsInReport -Message $txtNotSql -VMList $Global:SkippedVMs
    }

    Write-Host
    Write-Host "Please find the detailed report in file $($Global:ReportFile)"
    if (($Global:FailedVMs.count -gt 0) -or ($Global:UntriedVMs.count -gt 0) -or ($Global:SubscriptionsFailedToRegister.count -gt 0) -or ($Global:SubscriptionsFailedToConnect.count -gt 0)) {
        Write-Host "Please find the error details in file $($Global:LogFile)"
    }
    new-DashSeperator
}

<#
    .SYNOPSIS
    Write Details of VM to the report file

    .PARAMETER Message
    Message to be written

    .PARAMETER VMList
    List of VMs
#>
function show-VMDetailsInReport(
    [string]
    $Message,
    [System.Collections.ArrayList]
    $VMList
) {
    Write-Output $Message | Out-File $Global:ReportFile -Append
    Write-Output $Message
    new-ReportHelper -VmArray $VMList | Out-File $Global:ReportFile -Append
}

<#
    .SYNOPSIS
    Registers VMs in a given subscription

    .PARAMETER ErrorMessage
    Description of error

    .PARAMETER FailedSubList
    List of subscriptions
#>
function show-SubscriptionListForError(
    [string]
    $ErrorMessage,
    [System.Collections.ArrayList]
    $FailedSubList
) {
    $txtSubscription = "Subscription"
    $txtSubSeparator = "------------"
    Write-Output $ErrorMessage | Out-File $Global:ReportFile -Append
    Write-Output $ErrorMessage
    Write-Output $txtSubscription | Out-File $Global:ReportFile -Append
    Write-Output $txtSubSeparator | Out-File $Global:ReportFile -Append
    Write-Output $FailedSubList | Out-File $Global:ReportFile -Append
    Write-Output `n | Out-File $Global:ReportFile -Append
}

<#
    .SYNOPSIS
    Helper to Generate the report
#>
function new-ReportHelper(
    [System.Collections.ArrayList]
    $VmArray
) {
    $outputObjectTemplate = New-Object -TypeName psobject
    $outputObjectTemplate | Add-Member -MemberType NoteProperty -Name Subscription -Value $null
    $outputObjectTemplate | Add-Member -MemberType NoteProperty -Name ResourceGroup -Value $null
    $outputObjectTemplate | Add-Member -MemberType NoteProperty -Name VmName -Value $null

    $outputObjectList = [System.Collections.ArrayList]@()

    foreach ($vm in $VmArray) {
        $outputObject = $outputObjectTemplate | Select-Object *
        $outputObject.Subscription = $vm.Id.Split("/")[2]
        $outputObject.ResourceGroup = $vm.ResourceGroupName
        $outputObject.VmName = $vm.Name
        $tmp = $outputObjectList.Add($outputObject)
    }

    $outputObjectList | Format-Table -AutoSize
}

<#
    .SYNOPSIS
    Successfully connect to subscription

    .PARAMETER Subscription
    Subscription for searching the VM

    .PARAMETER Credential
    Credential to connect to subscription

    .OUTPUTS
    System.Boolean true if successfully connected and RP is registered, else false
#>
function assert-Subscription(
    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string]
    $Subscription,
    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    $Credential
) {
    #connect to the subscription
    $Global:Error.clear()
    $tmp = Connect-AzAccount -Subscription $Subscription -Credential $Credential -ErrorAction SilentlyContinue
    if ($Global:Error) {
        $connectionError = $Global:Error[0]
        $errorMessage = "$($Subscription), $($connectionError[0].Exception.Message)"
        Write-Output $errorMessage | Out-File $Global:LogFile -Append
        $tmp = $Global:SubscriptionsFailedToConnect.Add($Subscription)
        return $false
    }

    # register Subscription with SQL VM RP
    $registration = Get-AzResourceProvider -ProviderNamespace Microsoft.SqlVirtualMachine -ErrorAction SilentlyContinue
    if ((!$registration) -or ($registration[0].RegistrationState -ne 'Registered')) {
        # try register subscription to the SqlVirtualMachine
        $register = Register-AzResourceProvider -ProviderNamespace Microsoft.SqlVirtualMachine -ErrorAction SilentlyContinue
        if ((!$register) -or ($register.RegistrationState -ne 'Registering')) {
            $errorMessage = "$($Subscription), Subscription $($Subscription) should be registered to 'Microsoft.SqlVirtualMachine'. Steps to register can be found here: https://docs.microsoft.com/azure/azure-resource-manager/resource-manager-supported-services. This registration may take around 5 mins to propagate."
        }
        else {
            $errorMessage = "$($Subscription), Subscription $($Subscription) is registering to 'Microsoft.SqlVirtualMachine'. This registration may take around 5 mins to propagate. Run the script again for this subscription."
        }
        Write-Output $errorMessage | Out-File $Global:LogFile -Append
        $tmp = $Global:SubscriptionsFailedToRegister.Add($Subscription)
        return $false
    }
    return $true
}

<#
    .SYNOPSIS
    Registers VMs in a given subscription

    .PARAMETER Subscription
    Subscription for searching the VM

    .PARAMETER Credential
    Credential to connect to subscription

    .PARAMETER ResourceGroupName
    Name of the resourceGroup which needs to be searched for VMs

    .PARAMETER Name
    Name of the VM which is to be registered
#>
function register-SqlVMForSubscription (
    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string]
    $Subscription,
    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    $Credential,
    [string] $ResourceGroupName,
    [string] $Name) {
    [System.Collections.ArrayList]$vmList = getVmList -ResourceGroupName $ResourceGroupName -Name $Name
    #update vm count
    $Global:TotalVMs += $vmList.Count

    #Retry options
    Set-Variable MAX_RETRIES -option ReadOnly -value 3
    $retryCount = 0
    $retryIfRequired = $true

    # Try registering VMs and retry if required
    while (($retryCount -le $MAX_RETRIES) -and ($vmList.Count -gt 0)) {
        if ($retryCount -gt 0) {
            [int]$percent = ($retryCount * 100) / $MAX_RETRIES
            Write-Progress -Activity "Retrying registration" -Status "$percent% Complete:" -PercentComplete $percent -CurrentOperation "Retrying" -Id 2;
        }
        $retryCount++
        if ($retryCount -eq $MAX_RETRIES) {
            $retryIfRequired = $false 
        }
        [System.Collections.ArrayList]$vmList = createSqlVmFromList -VMList $vmList -RetryIfRequired $retryIfRequired
        if (($vmList.Count -eq 0) -or ($retryCount -eq $MAX_RETRIES )) {
            Write-Progress -Activity "Retrying registration" -Status "100% Complete:" -PercentComplete 100 -CurrentOperation "Retrying" -Completed -Id 2;
        }
    }
}

<#
    .SYNOPSIS
    Given a list of VMs, create SQL VMs

    .PARAMETER VMList
    List of Compute VMs for which SQL VM is to be created

    .PARAMETER RetryIfRequired
    Flag to specify if resource creation needs to be retried

    .OUTPUTS
    System.Collections.ArrayList List of VMs whose creation failed with retryable errors
#>
function createSqlVmFromList(
    [ValidateNotNullOrEmpty()]
    [array]
    $VMList,
    [bool]
    $RetryIfRequired = $false) {
    $retryableVMs = [System.Collections.ArrayList]@()
    [Int32]$numberOfVMs = $VMList.Count
    $completed = 0
    Write-Progress -Activity "Register SQL VM" -Status "0% Complete:" -PercentComplete 0 -CurrentOperation "RegisteringVMs" -Id 3
    # for each vm in the list try registering to RP
    foreach ($vm in $VMList) {
        # writeprogress of the loop
        [int]$percent = ($completed * 100) / $numberOfVMs
        Write-Progress -Activity "Register SQL VM $($completed+1)/$($VMList.count)" -Status "$percent% Complete:" -PercentComplete $percent -CurrentOperation "RegisteringVMs" -Id 3

        $name = $vm.Name
        $resourceGroupName = $vm.ResourceGroupName
        $location = $vm.Location
        $sqlVm = Get-AzSqlVM -Name $name -ResourceGroupName $resourceGroupName -ErrorAction Ignore
        
        # If already registered
        if ($sqlVm) {
            # Remove Sql VM and continue if the registration failed from the script
            if (($sqlVm.Sku -eq 'Unknown') -and (!$sqlVm.Offer)) {
                #remove sql vm successfully else log
                $isSqlVmRemoved = assert-RemoveSqlVmWithoutError -VmObject $vm
                if (!$isSqlVmRemoved) {
                    $completed++
                    continue
                }
            }
            else {
                # skip the VM if already successfully registered
                $Global:AlreadyRegistered++
                $completed++
                continue
            }
        }

        $SqlManagementType = "LightWeight"
        $LicenseType = getLicenseType -VmObject $vm

        $tmp = $Global:Error.Clear()
        $tmp = New-AzSqlVM -Name $name -ResourceGroupName $resourceGroupName -Location $location `
            -SqlManagementType $SqlManagementType  -LicenseType $LicenseType -ErrorAction SilentlyContinue

        # try re-registering if the error was due to Web, Express or Developer registering as AHUB
        if ($Global:Error) {
            if (isUnableToRegisterAsAHUB -ErrorObject $Global:Error[0]) {
                $tmp = handleError -ErrorObject $Global:Error[0] -VmObject $vm
                $tmp = $Global:Error.Clear()
                $LicenseType = 'PAYG'
                $tmp = New-AzSqlVM -Name $name -ResourceGroupName $resourceGroupName -Location $location `
                    -SqlManagementType $SqlManagementType  -LicenseType $LicenseType -ErrorAction SilentlyContinue
            }
        }

        if ($Global:Error) {
            $LastError = $Global:Error[0]
            $isRetryable = isRetryableError -ErrorObject $LastError

            #Add the vm to the retry list if error is retryable
            if ($isRetryable -and $RetryIfRequired) {
                $sqlVm = Get-AzSqlVM -Name $name -ResourceGroupName $resourceGroupName -ErrorAction Ignore
                if ($sqlVm) {
                    $isSqlVmRemoved = assert-RemoveSqlVmWithoutError -VmObject $vm
                    if (!$isSqlVmRemoved) {
                        $completed++
                        continue
                    }
                }
                $tmp = $retryableVMs.Add($vm)
            }
            else {
                $tmp = handleError -ErrorObject $LastError -VmObject $vm
            }
        }
        else {
            $tmp = $Global:RegisteredVMs.Add($vm)
        }
        $completed++
    }
    Write-Progress -Activity "Register SQL VM" -Completed -CurrentOperation "RegisteringVMs" -Id 3
    return , $retryableVMs;
}

<#
    .SYNOPSIS
    Remove Sql VM and handle errors

    .PARAMETER VmObject
    VmObject for the SQL VM to be removed

    .OUTPUTS
    System.Boolean True if SQL VM was removed successfully else False
#>
function assert-RemoveSqlVmWithoutError(
    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    $VmObject
) {
    $Global:Error.Clear()
    $tmp = Remove-AzSqlVM -Name $VmObject.Name -ResourceGroupName $VmObject.ResourceGroupName -ErrorAction SilentlyContinue
    if ($Global:Error) {
        $tmp = handleError -ErrorObject $Global:Error[0] -VmObject $VmObject
        return $false
    }
    return $true
}

Próximas etapas

Para obter mais informações, consulte os seguintes artigos:

Registrar várias VMs do SQL no Azure com a extensão do agente IaaS do SQL

Esta página é útil?

Visão geral

Pré-requisitos

Introdução

Criar o script

Importar o script

Conectar-se ao Azure

Todas as VMs em uma lista de assinaturas

Todas as VMs em uma assinatura única

Todas as VMs em vários grupos de recursos

Todas as VMs em um grupo de recursos

VMs específicas em um grupo de recursos único

Uma VM específica

Descrição da saída

Relatório

Log

Comentários

Script completo

Próximas etapas