Skip to main content
Add explanation on why the TF won't affect synchronous replicas
Source Link
AMtwo
  • 16.3k
  • 1
  • 33
  • 64

The Log Reader Agent will only read as far as the last transaction committed to all other Availability Group replicas. This means that if one AG replica is offline, Replication will stop reading transactions until that AG replica is back online. This is done to be ultra-safe and ensure that only fully committed transactions are replicated.

In some cases, this may be too conservative of a rule for your setup. In that case, you can add Trace Flag 1448 to your startup parameters, which will disable this behavior for async replicas. If a synchronous replica is offline, it will still prevent the log reader agent from reading unsent transactions for Replication.

Why does Microsoft impose this restriction on synchronous replicas? If the LSN has not been hardened on the synchronous replica, then the transaction isn't fully committed. Replication is very conservative in what it considers a fully-committed transaction that is safe for replication. Say for example, that the AG isn't in sync due to a network issue, or some other problem (ie, it is online, but the Primary node just doesn't know that). If you force a failover of the AG, and allow data loss, then your replication secondary would have data that was lost during failover-- potentially breaking replication.

This TF will affect the server that is currently hosting the primary database replica, and thus must be set on every server that might host the primary replica for the AG.

From the Microsoft documentation on TF 1448:

Enables the replication log reader to move forward even if the async secondaries have not acknowledged the reception of a change. Even with this trace flag enabled the log reader always waits for the sync secondaries. The log reader will not go beyond the min ack of the sync secondaries. This trace flag applies to the instance of SQL Server, not just an availability group, an availability database, or a log reader instance. Takes effect immediately without a restart. This trace flag can be activated ahead of time or when an async secondary fails. For more information, see this Microsoft Support article.

The Log Reader Agent will only read as far as the last transaction committed to all other Availability Group replicas. This means that if one AG replica is offline, Replication will stop reading transactions until that AG replica is back online. This is done to be ultra-safe and ensure that only fully committed transactions are replicated.

In some cases, this may be too conservative of a rule for your setup. In that case, you can add Trace Flag 1448 to your startup parameters, which will disable this behavior for async replicas. If a synchronous replica is offline, it will still prevent the log reader agent from reading unsent transactions for Replication.

This TF will affect the server that is currently hosting the primary database replica, and thus must be set on every server that might host the primary replica for the AG.

From the Microsoft documentation on TF 1448:

Enables the replication log reader to move forward even if the async secondaries have not acknowledged the reception of a change. Even with this trace flag enabled the log reader always waits for the sync secondaries. The log reader will not go beyond the min ack of the sync secondaries. This trace flag applies to the instance of SQL Server, not just an availability group, an availability database, or a log reader instance. Takes effect immediately without a restart. This trace flag can be activated ahead of time or when an async secondary fails. For more information, see this Microsoft Support article.

The Log Reader Agent will only read as far as the last transaction committed to all other Availability Group replicas. This means that if one AG replica is offline, Replication will stop reading transactions until that AG replica is back online. This is done to be ultra-safe and ensure that only fully committed transactions are replicated.

In some cases, this may be too conservative of a rule for your setup. In that case, you can add Trace Flag 1448 to your startup parameters, which will disable this behavior for async replicas. If a synchronous replica is offline, it will still prevent the log reader agent from reading unsent transactions for Replication.

Why does Microsoft impose this restriction on synchronous replicas? If the LSN has not been hardened on the synchronous replica, then the transaction isn't fully committed. Replication is very conservative in what it considers a fully-committed transaction that is safe for replication. Say for example, that the AG isn't in sync due to a network issue, or some other problem (ie, it is online, but the Primary node just doesn't know that). If you force a failover of the AG, and allow data loss, then your replication secondary would have data that was lost during failover-- potentially breaking replication.

This TF will affect the server that is currently hosting the primary database replica, and thus must be set on every server that might host the primary replica for the AG.

From the Microsoft documentation on TF 1448:

Enables the replication log reader to move forward even if the async secondaries have not acknowledged the reception of a change. Even with this trace flag enabled the log reader always waits for the sync secondaries. The log reader will not go beyond the min ack of the sync secondaries. This trace flag applies to the instance of SQL Server, not just an availability group, an availability database, or a log reader instance. Takes effect immediately without a restart. This trace flag can be activated ahead of time or when an async secondary fails.

Source Link
AMtwo
  • 16.3k
  • 1
  • 33
  • 64

The Log Reader Agent will only read as far as the last transaction committed to all other Availability Group replicas. This means that if one AG replica is offline, Replication will stop reading transactions until that AG replica is back online. This is done to be ultra-safe and ensure that only fully committed transactions are replicated.

In some cases, this may be too conservative of a rule for your setup. In that case, you can add Trace Flag 1448 to your startup parameters, which will disable this behavior for async replicas. If a synchronous replica is offline, it will still prevent the log reader agent from reading unsent transactions for Replication.

This TF will affect the server that is currently hosting the primary database replica, and thus must be set on every server that might host the primary replica for the AG.

From the Microsoft documentation on TF 1448:

Enables the replication log reader to move forward even if the async secondaries have not acknowledged the reception of a change. Even with this trace flag enabled the log reader always waits for the sync secondaries. The log reader will not go beyond the min ack of the sync secondaries. This trace flag applies to the instance of SQL Server, not just an availability group, an availability database, or a log reader instance. Takes effect immediately without a restart. This trace flag can be activated ahead of time or when an async secondary fails. For more information, see this Microsoft Support article.