SqlServerDsc: Resources Should Be Cluster Aware

Details of the scenario you tried and the problem that is occurring:
When the SQL Instance is an FCI and the configuration is pushed to every node of the FCI, each of the resources that reach into the Instance execute multiple times per "cycle".

For example, if I have a 2-node FCI and have xSQLServerMemory defined, both nodes will reach into the instance and set the memory regardless if it is the owner node. This could result in the server memory being configured twice every 15 minutes.

In the case where new servers are being added to the FCI that have a different memory configuration, this will result in the new node's configuration fighting with the old nodes causing the memory configuration to flip-flop between the correct memory allocations for each server. This scenario has been observed in a real-life environment.

I propose that the resources that do work inside of the instance only do work if the current node is hosting the instance. I realize the amount of work that will have to go into making this change, so I believe we should target the high-impact resources first. This includes, but is not limited to:

• xSQLServerMemory
• xSQLServerAlwaysOnAvailabilityGroup
• xSQLServerAlwaysOnAvailabilityGroupDatabaseMembership.

The DSC configuration that is using the resource (as detailed as possible):
A failover cluster instance (FCI) with two or more nodes.

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:
All

Start by reviewing the xSQLServerMemory, xSQLServerAlwaysOnAvailabilityGroup, and xSQLServerAlwaysOnAvailabilityGroupDatabaseMembership resources described in the issue. Determine how each resource identifies the FCI owner node; done means instance-level work runs only on the node currently hosting the instance, with behavior covered for the listed high-impact resources.