Overview


In one of our Consulting projects, we upgraded and migrated the Databases from On-Premises SQL server 2014 to AWS SQL server 2019.

Though we have done ’n’ number of migrations on all scales but the client was ultra-sensitive about this, so this activity had to be performed in multiple phases, tested, and passed in each phase.

Problem Statement


On the day of Migration, everything went well. Databases came online, application and workflow started working properly and on time.

But after a few days, we encountered a problem with replication. All the publications stalled.

After connecting to the Replication monitor, it had one of the most common errors.


The most common error come with one of easiet fixes.

If the setting “not for replication” is set to incorrect value in the tabe on subscriber database, it is expected that user will face this issue.

Let’s check the value of “not for replication” for table in subscriber DB.




Database on the subscriber server was copy of publisher and was restored using the publisher backup.

Solution


In order to fix this error, run below script on subscriber database for the table. Introduced in SQL Server 2005, the below script will change ‘Not For Replication’ to 1 and fix the error with distribution agent.
DECLARE @ObjectID INT

SET @ObjectID = OBJECT_ID( '' ) – Table Name

EXEC sp_identitycolumnforreplication @ApplicableObjectID,1



If you encounter this issue, now you now how to go about making the changes and resolve the issue.