We have transactional replication setup between an SQL Server 2012 box and a SQL Server 2016 box.
Every half an hour the source table articles are altered by DELETE then INSERT sequence. This sequence comes from a stored procedure executed by an SSIS packgage. Sometimes the DELETE has been replicated to the subscriber, but the INSERT hasn't made it there yet.
The result is interim missing data at the subscriber. This interim state can last several minutes as the commands get replicated out to all the articles under replication. These table articles are used as the basis for SSRS reports at the subscriber, which become inaccurate due to this issue.
Inside the stored procedures that house the DELETE – INSERT statements we tried wrapping both commands in BEGIN TRAN – COMMIT TRAN, but this didn't appear to help.
Ideally the delete – insert would be combined to happen at once (kind of like MERGE) using a different SQL command, or wrapping the commands in something akin to BEGIN TRAN – COMMIT TRAN
I've trended a row count on the replicated tables at the subscriber and there can be a span of 4 – 10 minutes where the deletes have occurred, but the inserts have not.
Best Answer
It sounds like you'd benefit from using procedural replication for those stored procedures. That way, SQL Server will just run the procedures on the replicated instance rather than replicating all the DELETE and INSERT operations. I'll wager that will be much faster and will reduce latency in addition to fixing the issue you're seeing where the destination tables are empty for a period of time.