Transactional replication is trying to update a row that does not exist yet. Why?

Question

I've been tinkering with SQL Server 2019 and transactional replication for a little while now and I've hit a wall. Looking at the replication monitor I'm seeing the error "The row was not found at the Subscriber when applying the replicated UPDATE command for Table '[dbo].[MessageQueue]' with Primary Key(s): [Id] = 6039250". This error occurs on the very first batch that the distributor sends to the subscriber.

I know the above is a row that was added after my backup of the publisher, but I would have expected the INSERT to be replicated prior to the UPDATE.

Publisher/Distributor: On-prem SQL Server 2019 standard

Subscriber: SQL Managed Instance

I'm using the backup/restore method for initialising as when I get this into production, I'm dealing with a 2TB database. My test database is only 150GB.

Below is the process I followed to stand things up:

• Create the publisher, I did this via the SSMS wizard.
• Set the subscription options of the publication for "Allow initialization from backup files" = True
• Backup up the database to an Azure storage account. I used an Access Key from the storage account as a credential. Also didn't want to break the log chain for my DPM server so I used COPY_ONLY.

CREATE CREDENTIAL [credential_name_here]
WITH IDENTITY = '<storage account name>',
SECRET = '<redacted>'

BACKUP DATABASE [myDatabase] TO  URL = N'https://<redacted>.blob.core.windows.net/backups/myDatabase_replication.bak' 
WITH CREDENTIAL = 'credential_name_here'
,COMPRESSION, COPY_ONLY, STATS = 5

• Then from my SQL MI, I run the restore command

use master
RESTORE DATABASE [myDatabase] 
FROM URL = N'https://<redacted>.blob.core.windows.net/backups/myDatabase_replication.bak'

• Finally, I created the subscription using some SQL.

Note I have a few options commented out. I attempted to use @sync_type, @backupdevicetype & @backupdevicename but could not get past the error 'Cannot open backup device 'https://redacted.blob.core.windows.net/backups/myDatabase_replication.bak'. Operating system error 86(The specified network password is not correct.). '.

For some unknown reason, sp_addsubscription doesn't support the passing of a credential... well as far as I could find anyway.

USE [myDatabase]
GO
EXEC sp_addsubscription 
@publication = N'pub_myDatabase', 
@subscriber = '<redacted>.<redacted>.database.windows.net', 
@destination_db = N'myDatabase',
@sync_type = 'replication support only',
--@sync_type = 'initialize with backup',
--@backupdevicetype ='URL',
--@backupdevicename = 'https://<redacted>.blob.core.windows.net/backups/myDatabase_replication.bak',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0

-- Configure the Distribution Agent job
exec [myDatabase]..sp_addpushsubscription_agent 
     @publication = N'pub_myDatabase', 
     @subscriber = '<redacted>.<redacted>.database.windows.net', 
     @subscriber_db = N'myDatabase', 
     @job_login = NULL, @job_password = NULL, 
     @subscriber_security_mode = 0, 
     @subscriber_login = '<redacted>', @subscriber_password = '<redacted>',
     @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0 -- Continuous
GO

-- Start Distribution agent
exec [myDatabase]..sp_startpushsubscription_agent 
     @publication = N'pub_myDatabase', 
     @subscriber = '<redacted>.<redacted>.database.windows.net', 
     @subscriber_db = N'myDatabase'
GO

It's at this point I check the replication monitor and see the error quoted earlier. Any tips and/or guidance would be most appreciated!

Answer 1

Replication is quite old and neglected feature. The official documentation how to initialize subscription from backup is barebones and ambigous IMO

Most probably the problems encountered were due to log backups executed between finishing full backup and adding subscription. In this case the log reader doesn't replicate the insert because the record of it is gone already.

I think this article supports somewhat my theory. It describes scenario with following steps:

1. Take full backup from publisher
2. Restore this backup on subscriber with NORECOVERY
3. Configure replication
4. Take a non-full backup of the publisher (DIFF or LOG).
5. Restore the non-full backup
6. Configure the subscription using the previous file as initialization source (@backupdevicename = N’<Path and name of backup file used for initialization>’)

Note using @backupdevicetype = URL is not supported. There is no way to restore from URL