October 24, 2018 at 9:52 am
Hi All
Many thanks in advance for any info provided 🙂
I have tried this out on our DEV enviroment from 2008 r2 to 2016 and it works fine
When I try this on our QA I get the error
Command attempted:
declare @event_type SMALLINT = 1, @distributor_major_version SMALLINT = 10, @distributor_minor_version SMALLINT = 50, @distributor_build_number SMALLINT = 2550, @totalruntime INT = 75567, @totalworktime INT = 218, @totalnumtrans INT = 0, @numtranspersec REAL = 0.00, @totalnumcmds INT = 0, @numcmdspersec REAL = 0.00, @totalskippedcmds INT = 0, @totalidletime INT = 0 if object_id(N'sys.sp_repl_generate_subscriber_event') is not null exec sys.sp_repl_generate_subscriber_event @event_state = @event_type,@distri
(Transaction sequence number: 0x004641EC000029AE000700000000, Command ID: 1)
Error messages:
The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null) (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598
The row was not found at the Subscriber when applying the replicated (null) command for Table '(null)' with Primary Key(s): (null) (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598
When I look at one of the with an issue the table with only one row the ID identity value in the publisher the ID is 35 in the restored DB the same table has only the one row has an ID value of 13 just after the restore????
How can the values be different when it was a restore?
Publisher DB table
ID URL Title Include BookLink
35 NULL NULL NULL NULL
Subscriber DB table
ID URL Title Include BookLink
13 NULL NULL NULL NULL
Any help would be appreciated
Kind regards
Northern Monkey
October 25, 2018 at 12:58 pm
If you replicate columns with an IDENTITY property, you can run into trouble. If you restore the subscriber database, you would need to re-setup the replication.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
October 26, 2018 at 2:32 am
Hi Sgmunson
Thanks for the responseThe replication is set to Initialize from backup so that is what I have been doing but the ID in the tables with just one row have different ID after the restore which keeps changing after each test restore and Initialize?
Kind regards
The Northern Monkey
October 26, 2018 at 10:40 am
Northern Monkey - Friday, October 26, 2018 2:32 AMHi SgmunsonThanks for the responseThe replication is set to Initialize from backup so that is what I have been doing but the ID in the tables with just one row have different ID after the restore which keeps changing after each test restore and Initialize?
Kind regards
The Northern Monkey
Are you using immediate sync?
And are you checking the differences and comparing to what is in the distribution database?
Sue
October 29, 2018 at 3:22 am
Sue_H - Friday, October 26, 2018 10:40 AMNorthern Monkey - Friday, October 26, 2018 2:32 AMHi SgmunsonThanks for the responseThe replication is set to Initialize from backup so that is what I have been doing but the ID in the tables with just one row have different ID after the restore which keeps changing after each test restore and Initialize?
Kind regards
The Northern MonkeyAre you using immediate sync?
And are you checking the differences and comparing to what is in the distribution database?Sue
Hi Sue
The processes I have been following which worked on our DEV servers 2008 to 2016 is the same but just with server name changes in the scripts for our QA server which we are having a problem with the tables with just one row in them so the processes has worked in the past with the scripts and processes timings.
The source of the scripts I built are from this page https://www.sqlpassion.at/archive/2012/08/05/initialize-a-transactional-replication-from-a-database-backup/
Once the full backup had run and the restore I then run the logs backup and the script below
EXEC sp_addsubscription
@publication = N'OurDB',
@subscriber = N'VM-QA-SVR-01\QA2016',
@destination_db = N'OurDB_QA',
@sync_type = N'initialize with backup',
@backupdevicetype='Disk',
@backupdevicename='\\VM-QA-SVR-01\ReplData\OurDB_Test_Tran.trn',
@subscription_type = N'pull',
@update_mode = N'read only'
October 29, 2018 at 8:00 am
Northern Monkey - Monday, October 29, 2018 3:22 AMSue_H - Friday, October 26, 2018 10:40 AMNorthern Monkey - Friday, October 26, 2018 2:32 AMHi SgmunsonThanks for the responseThe replication is set to Initialize from backup so that is what I have been doing but the ID in the tables with just one row have different ID after the restore which keeps changing after each test restore and Initialize?
Kind regards
The Northern MonkeyAre you using immediate sync?
And are you checking the differences and comparing to what is in the distribution database?Sue
Hi Sue
The processes I have been following which worked on our DEV servers 2008 to 2016 is the same but just with server name changes in the scripts for our QA server which we are having a problem with the tables with just one row in them so the processes has worked in the past with the scripts and processes timings.
The source of the scripts I built are from this page https://www.sqlpassion.at/archive/2012/08/05/initialize-a-transactional-replication-from-a-database-backup/Once the full backup had run and the restore I then run the logs backup and the script below
EXEC sp_addsubscription
@publication = N'OurDB',
@subscriber = N'VM-QA-SVR-01\QA2016',
@destination_db = N'OurDB_QA',
@sync_type = N'initialize with backup',
@backupdevicetype='Disk',
@backupdevicename='\\VM-QA-SVR-01\ReplData\OurDB_Test_Tran.trn',
@subscription_type = N'pull',
@update_mode = N'read only'
There is quite a bit that happens before adding the subscription. Immediate sync is how you avoiding missing transactions when initializing from a backup. It's in the documentation:
Initialize a Transactional Subscription from a Backup
I checked those scripts and it is set to false. One of the reasons you really want to understand scripts you find. In terms of the ids, you need to check the distribution databases as verify the ids there.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply