0

Lets say we have two node AlwaysON availability group setup and each node has an availability group as primary.

EG - Cluster Nodes - Node1, Node2

Availability Group AG1 is Primary on Node 1 and Availability Group AG2 is Primary on Node 2. Both AG's are in sync mode for HA.

What are the pros and cons of having this assuming that each node runs resource intensive Databases. One of my concerns is whether the AG will experience difficulty maintaining Sync mode, potentially switching internally to async mode if the respective secondary nodes can't keep up with committing the log sent workload because of the high workload caused by the primary databases on it.

EG AG1 on Node 1 not being able to be in sync with Node2 because of resource contention on Node 2 caused by primary AG(AG2) workload.

3 Answers 3

2

What are the pros and cons of having this assuming that each node runs resource intensive Databases.

I don't really see many/any Pros. You could argue that it's a type of "load balancing", but then you're screwed when/if there is any automatic failover or HA/DR event.

The Cons list is extremely long...

  • Potential licensing
  • Unexpected performance drops
  • Redo issues
  • HADR events taking a long time
  • Disconnected secondary replicas
  • Etc.

If you need to run like this, then it would be better to create a new replica or switch it to its own set of VMs.

1

If you maintain both nodes with active workloads, the first thing to keep in mind is that you have to pay the SQL Server licence for each node.

That said, each of the nodes should be able to support the workloads of the both AGs. This way, in the event of an automatic failover, the workloads will not suffer from a shortage of resources.

If you don't think its a good idea to keep both workload on the same node, a third node can be added to act as a secondary node for both AGs, without having to pay for the SQL server licence.

1

Configuring a multi-node Always On Availability Group (AG) where each node hosts a primary AG brings about a distinct set of advantages and disadvantages, particularly when managing databases with high resource demands. Let's delve into the potential benefits and drawbacks:

Benefits:

  1. Enhanced High Availability: By configuring AG1 and AG2 as primary on separate nodes, you guarantee high availability for both databases. In case of a node failure, the other node can seamlessly handle the workload.

  2. Efficient Load Balancing: Spreading out the primary AGs across multiple nodes aids in distributing the workload effectively. This proves advantageous when dealing with databases that have diverse usage patterns or resource needs.

  3. Enhanced Isolation: Each AG functions autonomously on its designated node, offering isolation in terms of resource utilization and potential issues. Any problems with one AG or node are less likely to affect the others.

Drawbacks:

  1. Resource Contention: As previously mentioned, databases that require a significant amount of resources on a single node (e.g., AG2 on Node 2) can result in contention for resources such as CPU, memory, disk I/O, and more. This can potentially impact the performance of the secondary replicas and cause synchronization delays or a switch to asynchronous mode.

  2. Synchronization Challenges: If the secondary replica (e.g., AG1 on Node 2) falls behind due to resource contention or other factors, it may struggle to keep up with the workload of the primary replica, resulting in synchronization issues. This can lead to data latency and potential data loss if asynchronous mode is enabled.

  3. Failover Considerations: Failover scenarios must be meticulously planned and tested to prevent exacerbating resource contention issues or causing performance degradation.

  4. Increased Complexity: Managing a multi-node AG setup with multiple primary replicas introduces complexity to the configuration, monitoring, and troubleshooting processes. It necessitates careful planning and monitoring to ensure optimal performance and high availability.

Mitigation Strategies:

  1. Resource Allocation: It is crucial to ensure that each node is adequately equipped with resources such as CPU, memory, and disk I/O to effectively handle the workload of both primary and secondary replicas. It is important to continuously monitor workload patterns and performance to make necessary adjustments in resource allocations as required.

  2. Monitoring and Alerting: Implementing robust monitoring and alerting systems is essential to proactively detect any resource contention issues, synchronization delays, or other performance-related problems. This enables timely intervention and resolution, minimizing the impact on the system's performance.

  3. Capacity Planning: Regularly reviewing and updating capacity planning is crucial to accommodate growth and ensure that the infrastructure can effectively support the workload demands. This helps avoid potential bottlenecks or performance issues due to insufficient resources.

  4. Optimization: This can be achieved through techniques such as indexing, query optimization, and other performance-tuning methods.

  5. Testing and Maintenance: Regularly testing failover scenarios and performing maintenance tasks, such as patching and updates, is essential to minimize the downtime risk and ensure the AG setup's smooth operation. This proactive approach helps in identifying and addressing any potential issues before they impact the system's availability.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.