SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

AlwaysOn Data Synchronization in Synchronous and Asynchronous Mode

When you are working on the AlwaysOn feature, it is crucial to understand how the AlwaysOn commit process works. This blog will walk you through how a synchronous and Asynchronous commit process works in AlwaysOn.

Data Synchronization in Synchronous mode 

  • Primary replica is generating transaction log blocks. The secondary replica establishes a valid connection to the primary replica using the configured mirroring endpoints. The secondary initiates a request to the primary, asking for the log blocks to be shipped. Log Block is a contiguous, chunk of memory (often 60K and disk sector size aligned), maintained by the Log Manager. The secondary and primary will negotiate the proper LSN staring point and other information necessary.
  • Primary replica’s log cache is being filled with these log blocks.
  • When the log cache becomes full or the primary replica issues a commit operation, the log blocks from the log buffer are flushed to the log disk to make it persistent. Since we are running in an AlwaysOn Availability Group configuration, when the log cache is being flushed to the log disk on the primary replica, those log blocks also get copied to log pool.
  • The log blocks in the log pool are read by a thread called log capture and its job is to read the log blocks from log pool and send them to the secondary replica. If there are multiple secondary replicas, there is one log capture thread for each of those replicas which ensures that the log blocks are sent across multiple replicas in parallel.
  • The log content gets compressed and encrypted before being sent over to the secondary replicas.
  • There is a thread called log receive that is running on the secondary replica. It receives the log blocks from the network and it starts writing to the log cache.
  • While the log blocks are being written to the log cache, there’s a redo thread that is always running on the secondary replica. It is reading those log blocks and applying those changes to the data pages and the index pages in the database on the secondary to bring it up to date with whatever has happened on the primary replica. When the log cache on the secondary replica becomes full, or it receives a commit log record, it hardens the content of log cache onto the log disk on the secondary replica.
  • If the secondary replica is configured to run in synchronous mode, it will send an acknowledgement on the commit to the primary node indicating that it has hardened the transaction, and so it is safe to tell the user that the transaction is committed. And because the log has been hardened on the secondary, there is a guarantee that in case there is a failover, there’s no data loss.
  • While this is going on, the redo thread is applying those transaction log blocks. And it is running independently of how log blocks are being generated on the secondary or being copied and persisted. If the redo thread is running few minutes behind, those log blocks may not be available in the log cache. In that case, it will pick up those log blocks from the log disk, and that is what is shown in the dotted line on the right side of the slide.

Data Synchronization in Asynchronous mode

  • Primary replica is generating transaction log blocks. The secondary initiates a request to the primary, asking for the log blocks to be shipped. 
  • Primary’s log cache is being filled with these log blocks.
  • When the log cache becomes full or the primary replica issues a commit operation, the transaction log blocks from the log buffer are flushed to the log disk to make it persistent. Since we are running in an AlwaysOn Availability Group configuration, when the log cache is being flushed to the log disk on the primary replica, those log blocks also get copied to log pool.
  • If all secondary replicas are in an asynchronous availability mode, the success of this step is good enough to send an acknowledge message of a successful commit back to the application when the I/O to the local transaction log is successfully executed.
  • The log blocks in the log pool are read by a thread called log capture.
  • The log content gets compressed and encrypted before being sent over to the secondary replicas.
  • There is a thread called log receive that is running on the secondary replica. It receives the log blocks from the network and it starts writing to the log cache.
  • While the log blocks are being written to the log cache, there’s a redo thread that is always running on the secondary replica. It is reading those log blocks and applying those changes to the data pages and the index pages in the database on the secondary to bring it up to date with whatever has happened on the primary.
  • While this is going on, the redo thread is applying those transaction log blocks. And it is running independently of how log blocks are being generated on the secondary or being copied and persisted. If the redo thread is running few minutes behind, those log blocks may not be available in the log cache. In that case, it will pick up those log blocks from the log disk, and that is what is shown in the dotted line on the right side of the slide.

Reference Link: https://bit.ly/2Logu4T

The post AlwaysOn Data Synchronization in Synchronous and Asynchronous Mode appeared first on .

SQL Geek

Dharmendra is a SQL Server/Microsoft Data Platform professional with over eight years of experience. He enjoys helping others in the SQL Server community and does this by contributing on blogs, speaking at several SQL events. His passion and focus is to explore and share more and more on SQL Server.

Comments

Leave a comment on the original post [www.dharmendrakeshari.com, opens in a new window]

Loading comments...