question

salilsingh-9961 avatar image
0 Votes"
salilsingh-9961 asked NavtejSaini-MSFT commented

Transactional replication - Update issue

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

sql-server-generalsql-server-transact-sqlazure-sql-database
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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

0 Votes 0 ·

1 Answer

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered CarrinWu-MSFT commented

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.


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TomPhillips-1744 ,

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

0 Votes 0 ·

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

Thanks,
Salil

0 Votes 0 ·

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.

0 Votes 0 ·

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.

0 Votes 0 ·