0

With MySQL secondary/primary, sync_binlog and innodb_flush_log_at_trx_commit control log and disk flushing, and are important to maintain consistency between primary and secondary (so that the secondary can never be ahead of the primary in case of a crash). An admin is free to set this (and mess this up), for performance reasons for instance. I was wondering, how is this arranged in Microsoft SQL Server in mirroring or replication mode? Does it even have the same tunables?

Specific questions would be:

  1. Being more 'click-and-go', does SQL server set everything to safe values?
  2. What are equivalent settings, or settings one must know when dealing with primary/secondary consistency, and their performance impact?
4

Being more 'click-and-go', does SQL server set everything to safe values?

It's baked in to Availability Groups (physical) and Transactional Replciation (logical) that a replica cannot commit changes ahead of the primary.

In AGs transaction log records are streamed to the replicas as they are hardened on the primary, but on a failover, any transaction that failed to commit on the primary will be rolled back on the replicas.

In Transactional Replication changes are harvested from the transaction log on the primary (called the "Publisher"), but only committed transactions are sent to the Distribution database.

What are equivalent settings, or settings one must know when dealing with primary/secondary consistency, and their performance impact?

Since consistency is always guaranteed, the main thing is to understand the difference between Sync and Async replicas.

0

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.