I've been reading about the sql azure scale out.
I suppose it makes senses to leverage the replicas hardware for read-only workloads.
But it got me wondering, which is actually doing more work? The primary or the replicas?
In particular it's not clear to me how expensive it is for the the replicas to 'catch up' on the transaction log from the primary.
If I understand correctly, if the primary executes a query like UPDATE dbo.LargeTable SET [SomeColumn] = 10
, then it would probably do an index scan to update all the rows, but the transaction log will actually contain one entry per row? That would mean the replica would need to do a key lookup for each row instead of an index scan, which would be a lot more expensive.
I'm sure my logic is wrong somewhere but I'd love to understand more about this.
Is this assumed that catching up on the primary is low cost (relative to the cost incurred by the primary to actually run the transaction)?
Also, are the physical pages of the replicas exactly identical to the primary at the byte level? (i.e. fragmentation is the same too) Or are there just logically the same?