January 7, 2014 at 8:36 pm
Hello folks,
I have a beginner type question about SQL replication -all feedback is greatly appreciated.
I've been playing around in setting up basic SQL replication. I have created a publication | publisher and subscriber by adding just one table. I have set up transactional replication so when the publisher table gets a change, it sends it to the subscriber. All looks good.
Now, I am working with a SQL developer type person who wants to add records to the subscriber database. I told him that every time I would do a snapshot of the publication, it would wipe the added records.
Question: Is it even possible to add records to the subscriber that is NOT coming from the publisher ? My thoughts are telling me it can't be done. All data should come from the subscriber.
thank you !!
erp.cncjay
January 7, 2014 at 10:45 pm
Hi,
As per my Knowledge, If your using transactional publication and push subscription, records
will travel from publisher to subscriber. whenever the new snapshot is generated, subscriber will be in sync with publisher. Newly added records in subscriber will be wiped. But we have an option to use transactional replication with updatable subscription, in this, newly added records in subscriber will be effected in publisher.
If you want to have new records only in subscriber, then you need to have a new table which may not exists in publisher.
January 8, 2014 at 8:56 am
erp.cncjay (1/7/2014)
Question: Is it even possible to add records to the subscriber that is NOT coming from the publisher ?
Yes. It is possible to add records to subscriber directly.
Even delete and update possible. But it can impact replication when the same row comes from the publisher.
January 8, 2014 at 9:02 am
erp.cncjay (1/7/2014)
Question: Is it even possible to add records to the subscriber that is NOT coming from the publisher ? My thoughts are telling me it can't be done.
It is possible but not recommended. For, if you change the article at all, you will end up doing a SNAPSHOT, which drops and recreates the table at the subscriber. Moreover, if the subscriber adds a row, and then later the publisher adds a row that has a duplicate of any unique constraint on the subscriber, replication will fail with an error 2627.
People always want to do this, and it should never be done. If you need to update rows at subscriber, then use merge or peer-to-peer transactional replication. Both have significant considerations.
Thanks
John.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply