SqlEndpointPermission: Should Be Cluster Aware

[randomnote1]

Details of the scenario you tried and the problem that is occurring: When the xSQLServerEndpointPermission resource is applied to all of the nodes in a Failover Cluster Instance (FCI), all of the nodes will connect to the instance and ensure the desired state is true even if the node is not currently hosting the instance.

The DSC configuration that is using the resource (as detailed as possible): When the following configuration is applied to two or more nodes in a FCI.

xSQLServerEndpointPermission SQLConfigureEndpointPermission
{
    Ensure       = 'Present'
    NodeName     = $Node.NodeName
    InstanceName = $Node.SqlInstanceName
    Name         = 'DefaultMirrorEndpoint'
    Principal    = $SqlServiceCredential.UserName
    Permission   = 'CONNECT'

    PsDscRunAsCredential = $SysAdminAccount
}

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running: Any

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running: Any

Version of the DSC module you're using, or 'dev' if you're using current dev branch: Any

[randomnote1]

The resource should evaluate if the current node is actively hosting the SQL instance. If it is not hosting the instance, it should write a verbose message stating that it does not own the instance, and then silently exit.

[randomnote1]

To resolve this issue, a parameter called ProcessOnlyOnActiveNode should be created on the Set-TargetResource and Test-TargetResource functions.

The Get-TargetResource function will execute the helper function Test-ActiveNode to determine if the current node is actively hosting the SQL instance and return the result via the read-only property IsActiveNode.

The Test-TargetResource function will use the value of ProcessOnlyOnActiveNode and IsActiveNode to determine if it should continue. If ProcessOnlyOnActiveNode is $true and IsActiveNode is $false, Test-TargetResource will return $true.

I put together this gist as a guide to updating the resource.

Also, see the xSQLServerAlwaysOnAvailabilityGroup resource for a completed example.