that one limitation in replication
-
Hi as shown below a replication target requires a primary key. if we want to replicate from the db level, how do we deal with target tables whose source has no pk?
-
Do you have a unique Clustered Index or Non-Clustered Index on the table or a column that could take one. If the answer is "Yes", at a PK constraint to the column or columns to match the index.
If the answer is "No", then add a column that would default to a new value of a SEQUENCE and used that as a PK.
If you have a date column that's ever-increasing, still add a SEQUENCE column (you really should avoid an IDENTITY column) and than make a PK based on two columns, the first being the date column and the second being the SEQUENCE column.
Other wise, you're going to have to "roll your own" for replication and you're really not going to like that at all.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
thx jeff. this is a db in an off the shelf erp. its probably not just one table. we cant add things like that without voiding our contract, at least not on the source.
I'm wondering then if the flavor of "CDC" coming out of the logs is a better option...
..or if starting at the db level choosing only tables with pks etc is a way (or even possible) to start this kind of thing and using something different for the rest .
-
If possible, you can create the indexed view on top the table and add Pk to the indexed view and replicate the view.
Or you can exclude tables without Pk keys.
Deepesh Dhake
Database Administrator
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply