0

With MySQL slave/master, sync_binlog and innodb_flush_log_at_trx_commit control log and disk flushing, and are important to maintain consistency between master and slave (so that the slave can never be ahead of the master 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 master/slave consistency, and their performance impact?
| improve this question | | | | |
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 master/slave consistency, and their performance impact?

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

| improve this answer | | | | |
  • +1. One extra very specific question: when you disable mirroring (temporarily) is any and all overhead prevented; is the performance you get representative of having no mirroring/replication? – Halfgaar Oct 3 '18 at 9:16

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.