Hi Team, I have a requirement where i need to remove and apply transactional replication on a daily basis. After removing and applying transaction, as the snapshot gets created each time, control tries to insert exiting records in publisher table (which are also exiting in subscriber table) to the subscriber table and thus error comes. How can i ensure that existing records in publisher/subscriber table are updated only to the subscriber table? Thanks, Salil

I am unclear what exactly you did. What exactly did you do to create your subscriber the first time? Why are you removing and applying transactional replication every day? Transactional replication requires an initial "snapshot" or restore. After that process is complete, transactional replication applies the commands run on the publisher to the subscriber(s). The only reason you would get duplicate errors, is if you applied the changes to the subscriber a different way than transactional replication.

Transactional replication - Update issue

Accepted answer

Tom Phillips 17,716 Reputation points

2021-04-12T16:13:48.763+00:00

I am unclear what exactly you did. What exactly did you do to create your subscriber the first time? Why are you removing and applying transactional replication every day?

Transactional replication requires an initial "snapshot" or restore. After that process is complete, transactional replication applies the commands run on the publisher to the subscriber(s).

The only reason you would get duplicate errors, is if you applied the changes to the subscriber a different way than transactional replication.
Please sign in to rate this answer.
salilsingh-9961 346 Reputation points

2021-04-12T18:08:33.65+00:00

Hi @Tom Phillips ,

I have a requirement where I need to restore a non replicated .bak file to my publisher db everyday, this removes my replication settings, which requires me to remove/apply replication each day.
Another requirement that I have is to make sure records in subscription db table are never deleted which is why I applied changes as mentioned in my above comment.
As now I am not dropping and recreating the subscription table again and as with each recreation of replication it causes new snapshot to be generated, publisher db always tries to insert the published records to subscriber db as records are already existing in subscriber db the error comes.
In daily backup file few records are as such they are existing in publisher/subscriber table as I always get an incremental backup.
Could you please suggest me a way so that the records which are existing in publisher and subscriber table could be updated in subscriber table?

Thanks,
Salil

salilsingh-9961 346 Reputation points

2021-04-12T18:13:51.423+00:00

Please note my publisher db is on-premises SQL server db and subscriber db is Azure SQL server db.

Thanks,
Salil

Tom Phillips 17,716 Reputation points

2021-04-12T18:25:53.77+00:00

If you are restoring a database to the "publisher" every day, that is not a good fit for replication.

My suggestion is you use an SSIS/Data Factory package to copy the data to the subscriber instead.

CarrinWu-MSFT 6,851 Reputation points

2021-04-13T08:46:30.193+00:00

Hi @salilsingh-9961 , may I ask why not try another way to updata data into publication database? It seem that SQL Data Sync could suitable to run continuous sync between Azure SQL Database and on-premises SQL Server in a bidirectional flow.
Sign in to comment

Transactional replication - Update issue

0 additional answers