RE: Zero DataLoss

If you are going to prioritize data protection (a.k.a. true ZERO data loss), then you must do so regardless of the impact on availability or performance of the database service. If zero data loss is really the goal, then the other considerations must be subordinate.

That is why MAX_PROTECTION is rarely used in real-life. Very, very few organizations are willing to subordinate service availability. MAX_PROTECTION requires that if the standby is down, then the primary must be down too, which is absolutely what is required for maximum data protection. If a pending transaction cannot be protected, then it cannot be permitted to commit. This is not a limitation or a compromise, this is simply purity of vision.

It's almost like the old saying about three choices of good, fast, and cheap, except in this situation you only choose any one. There is always a trade-off.

The original poster's opening sentence about "zero data loss availability" shows fundamental confusion, because there is no such thing as data protection and availability with equal priority. Either "zero data loss" is the goal, or "availability" is the goal.

The original poster's organization has clearly chosen against "zero data loss" in prioritizing either availability or performance above. They should listen to their own decision, and realize that the organization is implicitly prioritizing performance over data protection and availability.

There is no judgement here, it is simply a fact. zero data loss is not possible unless it is the priority.

All that being said, MAX_AVAILABILITY does minimize the possibility of data loss substantially, and with the inclusion of a FarSync instance, can also greatly minimize impact on performance. MAX_AVAILABILITY is the mode that most closely approaches the ideal of meeting all three priorities, but still it requires dedication to service availability as the top priority, making data protection and performance subordinate. So by choosing MAX_AVAILABILITY, be clear that there must be a negative impact on data protection (i.e. RPO > 0) and on application performance, albeit minimized. Likewise, choosing MAX_PERFORMANCE must be accompanied by acceptance of a significant negative impact on data protection as well as service availability.

Choose one of the three modes, and understand all the implications of that choice. Also, understand what must be improved infrastructurally in order to adhere to the chosen mode. Nobody uses MAX_AVAILABILITY or MAX_PROTECTION on the "cheap".

Once you see it, you can't unsee it.

Hope this helps...

-Tim

On 11/9/2022 7:04 AM, Clay Jackson (Clay.Jackson) wrote:

As usual, MWF hit the relevant points, except perhaps not standing in the predicted meteor impact zone😊. Also consider these points:

For ANY zero data loss system it’s possible to come up with a scenario where (committed) data will be lost.

Attempting to achieve zero data loss can be an infinite resource (time and money) sink, so one should ALWAYS consider things like the cost and probability of that “last bit” of data actually being “lost”.