Transactional replication - Update issue

Question

Transactional replication - Update issue

salilsingh-9961 351

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

salilsingh-9961 351 Reputation points

2021-04-12T14:09:30.797+00:00

Hi Team,

Could you please reply to the below.
Please note in order to disable deletion at subscription table end, under article properties, i have put action if name is in use as Keep existing object unchanged. Also, under Delete delivery format, i have put do not replicate delete statements.

Thanks,
Salil
Anonymous

2021-04-15T00:18:13.897+00:00

@salilsingh-9961 Please let us know if you need any further help.

Answer accepted by question author

0 additional answers

Your answer

salilsingh-9961 351 Reputation points

2021-04-12T14:09:30.797+00:00

Hi Team,

Could you please reply to the below.
Please note in order to disable deletion at subscription table end, under article properties, i have put action if name is in use as Keep existing object unchanged. Also, under Delete delivery format, i have put do not replicate delete statements.

Thanks,
Salil
Anonymous

2021-04-15T00:18:13.897+00:00

@salilsingh-9961 Please let us know if you need any further help.

Answer 1

Tom Phillips 17,781

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.

salilsingh-9961 351 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 351 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,781 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,891 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.

Share via

Transactional replication - Update issue

0 additional answers

Your answer