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:
The fact that we can handle more than one secondary replica
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?
log pool used in AlwaysOn availability group in SQL server Database for
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
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;
I am also attaching the screen shot here
2 = Synchronized. A
primary database shows
SYNCHRONIZED in place of
secondary database shows synchronized when the
local cache says the database is
failover ready and is synchronizing.