dsccommunity
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
Could you please split this issue up for each resource so it's easier to close issues. I can create a project to track this as group if necessary. When you split each resource into a separate issue, please provide context how this is a problem per resource.
For xSQLServerMemory, I only see this happening if the resource are used on all nodes with dynamic allocation. If this was used with fixed size this problem wouldn't occur since you would want to set this to the server with the lowest memory. Or do you mean that the real-life scenario there is two servers with different memory configuration and each is set to different fixed sizes?
Will do. Sorry haven't gotten to it yet, I've been under the weather the past couple days.
Here are the resources I've identified that definitely need to be made cluster aware.
- xSQLServerAlwaysOnAvailabilityGroup: #868
- xSQLServerAlwaysOnAvailabilityGroupDatabaseMembership: #869
- xSQLServerAlwaysOnAvailabilityGroupReplica: #870
- xSQLServerAvailabilityGroupListener: #871
- xSQLServerConfiguration: #872
- xSQLServerDatabase: #873
- xSQLServerDatabaseOwner: #874
- xSQLServerDatabasePermission: #875
- xSQLServerDatabaseRecoveryModel: #876
- xSQLServerDatabaseRole: #877
- xSQLServerEndpoint: #878
- xSQLServerEndpointPermission: #879
- xSQLServerEndpointState: #880
- xSQLServerLogin: #881
- xSQLServerMaxDop: #882
- xSQLServerMemory: #867
- xSQLServerPermission: #883
- xSQLServerRole: #884
- xSQLServerReplication: #885
- xSQLServerScript: #886
@randomnote1 No worries. No hurry. Thanks for starting to do issues. I added a project (Kanban board) for this as well, to easier track the progress.
@randomnote1 Can I trouble you to write about the concept of parameter ProcessOnlyOnActiveNode in each of the issues you created (same text in each suffice) so that others contributors can help and use the same approach?
@johlju, All of the issues should be updated. I don't think I missed any.
@randomnote1 Thanks for that! And you went above and beyond and created a gist as well! 🙇
