Common issue and fix – Test it out
- Transaction log grow very larger
- DML operation done on subscriber will stop replicate data
- DDL changes
With the default profile do the following test.
Ex: 1 – Insert
- Manually insert rows in subscriber 2. Insert the same data in publisher as well– That will break the replication, since PK violation.
Fix: Whatever is inserted manually needs to be deleted from subscriber with where condition.
Ex: 2 – Delete
- Insert three rows on publisher, after rows replicate 2. Delete in wrong order i.e delete first from subscriber 3. Then delete it from publisher
Fix: Insert that deleted rows in the subscriber
Ex: 3 – DDL
- Add column in subscriber 2. Add column on publisher – Error out says the column name must be unique
Fix: Drop that column from subscriber
Alter table drop column and see
Drop column is replicating to subscriber
Alter table add column and see
Add column is replicating to subscriber
Alter table add Unique constraint & Drop constraint the and see
Adding and dropping constraint both are replicating to subscriber
Alter table drop constraint PK and see
Not working, since primary key is must. We need to remove table from replication and add it
Alter table
–By default following will not replicate, you can change copy to in article property from false to true
Create & Alter Default and Check Constraints
Create & Alter Non-Clustered index
Create & Alter Foreign key Constraints
Create & Alter trigger
Here is the list:
How to fix for these errors and make the replication back to SYNC
- Full reinitializefor small database.
- Skip the commands in distribution. But make sure are you doing for correct known transactions
Drawback: The entire transaction will be skipped. Manually you can delete or make a dummy insert etc.
- Ignore errors in the agent. But make sure are you doing for correct known transactions
Identify the error number that is causing an issue. Modify the agent profile to ignore the specific error. Restart the distribution agent.
Drawback: We do not know what is exactly skipped. It will tell how many skipped.
- Re-initialize Full snapshotii. From backup iii. Just initialize the article that is only having problem (just drop that article).
How to fix table issue:
Is the table is small remove and re add that single article using T-SQL, for larger table can use initialize From backup.
Initialize Single article
Remove existing article and re add – Working fine and changes are coming
Add new article– Working fine and changes are coming
Initialize From backup
Initialize single article when initialized from backup – Same like Single article initialized, but only changes instead of sp_refreshsubscriptions, just generate new subscription
Using this method – both existing article changes and new added article are coming
Make DML changes in existing article – Working fine and changes are coming
Add new article and do DML– Working fine and changes are coming
Sometimes, we need to remove or recreate complete publication and subscription
Test removes only subscription and try it in the existing replication, which is configured by using snapshot method
Error – subscription is missing in subscriber and login account for distributer is using agent account, we can change it in subscription property
- Test remove both publication (Script out Pub) and subscription and try it , which is configured by using snapshot method
