Is it safe to turn on Delayed Durability on a transaction replication subscriber?

Question

In a transaction replication environment with a publisher SQL Server that receives frequent inserts and updates from an application and a subscriber SQL Server with pull replication jobs, is it safe to enable delayed durability on the subscriber?

Microsoft says that delayed durability is not supported for transaction replication, but was unclear if this was in regards to any server involved in the replication, or just the publisher.

While there is always risk in turning on delayed durability, is there any added risk in turning it on for a replication subscriber? If it is unsupported or there are added risks, is there a way to reduce the WRITELOG waits on the subscriber? The subscriber is a reporting server and its number one wait is always WRITELOG due to the frequent inserts and updates occurring on the publisher from the application (45.3 hours of WRITELOG wait in 345.1 hours of uptime).

Answer 1

First off who cares? The WRITELOG waits are suffered by the Distribution Agent, not the users.

Second, have you considered the normal performance optimizations for replication, ie Enhance Transactional Replication Performance, in particular Subscription Streams?

The –SubscriptionStreams parameter can greatly improve aggregate replication throughput. It allows multiple connections to a Subscriber to apply batches of changes in parallel, while maintaining many of the transactional characteristics present when using a single thread. If one of the connections fails to execute or commit, all connections will abort the current batch, and the agent will use a single stream to retry the failed batches. Before this retry phase completes, there can be temporary transactional inconsistencies at the Subscriber. After the failed batches are successfully committed, the Subscriber is brought back to a state of transactional consistency.

Writing to the subscriber in parallel will improve replication throughput and should reduce the aggregate WRITELOG waits as concurrent transactions can caravan on the each log file IO.

It's certainly unsupported, as the Distributer would not know the correct state of the Subscriber after a failure. So after an unplanned shutdown, you'd have missing data, and you wouldn't have any other indication. You'd basically have to reinitialize the subscriber after any failure.