Overview
High availability options are becoming more and more popular with the increase of critical business systems and SQL Servers. The transaction log is one of the most important aspects of database mirroring, replication, and log shipping.
Explanation
I’m not going to go over what each high availability option is, but instead, I’ll talk about how the transaction log is involved with each option.
Things that Use the Transaction Log
Database Mirroring – When a DML statement is executed against the primary database, mirroring needs to create this exact same statement on the mirrored database as quickly as possible. This is done by sending a continuous stream of active transaction log records to the mirror server, which applies these logs to the mirrored database, in sequence, as quickly as possible. Starting in SQL Server 2008, the transaction log record gets compressed before sending it over to the mirrored server to help reduce latency across the network.
Another consideration when choosing between synchronous or asynchronous mirroring is that when mirroring is asynchronous, the transactions commit without waiting for the mirror server to write the log to the disk, which maximizes performance. In synchronous mirroring, the transaction is committed on both partners, but that increases network latency.
One thing to take into account with database mirroring is that when you pause mirroring, the principal database will still accumulate records in the transaction log, and that log file cannot be truncated. Therefore, if the database mirroring stays in the paused state for a long period of time, it can cause the log to fill up.
Transactional Replication – Transactional replication includes 3 main parts: SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent.
During transactional replication, the Log Reader Agent monitors the transaction log of the database replicated and copies the transactions marked for replication into the distribution database (a database created when replication is configured). The Distribution Agent then comes along and copies the transactions from the distribution database to the Subscriber (replicated database)
The Log Reader Agent runs at the Distributor, and when executed, it first reads the publication transaction log looking for any DML statements. Next, the agent copies those transactions marked for replication to the distribution database. The Distribution Agent then moves the transactions to the Subscriber, as described above. Only committed transactions are sent to the distribution database.
Unlike mirroring, which works at the physical level, replication works at the logical level.
Log Shipping – There’s really not too much involved in log shipping. You can think of it like you think of Backup/Restore. Log shipping basically consists of backing up a transaction log, shipping (moving) it to another server, and restoring the transaction log. The log can also be shipped to multiple secondary servers, if needed.
