sql-server-2014-alwayson-existing-vnet-and-ad VMExtensionProvisioningError 'CreateCluster'

[kjk129]

Template Name: sql-server-2014-alwayson-existing-vnet-and-ad

This template deployment is failing in this extension processing:
"Resource Microsoft.Compute/virtualMachines/extensions 'test-sql-1/CreateCluster' failed with message"

The error detail of the error is below and is related to xSqlEndpoint configuration section.

"Following are the first few: The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input. "

Issue Details

New-AzureRmResourceGroupDeployment : 4:47:00 PM - Resource Microsoft.Compute/virtualMachines/extensions 'test-sql-1/CreateCluster' failed with message '{ "status": "Failed", "error": { "code": "ResourceDeploymentFailure", "message": "The resource operation completed with terminal provisioning state 'Failed'.", "details": [ { "code": "VMExtensionProvisioningError", "message": "VM has reported a failure when processing extension 'CreateCluster'. Error message: \"DSC Configuration 'CreateFailoverCluster' completed with error(s). Following are the first few: The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input. The PowerShell DSC resource '[xSqlEndpoint]SqlAlwaysOnEndpoint' with SourceInfo 'C:\Packages\Plugins\Microsoft.Powershell.DSC\2.76.0.0\DSCWork\create-failover-cluster.ps1.0\CreateFailoverCluster.ps1::273::9::xSqlEndpoint' threw one or more non-terminating errors while running the Set-TargetResource functionality. These errors are logged to the ETW channel called Microsoft-Windows-DSC/Operational. Refer to this channel for more details. The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.\"." } ] } }'

[kjk129]

I've tracked this down this code at the beginning of the xSqlEndpoint.psm1 module. It seems that the "Microsoft.SqlServer.Management.Smo.Server" object returned from Get-SqlServer has a mismatch of properties from what the Set-SqlHadrEndpoint cmdlet is expecting. I'm still digging but this seems like a version mismatch of some sort between this object and the cmdlet.

$s = Get-SqlServer -InstanceName $InstanceName -Credential $SqlAdministratorCredential

try
{
    if (-not ($s.Endpoints | where { $_.Name -eq $Name }))
    {
        # TODO: use Microsoft.SqlServer.Management.Smo.Endpoint instead of
        #   SqlPs since the PS cmdlets don't support impersonation.
        Write-Verbose -Message "Creating database mirroring endpoint for SQL AlwaysOn ..."
        $endpoint = $s | New-SqlHadrEndpoint -Name $Name -Port $PortNumber
        $endpoint | Set-SqlHadrEndpoint -State 'Started'
    }
}

[vyunev]

Same issue here

[kjk129]

My workaround was to define the -path variable and pass it instead of piping the $s variable. This succeeded on the primary node for the SqlAlwaysOnEndpoint resource but failed on the secondary for the SqlSecondaryAlwaysOnEndpoint resource. I ended up using T-SQL to create the Endpoint on the secondary pulling out the code for the last 2 resource statements in CreateFailoverCluster.ps1 and manually executing that code. Despite a error in these, I was able to complete the deployment. This all seems to defeat the purpose of a template like this.

$path="SQLSERVER:\SQL\$InstanceName\Default"

try
{
    if (-not ($s.Endpoints | where { $_.Name -eq $Name }))
    {
        # TODO: use Microsoft.SqlServer.Management.Smo.Endpoint instead of
        #   SqlPs since the PS cmdlets don't support impersonation.
        Write-Verbose -Message "Creating database mirroring endpoint for SQL AlwaysOn ..."
        $endpoint = New-SqlHadrEndpoint -Name $Name -Port $PortNumber -Path $path
        $endpoint | Set-SqlHadrEndpoint -State 'Started'
    }
}