replication doubt

Question

replication doubt

sandhyarao49

SSCrazy

Points: 2215
More actions
April 13, 2008 at 4:02 am

#184421

I have a small doubt. Why it is necessary to have a primary key for a table in order to publish.
After setting up transactional replication can we add another table at the publisher side.If it is possible how to replicate that table.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the primary key alows the replication service to uniquely identify every row in a table.

in that manner, it is much easier to tell the recipient database of the replication which rows are new, which were deleted, and which were changed.

If you can't identify row data individually, you would not be able to track changes. maybe you expect SQL server to "know" it behind the scenes, but it still comes down to a primary key /unique id

I'm sure you've tripped over situations where the data is in a heap, say contact records, and records in the results as you view them in rows 3,6,19 are identical, but you need to keep just one.

without a primary key, you've needed to somehow identify via an ORDER BY and MIN/MAX/DISTINCT which to keep... but if they had primary keys, you could simply delte items you didn't want to keep.

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!

dbaker-620086 SSCrazy Points: 2336 More actions · Answer 2

1. it IS necessary to have a PK for transactional/merge replication

2. it is NOT necessary to have a PK for Snapshot repl

as Lowell rightly said, the Distribution Agent (DA) needs to have an unambiguous row to perform any replay to, and the PK is that disambiguator [sorry for long words!]

if you want to add another table article in the publication, you can do so but it will not happen until you do a re-initialise of the pub. That implies that all the other existing tables will also be affected as a "rip & replace" operation so do such out of hours !

I find that I am often asked for "just another table" to be added to a pub, but choose to create a secondary pub to run alongside. At later time [e.g. Saturday] I can then tear down both and recreate one pub with all the articles.

It is generally best to elect "independent agent = false" so one DA can handle both pubs (especially necessary if you have hierarchy of [say] Customers and Orders when you want to avoid race condition [of DA for Orders attempting an INSERT before the DA for Customers has done its INSERT!]). And otherwise you would have too many distrib.exe processes competing for memory, bandwidth etc.

as a side-issue, in SQL2005 you can elect for DDL-change true so that adding columns to existing tables are taken care of [without elaborate secondary pubs as above].

works if user does

ALTER TABLE Employee add column zodiac char(1)

but not if you attempt changing datatype, ordinal etc

HTH

Dick