SQL Server replication testing

Question

I'd like to write a sql script to do a basic smoke test to check that the replication setup on my server is correct.

I think a simple set of CRUD operations against the tables that are supposed to be replicated will achieve what I'm after and I imagine the workflow will look something like this:

1. INSERT a row into a replicated table in the publisher db
2. Wait for replication job to run?
3. Check the row exists in the table in the subscriber db

My question is, how do I do step 2? I think I can check when the relevant job was last executed by looking in the msdb..sysjob* tables, but I'm not sure this is the best way to solve the problem.

Answer 1

For snapshot replication you can check indeed if both jobs have run (snapshot agent and distribution agent), but for merge and transactional you cannot since the agents run continously.

For transactional you can insert a tracer token and verify when the tracer token is marked as distributed to the subscriber, using sp_posttracertoken and sp_helptracertokenhistory.