Introduction

Hey there, fellow database administrators! If you’re like me, you’re always looking for ways to improve the availability and resilience of your SQL Server databases. One powerful tool in our arsenal is the Availability Group (AG) feature. But what if you want to use AGs without the complexity of a Windows Server Failover Cluster (WSFC)? Enter clusterless AGs, introduced in SQL Server 2017. They allow you to create readable secondaries and disaster recovery (DR) nodes without the overhead of a cluster. Sounds great, right? Well, as with most things in our world, there are some challenges and trade-offs to consider. In this article, I’ll share my experiences supporting clusterless AGs and highlight the key pain points you need to be aware of. By the end, you’ll have a solid understanding of when and how to leverage this feature effectively. Let’s dive in!

Challenges with Readable Secondaries

One of the primary use cases for clusterless AGs is creating readable secondary replicas to offload reporting and analytics workloads from the primary. However, there are a few gotchas to keep in mind:

1. Latency: Since the secondary replica is asynchronously updated from the primary, there will always be some amount of data latency. This means queries on the secondary may return stale data. Be sure to set proper expectations with your users and monitor the actual lag using DMVs like sys.dm_hadr_database_replica_states.
2. Schema Changes: Unlike a normal AG with automatic failover, schema changes are not automatically propagated to clusterless secondaries. You’ll need to manually apply any DDL statements on each replica. I recommend using database projects in SSDT to help manage schema consistency.
3. Read-only Routing: Clusterless AGs do not support the read-only routing feature that allows the primary to automatically redirect read-only connections to a secondary. You’ll need to update application connection strings to point directly to the secondary replica(s) for read-only queries.

Disaster Recovery Considerations

Another key benefit of clusterless AGs is the ability to create a DR replica in a separate site or cloud region without needing a separate WSFC. However, there are some limitations and trade-offs to consider:

• Manual Failover: Clusterless AGs do not support automatic failover. In the event of a disaster, you’ll need to manually fail over to the DR replica using T-SQL or PowerShell commands. Be sure to document and practice your DR runbooks regularly!
• No Failback: Once you fail over to the DR replica, it becomes the new primary. There is no built-in way to fail back to the original primary once it comes back online. You’d need to manually re-establish the AG in the opposite direction.
• Data Loss Risk: Since the DR replica is typically asynchronous, there is a risk of data loss if the primary fails before all transactions are replicated. Be sure to monitor the send queue size and consider using manual seeding to initialize the DR replica if the databases are very large.

Conclusion

Clusterless AGs can be a valuable tool for creating readable secondaries and DR replicas without the full overhead of a WSFC. However, as we’ve seen, there are some significant challenges and limitations to consider. My advice is to carefully evaluate your use case and SLAs to determine if clusterless AGs are the right fit. In some cases, a traditional AG with sync commit and automatic failover may be worth the extra complexity.

I hope this article has given you a better understanding of the key pain points and considerations around clusterless AGs. Remember, our job as DBAs is all about managing trade-offs to meet the business requirements. With the right planning and design, clusterless AGs can be a powerful addition to your SQL Server HA/DR toolkit. Feel free to reach out if you have any other questions!