question

rehaan avatar image
rehaan asked

Transactional Replication query

Hello Friends, I am working on Transactional Replciation and i have a query. Can someone help me please At publisher i have a table which has 5 columns. I am replicating this to a different server which would be my Subscriber My requirement is 1. I need 6 columns on subscriber : Which i can directly add one additional column at subscriber 2. The new column should get last 6 digits of column2 (PAN) everytime it does insert. 3. I have tried to amend the stored procedue that does insert ,but it is failing with an error message saying that @c6 is not valid parameter How can i achieve this ?
replicationtransactional-replication
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
Maybe you could just add a computed non-persisted column instead of a physical column: https://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks David for your help. I will try this and keep you posted.
0 Likes 0 ·
rehaan avatar image
rehaan answered
Does anyone suggest me any other option?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That is the best way to do it. I have a very large replication system, and do this with several databases.
0 Likes 0 ·
rehaan avatar image
rehaan answered
Hi Sjimmo. Thanks for the response. DO i need to add computed column at Publisher or Subscriber? If i add computed column at publisher does that replicate automatically to subscriber or do i need to amend any stored procedures?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sjimmo avatar image
sjimmo answered
Put it at the subscriber and you won't have to change the publication
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rehaan avatar image
rehaan answered
Sjimmo/David Wimbush As suggested i have added computed column and it works fine. But my requirement is a bit different The new column should get last 6 digits of column2 (PAN) everytime it does insert. New column is getting 6 digits of column2 , but my column 2 is encrypted on my subscriber as it has sensitive information.My computed column returns me 6 digits which is encrypted, but i rather want the 6 digits of unencrypted (which is the same as it is at publisher). I am happy to add additional column at subscriber , but struggling to amend the insert procedure to add that additional column in the sproc? Can guidance would be much helpful for me please. Hope i have made my requirement clear .
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

At what point is the data encrypted?
0 Likes 0 ·
rehaan avatar image
rehaan answered
Hi sjimmo It is encrypted (after snapshot)while it is being replicated from Publisher to subscriber.I have customised insert stored procedure and it is working fine. It is failing when an update is running? Update sproc is complex . unable to amend it. Can you provide me your email so that i can share my code ?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Use this email address: sclemens3@charter.net It isn't normally monitored, but I will watch it. But I'm curious, you say it is encrypted after the snapshot. Is that the replication snapshot or something else?
0 Likes 0 ·
rehaan avatar image
rehaan answered
sjimmo, I have emailed you all the details. Can you please check when you have sometime.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sjimmo avatar image
sjimmo answered
OK Rehaan, I think I understand now. It appears that the data is encrypted on the subscriber side only, and not on the publisher side. I would a field on the publisher and have a calculation that takes a substring of each of the fields that you want to put together. Then simply replicate this field as a normal field. ie: [TestField] AS (SUBSTRING([field1],(1),(6))+SUBSTRING([field2],(1),(6))) You will not have to change the application that inserts the data. Once it is inserted/updated the field is recalculated and replicated. If you wish to do it on the subscriber side, you are going to have to be a lot more elaborate in your SP and calculate the field which I believe is @c19 before doing the encryption in order to populate that field. Thus after the begin in your SP, then calculate as select @c19 = substring(field1, 1, 6) + substring(field2, 1, 6). Then do the insert as it is written but get rid of everything after the @c19. Do the same with the update SP. In other words do your calculation before the encryption. Good luck
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rehaan avatar image
rehaan answered
Thanks Sjimmo for your quick response. In my case i am encrypting only at subscriber which is @c1 and @c2. using a encryption function for @c1 and @c2 and substring of @c1 and @c2 for @c20 and @c21. Insert is working fine. I am having no issues with inserts and encryption. I am having issues with update SP. Having issues with update SP only .Struggling to amend this. I need to amend @c1,@c2,@c20 and @c21
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The data coming into your SP from the publication is unencrypted. So do your concatenation of C1 and C2 right after the begins. This will give you what you want. ie: begin select substring (@c1, 1, 6) + substring(@c2, 1, 6) if ... begin ... end else begin end end This way when you are doing our case for that field, @c19 is already calculated. You cannot do this after the encryption has taken place. Otherwise you will need a trigger or another sp to decrypt the fields in question and then do the concatenation.
0 Likes 0 ·
rehaan avatar image
rehaan answered
Thanks sjimmo for your help. I will test this tomorrow and will keep you posted. Honestly your help is much appreciated sir. Thanks Again
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

No problem. Let me know how it goes.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.