Search code examples
sql-serverazure-sql-databasescalingacid

Is Azure SQL database designed with read replicas ACID compliant


I am currently preparing for the interview and trying to figure out what is ACID compliant and what is not.

As I understand Azure SQL database with 1 write/read and multiple read replicas will be ACID compliant (in case C in ACID is considered as leaving database not in broken state and not as strong consistency) Yes we will have eventual consistency here, but isn’t it still ACID compliant?

With my current knowledge it looks like it will be compliant.


Solution

  • Every element of ACID can vary by degree, so the question is specious. You can't really say much more than that transactions in Azure SQL have ACID properties without getting into the weeds.

    For instance you can vary isolation from none (READ UNCOMMITTED) to lots (SERIALIZABLE / SNAPSHOT). Durability can be qualified by replication delay. etc. Consistency can be enforced through constraints but you have to apply them and you can bypass them if you want to.

    As far as your question goes, you can argue that replication delays do expose you to inconsistent reads if you bounce clients between replicas - but that rarely happens in practice in most systems except during failovers and even that is bounded by very short RPO timescales.

    I think whether or not you have replication configured is probably not that relevant to ACID - this is more relevant when dealing with questions about high availability, disaster recovery and business continuity.

    I would say any use of Azure SQL has "ACID compliant" transactions, because it's a fundamental property of the product architecture.