Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that has some unencrypted data that needs to be replicated to another server. I want to do :

  1. Mask the data with an x except the last 5 characters e.g. xxxx12345

  2. just replicate the last 5 characters of the data in the column (substr the last 5 characters)

Is there a way I can do that other than writing a trigger on the subscriber ?

Thanks

R

share|improve this question
1  
Why do you want to do the mask? And the reason I ask is because the "why" will dictate the allowable "where" it could happen. Do you not want the numbers to go over the wire from server1 to server2? Or do you just not want the full number living on server2? –  Thomas Stringer Jan 24 at 19:54
    
Replication works by reading the T-Log. So I dont think it is possible to just replicate the last 5 characters of the data in the column. What is the purpose of doing it ? –  Kin Jan 24 at 19:54
    
Also for part(1), this answer will help you. –  Kin Jan 24 at 20:04
add comment

2 Answers 2

Yes, you can use custom replication stored procedures to do this. Custom replication stored procedures are especially useful when an application requires custom logic such as this and can allow the data to be transformed in-flight to Subscribers.

Have a look at the sections Default and custom stored procedures and Considerations for Using Custom Stored Procedures in Specify How Changes Are Propagated for Transactional Articles, and the section To generate and use custom stored procedures in Set the Propagation Method for Data Changes to Transactional Articles.

share|improve this answer
add comment

There's a couple of options.

The best option would be to add a calculated column to the table which contains the final value which you want to replicate. Then replicate this column instead of the actual column. This is the best option because only the final value is sent.

Another option would be to edit the replication stored procedures on the subscriber so that when new rows and inserted, or rows are updated the modified value is used. This would require that all the rows be updated after the snapshot is delivered. If the admin of the subscriber wants to they could change this by simply editing the stored procedure so that new rows will get the actual value.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.