Answered by:

what if i want replication but dont have a pk

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.

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.

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.

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

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.

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.

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

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.

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.