5

Throughout MS documentation for AlwaysOn Availability Groups (AG) and AlwaysOn Failover Cluster Instances (FCI) I see the following pattern:

  1. FCI is for HA scenarios.
  2. An AG synchronous secondary replica, co-located with the primary, is for HA scenarios.
  3. An AG asynchronous secondary replica, in a different datacenter, is for DR scenarios.

Here is an example MS link discussing this.

Since option #1 and #2 are both for HA scenarios, how do I decide between them? If MS published costs and RPO/RTO metrics for both #1 and #2, it would be fairly easy to decide which I want.

Or perhaps there is a different way to understand the ROI differences between these options. For example, perhaps option #2 is best suited for VLDB's and option #1 is best suited for very high transaction volumes. I don't know.

So again, what is the selection criteria a DBA uses to choose between options #1 and #2?

To further complicate things, I know that options #1 and #2 can be combined! When is it wise to combine the two options? When is it pointless to combine the two options? I know when these options are combined, the AG no longer supports automatic fail-over. It's interesting trivia, but doesn't answer my question.

Incidentally, I intend on provisioning my final solution into Azure IaaS. If I use an Always On FCI, I will likely create the quasi-SAN using Storage Spaces Direct (S2D).

Update

I've found two articles that give a comparison. The first is MS docs, and the other is Choosing the Right Availability Tech. Both have a chart like this:

╔═════════════════════════════╦══════════════════════════╗
║ FCI                         ║ AG                       ║
╠═════════════════════════════╬══════════════════════════╣
║  * Server Level             ║  * Database Level        ║
╠═════════════════════════════╬══════════════════════════╣
║  * Requires shared storage  ║  * Uses direct           ║
║     (SAN or Storage Spaces  ║     attached storage     ║
║     Direct)                 ║                          ║
╠═════════════════════════════╬══════════════════════════╣
║  * RTO from 30              ║  * RTO typically less    ║
║     seconds to 20 minutes.  ║     than 30 seconds      ║
╠═════════════════════════════╬══════════════════════════╣
║  * RPO: no data loss.       ║  * RPO: ???              ║
╠═════════════════════════════╬══════════════════════════╣
║  * Only Passive Secondaries ║  * Active or Passive     ║
║                             ║     Secondaries          ║
╠═════════════════════════════╬══════════════════════════╣
║  * One SQL Server           ║  * Multiple SQL Server   ║
║     instance/license        ║     instances / licenses ║
╚═════════════════════════════╩══════════════════════════╝

The article didn't comment on AG RPO, but I read elsewhere that there is no data loss when recovering from a synchronous secondary replica. I don't know if that is true and I don't know what the RPO of asynchronous secondary replica might be. In Azure there is an AG resource that requires two domain controllers are created with the AG (regardless if you have your own domain controllers). I don't know if an Azure FCI has the same heavy-handed requirement.

Most significantly - I still don't know why it is valuable to combine the two techniques. I've only read that it "improves" or "maximizes" availability. That is a vague claim IMO.

More trivia: I also spotted a discussion suggesting that an FCI of two nodes should be avoided.

5

In general, FCI is more beneficial compared to AG because of various reasons already mentioned above. Of course, 2-node FCI is completely great but should not be built on top of storage spaces direct since S2D is known to be problematic in less than 4 nodes deployments (source).

For Azure, I would rather stick to a VSAN that is designed to work in 2 or 3 nodes scenarios like (example).

Here is a bit more information on FCI vs AG that you might find useful too

4

It depends on every client's requirement and need as to which is a better option out of the two (options 1 and 2) for HA purposes. Various criteria that is generally checked is provided below:

If it's a standard edition then you'll need to go with FCI (Option 1).

Go with AG (Synchronous - Option 2), if all of the below conditions satisfy:

  • Have Enterprise edition of SQL Server (Basic AG is also available in Standard edition since SQL Server 2016)
  • You require and have the storage capacity for storing multiple copies of your database files for additional level of safety. Storage is a single point of failure in FCI as it just has one set of data files as against multiple in AGs.
  • Check the network and disk latency to see if the total latency from Server A's disk to Server B' disk is within acceptable norms for your applications as the transactions will not return back to the application till it is committed at the Secondary (B) server.
  • Remember that Server level objects are not automatically synced from server A to Server B as the system Databases are not synced. So either you manually keep in sync all the server level objects between the participating servers or deploy an automated solutions which will keep syncing such server level objects between the 2 servers.
  • You've got enough expertise to deal with the operational issues of AG. AGs are much more complicated than FCIs and hence you need to be ready to troubleshoot any issues arising out of AGs especially performance ones.

Or

If you need to offload reporting queries (Or Backups) onto the secondary server to reduce load on the Primary Instance go with AG.

I’d either combine (1 and 3) or (2 and 3) for both HA and DR purposes wherein 1 or 2 would provide an HA solution and 3 would be a DR solution. So it is always better to combine 1&3 or 2&3 for a complete HA and DR strategy.

But am uncertain if there’s any benefit in combining 1 and 2.

  • So the short version is that AG's cost more in licensing and maintenance, but allow more replicas of the data and can offload reporting. I still wonder why MS puts so much effort to show how to combine the two. – Brent Arias Feb 1 '18 at 17:36
  • "I still wonder why MS puts so much effort to show how to combine the two", Do you mean FCI and AG(Sync) ? – Masood Hashim Feb 2 '18 at 8:24
  • 1
    You can use (Basic) AGs with the Standard Edition, and you can combine FCI with the Basic AG as well (with the given Limitations). – SwissCoder Feb 18 at 8:29

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.