What would cause a transactional replication subscription to restart repeatedly with no errors?

by PaulM   Last Updated August 09, 2018 10:06 AM

I have set up a transactional replication publication/subscription to test a migration from an on-premise SQL 2016 Enterprise Edition installation to Azure SQL DB and I'm finding that when the database snapshot finishes uploading to Azure, it just starts over again from the beginning. It finishes within 5-6 hours and uploads ~125GB of data, so I don't believe it is anything to do with the subscription expiring or exceeding any timeout thresholds. It is set to run continuously, rather than on a schedule.

I've looked at the distribution history in the MSdistribution_history table and can't see anything which mentions the subscription being marked for reinitialization, all that is there are entries showing 'Bulk copied data into table ...', and then at the point where I can tell it restarted, it goes back to 'Creating primary key index on table...' and 'Applied script x...'. I have also looked at the MSrepl_errors table and msdb..sysreplicationalerts and both are empty.

I have tried deleting the Azure SQL DB and publication and going through the process from the beginning again, but I am seeing the same thing after having done this.

Is this something that someone has seen before and could explain the cause of and how to fix it?

Related Questions