I need to replicate all the articles of a table while preventing one column of data from being replicated. Unchecking an article removes the entire column, changing its schema. However, I want to maintain the column without updating its data.
You want the entire schema on the subscriber, but you want changes to certain columns replicated, correct? This can be done, but will require some work on your end. I can see two options:
- Create a trigger on the subscriber, inside the trigger would contain the logic that ignores changes to the mentioned columns. The drawback to this option is that triggers will have a performance impact. I'd avoid this option.
- Utilize custom stored procedures to replicate changes. What this does is, you modify the provided-replication stored procs, to exclude the columns you want excluded on the subscriber. When replication applies changes to the subscriber, it will ignore the changes you specified.
- Utilize replicating the execution of a stored proc. What this does is, if you mark a stored proc to be replicated, any changes the stored proc makes to any published tables will not be replicated, only the proc call and its parameters are sent to the subscriber and executed. The stored proc on the subscriber will need to be modified to exclude updating the mentioned columns. Also, the table article will need to have its ins/upd/del properties set to do nothing. To ensure individual changes made to the table on the publisher side is not replicated to the subscriber, you would set the ins/upd/del actions to nothing on the article.
In all cases, it most likely will require you to utilize the publication's @post_snapshot_script, which will create the object on the subscriber after a reinit/snapshot.
IMO, I would go with option #2.