Asynchronous replication

As explained in 100 SQL Server Mistakes and How to Avoid Them

Asynchronous replication propagates data changes to a replica without requiring the primary operation to wait for replica confirmation.

Asynchronous replication completes writes on the primary before confirming replication to the secondary. It avoids network-latency delays but permits limited data loss. section 13.1

Account for these operational consequences:

• Treat asynchronous replication as a disaster-recovery mechanism, not proof of zero data loss. section 13.1
• Monitor network and server performance because undersizing can create a synchronization queue. section 13.1
• Distinguish disaster recovery from high availability so the business does not overestimate its protection. section 13.1

Cross-site disaster recovery

An availability group replicates changes asynchronously to a secondary site. A primary write succeeds immediately, but a network delay or overloaded server can leave recent transactions unsynchronized when failure occurs. section 13.1

Figure 13.3 HA/DR topology using availability groups providing local automatic failover and cross- site manual failover

Use the HA/DR topology to compare local automatic failover with cross-site disaster-recovery replication. section 13.1

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes

Explains write ordering, latency benefits, data-loss risk, and synchronization queues, but stops short of configuration commands.

Examples

Uses cross-site availability-group topology and an overloaded synchronization queue to illustrate delayed replication.

What it assumes

Assumes familiarity with primary and secondary servers, while emphasizing the distinction between high availability and disaster recovery.

What you can do after

Evaluate disaster-recovery protection by weighing latency, server capacity, synchronization delay, and possible data loss.

How it connects

Ties asynchronous replication to disaster recovery, availability groups, high availability, network latency, and data loss.

liveBookmanning.com

Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

• replication lagcontrasts with · Replication lag is the elapsed delay between a change being applied or committed at a source and becoming applied or visible at a replication target.
• disaster recoveryimplements · Disaster recovery is the planning and execution of processes and capabilities for restoring systems, data, and services after a disruptive event.
• availability groupconfigures · An availability group is a SQL Server high-availability and disaster-recovery configuration that maintains synchronized database replicas.
• asynchronous programmingimplements · Asynchronous programming is an execution approach in which operations can progress independently without requiring the initiating flow to wait for their completion or an immediate response.
• replicationkind of · Replication is the creation or maintenance of multiple copies of data, state, or functionality across separate locations or components.

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.