Replication without a primary key in the publisher ?

Question

Replication without a primary key in the publisher ?

obarahmeh

SSCommitted

Points: 1595
More actions
February 3, 2009 at 6:51 am

#94555

Dear All,
I have a table that has a huge number of data, this table has no primary key.
I need to create a replication that replicate this table and any new transactions applied to this table.
And so, I think I should choose either "Transactional publication" OR "Transactional publication with updatable subscriptions", but in both options it does not accept to create this replication because the source table has no primary key.
How can I solve this problem to create a good replication? :hehe:
Regards

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

lol...you know the answer....you'll have to add a primary key to the table

...even if nothing uses it business wise, you can add an idnetity() column, make it the primary key,a dn you'd be good to go.

If your table has a clustered index, I'd leave it wherever it is, and not let it put a Clustered index on the new identity(0 column, since it doesn't help with any searches agaisnt that table.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steveb. SSC-Forever Points: 46733 More actions · Answer 2

You really need to have a primary key for replication to work, otherwise the rep-agent would not know what rows need updating.

So my suggestion would be to add primary key, is there a reason you do not have one?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

obarahmeh (2/3/2009)
Dear All,
I have a table that has a huge number of data, this table has no primary key.
I need to create a replication that replicate this table and any new transactions applied to this table.

Without a primary key, the nly replication method you can use is snapshot, which copies the entire table every time it runs. There is no way to d transactional replication without a primary key.

Why can't you add a pk to the table?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass