I have snapshot replication set up on a server which publishes to a second server as part of a scheduled SQL Server Agent job. When the snapshot finishes, I would like to detect when each table has been updated or when all tables have been updated.
The best solution I have found so far is to put a flag into a placeholder table before the SQL Server Agent job which is replicated to the second server and then have a job on the second server that runs every minute to check to see whether a new snapshot has arrived.
Does anyone have a better solution or a way to identify when a snapshot has been processed onto a new server?
Best Answer
Change it to a pull subscription, and watch for the SQL Agent job to stop. When it stops the snapshot has been applied. Or you could get really creative and setup a linked server to the distributor if you want to keep it as a pull subscription and watch for the job on the distributor to stop.