0

first question here so please advise if I've missed anything.

I've read this question (AG of 2 replicas on 2 nodes in WSFC : is it possible to add 3rd replica to AG and NOT add 3rd machine WSFC? ) but it doesn't quite match my scenario so am asking my own one.

I have a 2-node FCI (SQL 2017 Enterprise) with the requisite underlying WSFC. This sits in DC1. I have been given a standalone node (also SQL 2017) in DC2. I've been asked to configure AG between the FCI and the standalone node.

We don't want/need to offload any reporting/read operations to the secondary replica, it's just for DR

It will be in asynchronous mode.

I can't add the standalone node to the WSFC because the storage for the FCI would not be available in a DR situation.

It is recognised that we will have to manually failover the AG if the situation arises and there will be some data loss if it happens as an unplanned event. RTO/RPO is under discussion currently.

So, my question(s) is/are 1/. I assume I would just follow instructions for setting up a clusterless AG configuration (read-scale but not make it readable?) and 2/. therefore, because no read-operations are going to be hitting the standalone node, then no listener is required?

Hopefully that all makes sense.

Thanks.

1

I think you're making this situation more complex than needed because one of your assumptions is incorrect:

I can't add the standalone node to the WSFC because the storage for the FCI would not be available in a DR situation.

You can still add the standalone instance to the WSFC, but you simply do not configure it as a possible owner of any of the FCI resources, including the shared disks.

The FCI resources (instance, disks, VNNs, IPs etc) are configured so they can be owned by any of the FCI nodes, and the AAG resources (VNNs, Listener, IPs etc) are able to be owned by the FCI or the standalone node.

The way to think about this is the WSFC provides clustering capabilities, but your components (shared disks) are isolated to the clustered resource (SQL FCI) only. This is how you can deploy multiple FCIs in an active-active configuration within a single WSFC - each FCI has its own dedicated disks that each node may or may not be able to own depending on the FCI configuration.

Adding a 3rd node to the WSFC simply allows that node to take advantage of clustering capabilities, but until you configure a clustered resource (AAG in this case), the 3rd node is essentially a silent member of the WSFC cluster. Once you set up the AAG, the components of that clustered resource can then be managed by the designated owners, in this case, the FCI or the DR node.

Microsoft has a whitepaper on this very subject. Its for SQL 2012, but the theory behind it is still valid.

2
  • Thank you for your answer. I have downloaded the whitepaper and seen there is a diagram on page 6 which is identical to what I am trying to achieve. Would my question (#2) re the listener still be valid in this configuration?
    – Ric
    Nov 11 '21 at 16:18
  • The listener is still required, otherwise when you failover in a DR scenario, all of your connection strings would need to be updated to point to the new primary replica.
    – HandyD
    Nov 15 '21 at 0:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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