none
what if i want replication but dont have a pk RRS feed

  • Question

  • Hi I have an incident history table that doesn't have a primary key. It is provided by a 3rd party so I don't have much of a choice there.

    it has a clustered index on the incident id (which is unique on the table where the data originates) and a modified datetime.  But without even looking I suspect that the incident id and mod datetime don't necessarily have to be unique.

    The originating table is the source for a subscribed replication table.  The ultimate would be for the subscribing db to be the recipient of something like a replicated history table as well.

    do I have any options?  I think one option is to set up a separate db (I heard log shipping cant go to a db that is enlisted as a replication subscriber) where log shipping would take place but I've heard log shipping is voluminous.  and if the source of log shipping is the same tran log i'm used to seeing, i suspect the source is recovery full and therefore going to be much more voluminous than a recovery simple db source. 

    • Moved by Tom Phillips Thursday, August 15, 2019 7:51 PM Replication question
    Thursday, August 15, 2019 7:33 PM

Answers

  • Since it is a history table, I assume that no rows are updated, and that the datetime column is growing monotonically. In that case, the effort for rolling your own may be reasonable. Although without an index with this datetime column as the leading key, it is not going to be fun.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by db042190 Wednesday, August 21, 2019 4:54 PM
    Thursday, August 15, 2019 9:12 PM
  • Hi db042190

     

    You could use Merge replication, that doesn't require a primary key, and it automatically creates a rowguid column if it doesn't exist.

     

    You are talking about history tables, so is there no new data updates or only a small amount of data updates, and you don't care if the data is synced immediately. Then you can use snapshot replication.

     

    If you must use transaction replication, then you can only add a primary key, after copying, please refer to  https://searchsqlserver.techtarget.com/feature/Replicate-tables-without-primary-keys

     

    Best regards,

    Dedmon Dai


    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

    • Marked as answer by db042190 Wednesday, August 21, 2019 4:54 PM
    Friday, August 16, 2019 9:13 AM

All replies

  • Since it is a history table, I assume that no rows are updated, and that the datetime column is growing monotonically. In that case, the effort for rolling your own may be reasonable. Although without an index with this datetime column as the leading key, it is not going to be fun.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by db042190 Wednesday, August 21, 2019 4:54 PM
    Thursday, August 15, 2019 9:12 PM
  • Hi db042190

     

    You could use Merge replication, that doesn't require a primary key, and it automatically creates a rowguid column if it doesn't exist.

     

    You are talking about history tables, so is there no new data updates or only a small amount of data updates, and you don't care if the data is synced immediately. Then you can use snapshot replication.

     

    If you must use transaction replication, then you can only add a primary key, after copying, please refer to  https://searchsqlserver.techtarget.com/feature/Replicate-tables-without-primary-keys

     

    Best regards,

    Dedmon Dai


    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

    • Marked as answer by db042190 Wednesday, August 21, 2019 4:54 PM
    Friday, August 16, 2019 9:13 AM
  • thx erland and dedmon. the index does include a datetime as the second column.

    can merge replication on history target the same db that currently subscribes to replication on the base table?  Or would I need to create a separate database?  same question for snapshot replication and same question for roll our own (ie can I target a db that is already a replication subscriber as also being the target of roll my own updates?).

    I believe that even inserts on the base table are recorded in history, I would say unfortunately. And the volume is not bad about 30 million / 730 days / 20 buildings = 2k on average per day per building.  I'll double check this math.  We replicate I believe every 15 minutes so we are talking about small numbers incrementally.  And we keep a separate db for each building's replication subscriptions.

    i'm not sure what this means "If you must use transaction replication, then you can only add a primary key, after copying" but i'll read the link.

    and what about log shipping and perhaps cdc?  I see we are now in a different forum but those still seem like legitimate questions.

    Friday, August 16, 2019 11:58 AM
  • dedmon's link is interesting but we don't really have that option with it being a 3rd party app. From what I remember inserts can be done on views so in theory its viable. 
    Friday, August 16, 2019 12:21 PM
  • I looked briefly at merge replication https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication?view=sql-server-2017.

    this statement worries me..."To accomplish this merge replication adds the column rowguid to every table"

    With this being a 3rd party app and that statement making it sound like the unique id is added physically, I think we would have a problem.  unless perhaps this (rowguid) is something like a non persisted computed column but I don't know what a a select * does with computed columns.  and we know select *'s (if they exist) in the 3rd party app could suffer from changes  like this.

    I'm also concerned about the triggers as triggers bother me in general.  But my understanding is that triggers occur on tran replication as well, so if that is true then i'm not as worried.

    Friday, August 16, 2019 12:36 PM