How To Find A Bounded-buffer Schedule For Synchronous Data Flow

This article describes the information synchronization process on SQL Server Ever On Availability Groups in both Synchronous, and Asynchronous data commit mode.

Loftier data availability is an important aspect of every DBA's life. We demand to minimize and mitigate the issues related to database unavailability.

In the production environment, we want to achieve RPO (Maximum allowed downtime) and RTO (Maximum adequate data loss) goals of a production database service. Information should be available to business round the clock to avoid any business concern loss.

In the following table, nosotros tin get a glimpse of the maximum allowed downtime based on availability percentage. We get a very strict maximum of downtime per year every bit we movement towards a strict availability pct.

Availability pct	Maximum downtime per twelvemonth
99%	iii.65 Days
99.9%	8.77 Hours
99.99%	52.60 Minutes
99.999%	5.26 Minutes
99.9999%	31.56 seconds
99.99999%	3.sixteen seconds
99.999999%	315.58 Milliseconds

In SQL Server, we can achieve loftier availability using SQL Server Ever On Availability Groups characteristic. Let'due south get a quick overview of SQL Server Ever on Availability Groups before nosotros go in deeper.

In the following screenshot, we accept three SQL instances participating in SQL Server Always On.

SQL Server Always On Availability Groups Example

Nosotros tin accept a Principal instance and multiple secondary instances (depending upon SQL Version and Edition).

Few important terms of SQL Server Always on Availability Groups are every bit follows.

Availability group: Information technology is a logical group of user databases that should failover together on the secondary replica
Replica: Each availability grouping should contain ii or more participating example. Each participating example is known equally Replica in SQL Server Always On
Primary Replica: It hosts the principal database and available for all users for read-write connections
Secondary Replica: It hosts a copy of a database from the principal replica. It works equally a failover target in case of any issues with the primary replica

In the SQL Server Always On, Primary replica sends transaction log to secondary databases for all databases defined in the availability group.

Nosotros can accept two types of data commit modes.

Asynchronous-commit mode: In this mode, Main replica sends the transaction log blocks to a secondary replica, but it does non expect for the acknowledgement for transaction commit. Information technology is suitable for disaster recovery solutions
Synchronous-commit fashion: In synchronous-commit mode, the primary replica waits for the transaction commit from a secondary replica. Once it receives the confirmation, SQL Server confirms to the client

In the following screenshot, we can see an overview of two node SQL Server Always On Availability groups

SQL Server Always On Availability Groups Example

We might be interested to know the internals of SQL Server Always On. In the next section, let'southward come across how really information synchronization happens in both Synchronous and Asynchronous information commit way.

Data Synchronization in Synchronous data commit mode

In the following image, we tin run into the overall working of information synchronization in Synchronous commit.

Data Synchronization in Synchronous data commit mode

Let's go through in item with each step on the higher up diagram.

A client connects to the principal replica (either using a listener or SQL example proper noun) and problems a DML transaction
In Primary replica, transaction log block is generated. These transaction log records get stored in the log enshroud of the master replica
SQL Server regular performs an automatic checkpoint. It flushes log data into the disk for a master replica. At the same time, it besides copies the log records to Log Pool
In this pace, a thread Log capture reads Log puddle data. The Log Capture process continuously scans the logs from the Log Capture and sends to each secondary replica. If we have multiple secondary replicas, we accept private threads for each replica, and its responsibleness is to send a log cake for the respective replica
Note : SQL Server encrypts log records before sending information technology to secondary replicas.
In the secondary replica, Log Receive gets the log records from the primary replica and writes to Log enshroud. This process is repeated on each secondary replica participating in synchronous-commit mode
On each secondary replica, Redo thread exists, and information technology writes all changes mentioned in log records to the data page and index page. It flushes the log for hardening on secondary database log
As stated earlier, in synchronous data commit, primary replica waits for the acknowledgement from the secondary replica. At this stage, secondary replica sends an acknowledgement that transaction hardening is completed on secondary
In one case Primary replica, receives an acknowledgement from the secondary replica, it sends the transaction completion bulletin to the client
The secondary replica besides contains a redo thread, and information technology is independent of the log block process in SQL Server Always on. Redo threads reads the logs from log enshroud. There might be a filibuster in processing by redo thread and log records might not be available in log enshroud because it is already hardened to deejay. In this example, redo thread read log blocks from the log disk

Data Synchronization in Asynchronous data commit mode

Nosotros tin can empathize data synchronization in Asynchronous data commits manner with the following image.

Data Synchronization in Asynchronous data commit mode

A customer connects to the principal replica (either using a listener or SQL instance name) and issues a DML transaction
In Primary replica, transaction log block is generated. These transaction log records get stored in the log cache of the primary replica
SQL Server regular performs an automatic checkpoint. It flushes log data into the disk for a primary replica. At the same fourth dimension, it also copies the log records to Log Puddle
In Asynchronous data commit way, the chief replica does not wait for the acknowledgement from participating secondary replica. It sends the acknowledgement to the client for transaction commit
In the secondary replica, Log Receive gets the log records from the master replica and writes to Log enshroud. This process is repeated on each secondary replica participating in asynchronous-commit mode
On each secondary replica, Redo thread exists, and it writes all changes mentioned in log records to the data page and index page. Information technology flushes the log for hardening on secondary database log
The secondary replica as well contains a redo thread, and information technology is independent of the log cake procedure in SQL E'er On. Redo threads reads the logs from log cache. In that location might exist a delay in processing by redo thread and log records might not be available in log enshroud because it is already hardened to disk. In this case, redo thread read log blocks from the log disk

Synchronized AlwaysON with Secondary replica down

Suppose we have two synchronous node information commit AlwaysOn in our environment. As stated before, Chief replica waits for the acknowledgement from Secondary replica and then only sends an acknowledgement. If Secondary AG case is downwards, SQL Server will non exist able to deliver transaction log records to the secondary replica.

Recollect about it. Nosotros can get a few questions such every bit post-obit.

What will be the transaction beliefs in this case?
Exercise users get transaction commit bulletin?

We tin can view this scenario in the following screenshot.

SQL Server Always On Availability Groups - Windows Failover Clustering

In SQL Server Availability groups checks the condition of each replica based on a parameter Session-Timeout. By default, its value is ten seconds. Information technology shows that the Primary replica waits for ten seconds for a ping response. If it does not receive whatever response from the secondary replica, SQL Server alter Synchronous data commit to Asynchronous data commits as a temporary measure out. Due to this change, users volition not confront any issues while executing DML. Users will receive commit acknowledgement every bit soon as it gets committed in Primary replica.

In the following screenshot, y'all tin can come across this scenario.

Once the secondary replica is available and connects with the Primary replica, it once more starts Synchronous information commit. SQL Server maintains the log entries until the time secondary replica becomes available. In one case it reconnects with Secondary replica, it sends all log blocks as per the usual procedure. It increases the transaction log size in Primary replica until the secondary replica is unavailable.
If you remove the Secondary database from the availability grouping, SQL Server does not hold any transaction log records. If it takes longer to fix problems with the secondary replica, information technology is good to remove the replica from the availability group.

Determination

In this article, we explored the internals of data synchronization for AlwaysOn availability groups. Information technology will help to sympathize the stop-to-end data menstruation. We will cover more on SQL Server Always on Availability Groups in upcoming articles.

Author
Recent Posts

Hullo! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, set up related issues, and Performance Tuning with over xiv years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of i of the biggest free online collections of manufactures on a single topic, with his l-office series on SQL Server E'er On Availability Groups.

Based on my contribution to the SQL Server customs, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (second Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Weblog: https://www.dbblogger.com
I am e'er interested in new challenges so if yous need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta