none
can a db that is already the target of trans replication also be the target of custom made etl? RRS feed

  • Question

  • hi we run 2017 std.

    Due to a lack of pk's on some publisher tables and 3rd party involvement, we may need to roll our own "replication" to a target db via etl.

    the best scenario would be for us to put those tables on db's whose tables with pk's already subscribe to sql server tran replication.  Is that doable?  Is it possible that best practice says not to do this? 


    • Edited by db042190 Thursday, August 22, 2019 4:27 PM clarification
    Wednesday, August 21, 2019 5:07 PM

Answers

  • Hi.

    I believe this is doable, and here is why:

    I have just tried (yesterday) a scenario where I am replicating multiple Publishing (PUB) Databases into one Subscribing (SUB) Database - this worked without a glitch. 

    On the same note, if you were to create a SUB DB and designate it as a target to your Replication and then you ship some more Tables to the same SUB, I see no issues with that as long as you do not tamper with the part of the SUB DB containing the Tables replicated through Transnational Replication.

    (Note: Be careful of the following: Triggers, FK constraints, Check Constraints - if your Subscriber has these and they do not contain NOT FOR REPLICATION clause, then you might be in for an unpleasant surprise down the road, in that your replication might start breaking because of these - in my case, it was Triggers)

    A general word of caution - read through the "Properties for All Table Articles" carefully, as there might be something relevant to your case (perhaps the Parameter such as "Action If Name Is In Use" which, depending on the case, should be changed to "Keep Existing Object Unchanged", e.g. if there is already an Object with the same name at the SUB).

    Good luck. :)

    Regards,

    Bogdan


    • Edited by BogdanU Wednesday, August 28, 2019 11:47 AM
    • Proposed as answer by BogdanU Wednesday, August 28, 2019 11:47 AM
    • Marked as answer by db042190 Wednesday, August 28, 2019 1:09 PM
    Wednesday, August 28, 2019 11:46 AM
  • Hi.

    You're welcome.

    I understood you from the get-go, and what I was saying is not that I am shipping Tables via means other than Transactional Replication, but rather that the concept of having more than one source shipping to the same SUB DB works.

    Thinking a bit more about your concrete situation, I still do not see any issues with shipping Tables (which are NOT already a part of an existing replication) to the same SUB DB that is the target DB for a standard Transactional Replication.

    What's more, I have just manually added a Table to my SUB DB already subscribed for data via Transactional Replication, inserted some data in it, am observing the Replication Monitor, and it still runs fine.

    To conclude, I think you should be o.k. as long as your "moonshine" :) ETL does not touch what is already a part of an existing Replication.

    I also think that the best way to learn things like these is just to try it (preferably not on live data :) ) and learn from whatever the outcome might be (and it'd be great if you are willing and able to share the outcome here, so that we all have the opportunity to learn from it :) ).

    With kind regards,

    Bogdan


    • Edited by BogdanU Wednesday, August 28, 2019 1:01 PM
    • Marked as answer by db042190 Wednesday, August 28, 2019 1:09 PM
    Wednesday, August 28, 2019 1:00 PM

All replies

  • There are no issues unless the tables involved in replication and those involved in ETL are different. 

    What are you trying to achieve with replication + etl? Is that for reporting? DR?


    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    Wednesday, August 21, 2019 11:23 PM
  • Hi db042190,

    Transactional replication requires a primary key constraint on each published table. Merge replication does not require a primary key, but if one is present, it must be replicated. Snapshot replication does not require a primary key.

    By default, primary key constraints, indexes, and check constraints are replicated to Subscribers.

    By the way, it is not possible to create a transactional replication on tables without primary keys, but you can use Merge replication ,that doesn't require a primary key,

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, August 22, 2019 7:08 AM
  • thx Vivek. the reason I am asking is that they (proposed etl target tables) are different from the tables already on the receiving end of replication. 

    Yes this is for reporting.

    Thursday, August 22, 2019 2:45 PM
  • thx Cathy, i'm asking only about custom etl targets here.  And those targets being db's already engaged in tran replication subscriptions.

    2 of the subjects (pk, merge) you brought up are being asked in a different post.  We aren't really interested in snapshot replication. 

    Thursday, August 22, 2019 2:49 PM
  • Hi.

    I believe this is doable, and here is why:

    I have just tried (yesterday) a scenario where I am replicating multiple Publishing (PUB) Databases into one Subscribing (SUB) Database - this worked without a glitch. 

    On the same note, if you were to create a SUB DB and designate it as a target to your Replication and then you ship some more Tables to the same SUB, I see no issues with that as long as you do not tamper with the part of the SUB DB containing the Tables replicated through Transnational Replication.

    (Note: Be careful of the following: Triggers, FK constraints, Check Constraints - if your Subscriber has these and they do not contain NOT FOR REPLICATION clause, then you might be in for an unpleasant surprise down the road, in that your replication might start breaking because of these - in my case, it was Triggers)

    A general word of caution - read through the "Properties for All Table Articles" carefully, as there might be something relevant to your case (perhaps the Parameter such as "Action If Name Is In Use" which, depending on the case, should be changed to "Keep Existing Object Unchanged", e.g. if there is already an Object with the same name at the SUB).

    Good luck. :)

    Regards,

    Bogdan


    • Edited by BogdanU Wednesday, August 28, 2019 11:47 AM
    • Proposed as answer by BogdanU Wednesday, August 28, 2019 11:47 AM
    • Marked as answer by db042190 Wednesday, August 28, 2019 1:09 PM
    Wednesday, August 28, 2019 11:46 AM
  • thx Bogdan. Are you saying that included in what you tried yesterday is an additional process that sends data to other tables in the sub db via means other than tran replication?  We already have tran replication occurring there so our dba's are probably aware of the warnings you presented for replicated tables.  I'm just worried about adding other processes that target the same db but thru means other than tran replication.
    • Edited by db042190 Wednesday, August 28, 2019 12:00 PM clarity
    Wednesday, August 28, 2019 11:58 AM
  • Hi.

    You're welcome.

    I understood you from the get-go, and what I was saying is not that I am shipping Tables via means other than Transactional Replication, but rather that the concept of having more than one source shipping to the same SUB DB works.

    Thinking a bit more about your concrete situation, I still do not see any issues with shipping Tables (which are NOT already a part of an existing replication) to the same SUB DB that is the target DB for a standard Transactional Replication.

    What's more, I have just manually added a Table to my SUB DB already subscribed for data via Transactional Replication, inserted some data in it, am observing the Replication Monitor, and it still runs fine.

    To conclude, I think you should be o.k. as long as your "moonshine" :) ETL does not touch what is already a part of an existing Replication.

    I also think that the best way to learn things like these is just to try it (preferably not on live data :) ) and learn from whatever the outcome might be (and it'd be great if you are willing and able to share the outcome here, so that we all have the opportunity to learn from it :) ).

    With kind regards,

    Bogdan


    • Edited by BogdanU Wednesday, August 28, 2019 1:01 PM
    • Marked as answer by db042190 Wednesday, August 28, 2019 1:09 PM
    Wednesday, August 28, 2019 1:00 PM