SQL Server Merge Replication - Excluding A Column From Replication

Question

Environment: One Head Office (HO) and 2 Stores (S1 and S2), SQL Server 2008 R2

Goal:

I need to exclude column Password (varchar (30)) from merge replication on table Users. The Password column needs to be local to where it is originally created (HO or S1 or S2). The POS application requires Password column in Users table to authenticate users. We need this column to be replicated as NULL on other locations.

Things I tried:

I excluded column Password from merge publication on table Users but when the snapshot agent works at S1 or S2 for the first time, it drops this column at subscribers. We need this column there.

I tried to ADD COLUMN back after snapshot is applied using snapshot post snapshot script but it fails saying that objects can't be altered at subscriber.

Any solution?

Thanks

Answer 1

Is the users table ever changed on the subscriber? If not, you should make this table download only.

Then use a post snapshot script to make the password column null on the subscriber.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Yes, it does. Any insert/ update at subscriber should go back to HO and other stores without the password value. Password column should have value only in the location where it was inserted. Other locations will see password as null for that user.

Answer 3

Use a post snapshot script to zero them out on the subscriber.

Then hang a trigger off them to zero out the value of the password when the change is done by a replication process. Changes performed by triggers initiated by a replication process are not replicated.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Seems very good suggestion. Questions:

1) If I zero out password after snapshot or replication, won't these changes replicate back to other location and zero out theirs too? We are talking about merge replication here

2) How can I identify replication transactions from application transactions in trigger at subscriber before zeroing out? Should I go by User ID? 

Answer 5

1) No.

2)Check replication system table

Answer 6

1) if the action is done by a user process it will. But when it is replicated to the other side the trigger will fire and if you use sessionproperty('replication_agent')=1 to detect that the trigger is fired by replication it will not be re-replicated.

2) use this

if sessionproperty('replication_agent') =1

update users set password =null

where pk in (select pk from inserted)

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941