Sql-server – Data Synchronization process in Database Mirroring and Alwayson Groups SQL Server

Without any HA like DBM or AG, will there be any log buffer present in SQL Server WAL mechanism?

As SQLAuthority Blog Here The book online definition of the LOGBUFFER seems to be very accurate. On the system where I faced this wait type, the log file (LDF) was put on the local disk, and the data files (MDF, NDF) were put on SanDrives. My client then was not familiar about how the file distribution was supposed to be. Once we moved the LDF to a faster drive, this wait type disappeared.

Is there any log buffer and log capture which also present in Database Mirroring, and Is that same as in Always on Groups?

Comparing to Database Mirroring (DBM), AlwaysOn required some serious rework in the counters and in the structure of counters, despite the fact that the transfer of the data is more or less following the very same path as in DBM. Reason for this is:

1. The fact that we can handle more than one secondary replica
2. That an Availability Group can contain more than one database.

Another few new counters got added to SQL Server:Databases. These counters give information about the SQL Server Log Cache of each individual database which is playing an instrumental role when transferring data to the secondary replicas or on the secondary for redo operations.

The counters are named:

Log Pool Cache Misses/sec – showing the number of log blocks which could not be found in the Log Cache
Log Pool Disk Reads/sec – Number of Reads issued by the Log Cache to read log blocks from disks
Log Pool Requests/sec – Number of log block reads per sec

In fact Always On is a bit different than database mirroring (DBM) with respect to sending the log blocks to the secondary replica(s). DBM flushes the log block to disk and once completed locally, sends the block to the secondary.

for more details you can find Here and Here

What is log pool here?

As literally log pool used in AlwaysOn availability group in SQL server Database for system monitor.

The SQLServer:Databases object in SQL Server contains performance counters that monitor transaction log activities, among other things. The following counters are particularly relevant for monitoring transaction-log activity on availability databases:

Log Flush Write Time (ms), Log Flushes/sec, Log Pool Cache Misses/sec, Log Pool Disk Reads/sec, and Log Pool Requests/sec. For you ref Here

Why two different name Log flush and Log Hardening? Can any one provide a link for database mirroring sync process.

The primary replica makes the primary databases available for read-write connections from clients. The primary replica sends transaction log records of each primary database to every secondary database. This process - known as data synchronization - occurs at the database level. Every secondary replica caches the transaction log records (hardens the log) and then applies them to its corresponding secondary database. Data synchronization occurs between the primary database and each connected secondary database, independently of the other databases. Therefore, a secondary database can be suspended or fail without affecting other secondary databases, and a primary database can be suspended or fail without affecting other primary databases.

Log is flushed on the secondary replica for hardening. After the log flush, an acknowledgement is sent back to the primary replica.

Once the log is hardened, data loss is avoided.

you can also monitor log harden through Dashboard Like right click on dashboard

for your ref Here and Here

In DBM or AG if i am using Synchronous mode, How does application can know Acknowledge Commit is success on both replicas will relational engine take care of that or storage engine take care about that.

Through TSQL query in primary replica we know that synchronization is update in both replica or not.

select * from master.sys.dm_hadr_database_replica_states;
Go

I am also attaching the screen shot here

Here synchronization_state is 2.

2 = Synchronized. A primary database shows SYNCHRONIZED in place of SYNCHRONIZING. A synchronous-commit secondary database shows synchronized when the local cache says the database is failover ready and is synchronizing.