Issues with Updatable Subscriptions for Transactional Replication

  • Article

Careful with Replication type "Updatable Subscriptions for Transactional Replication" when your application does not use column names in INSERT and SELECT statements and has database triggers

NOTE:This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Problem : Configuration of replication type "Updatable Subscriptions for Transactional Replication" made application stop working because of column names not defined in some of INSERT/SELECT statements and "NOT FOR REPLICATION" option not defined in Triggers.

Resolution : Replication needs to be removed ASAP and dropping of extra columns and constraints created by Replication.

Please follow below steps to remove replication and drop constraints/columns,

1. Remove Subscription and Publication (Please follow the below link)

https://support.microsoft.com/default.aspx/kb/324401

2. Removing Columns and constraints from table/articles created by Replication 

1. Run the below query on publisher database, copy the output and execute the output on publisher database to remove constraints prefixed with "msrepl_tran_version".
    SELECT 'ALTER TABLE '+ s.name+ '.'+ OBJECT_NAME(o.parent_object_id) + '    DROP CONSTRAINT ' + o.name

    FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE o.name LIKE '%MSrepl_tran_version_default%'

  2. Run the below query on publisher database, copy the output and execute the output on publisher database to remove columns named "msrepl_tran_version".

    SELECT 'ALTER TABLE '+TABLE_SCHEMA+'.'+TABLE_NAME + ' DROP COLUMN msrepl_tran_version'

FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME ='msrepl_tran_version'

Above steps can be followed for subscriber database, if needed.

Suggestion : before configuring this replication type, please go through the below link to understand more about this and considering for your environment
https://msdn.microsoft.com/en-us/library/ms151718.aspx