Transaction replication error @2008 R2 PROD

Question

Post reply

Transaction replication error @2008 R2 PROD

Ron007

Mr or Mrs. 500

Points: 564
More actions
April 5, 2016 at 7:55 am

#315061

we used " Initialize a Transactional Subscription from a Backup " to set up replication and followed exactly MSDN steps(https://msdn.microsoft.com/en-us/library/ms147834.aspx).
But we are missing some data at Subscription after reinitialize from backup. then we are getting error as "row was not found at the subscriber when applying the replicated command", some times another error "The DELETE statement conflicted with the REFERENCE constraint" WHY replication is not deleting in sequence manner?
we can't skip these errors and we able to fix this issue by inserting manually as temporary fix but we are trying to find out permanent solution for this.
Are we missing any important option while setting up replication ?
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Sounds like the backup was taken too early (and hence isn't quite in-sync with the replication).

You need to take the backup after creating the publication.

Create the publication. Set the options on the publication to allow for init from backup.

Take the backup

Restore it

Create the subscriptions and agents.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ron007 Mr or Mrs. 500 Points: 564 More actions · Answer 2

Thanks for response.

My set up steps: I took full backup then created publisher then took differential and t-log backup

next Initialize database backup..

My mistake is , i took full back up then created PUB, instead of creating PUB then take full backup. Am i right?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yup. Create the publication, then take the backup. You don't need diff or log backups for replication, just a full backup taken after the publication was created.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ron007 Mr or Mrs. 500 Points: 564 More actions · Answer 4

one final question: Does sequence matter? (our DB size is 500 GB that's why we need to take log backup too to get the latest data on PROD)

We did this sequence : Full backup-> create PUB-> DIFF backup/ log backup-> then Initialize SUB from BACKUP.

Do you think, it works? : create PUB ->then take Full backup-> DIFF backup/log backup-> then Initialize SUB from BACKUP.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

You don't need to take the diff or log backups. After the publication is created, the changes will get copied to the distributor and will be synced to the subscriber once it's created.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass