Transactional replication in SQL

vyanku

In transactional replication whole table is get replicated or only that transaction is replicated in which the transaction is occured?

garak

only the rec with the modification will be send.
On SQL 2000 the whole Rec since 2005 only the modified field.
If you send a SnapShot, all Datas of the Table will be send.

Regards

ara3n

Just the changes that occurred in the transaction.

Transactional replication is unidirectional from the publisher to the subscribers. Data can be sent on a schedule or in real time. As data is transmitted to the subscriber, all data changes are processed in the order they were made on the publisher.

vyanku

But what happend in my case.
I had two database one is Publisher and other is Subscriber.
If I change the records in Subscriber and Not in publisher then the records in the subscriber should be remain same.
Because it replicates the data only when we change the records in Publisher.
Suppose the Book rate = 0 in publisher and book rate = 100 in subscriber.
And when snapshot is created and data is replicated then the book rate becomes 0 in subscriber.
It overwrites the values in subscriber by publisher.
Please suggest How can I solve this problem?

DenSter

What are you talking about? Replication is meant to provide a copy of a database. For purposes of having a copy of the database to do reporting, or to have a ready database in case the publisher goes down. Every time that a change is made in the publisher database, it is SUPPOSED to send that to the subscriber. You're not supposed to go and modify data in the subscriber database.

Do you want to have it so that you have two databases, and that changes in each of those are replicated in both databases? What's the point? Is there a reason why you can't connect all users to the same database?

garak

What you need is the Merge Replication (but these can bring some troubles if the same rec is also changed on publisher or other subscriber -> which one wins?).
Or you use SSIS Packaged (DTS on SQL2000) to get all changed data from a Subscriber, send it back to the publisher and the publisher send the data to all subscribers.
But also in this case -> which change wins? ;-)

Regards

garak

So, the principle of a transaction replication is, that you have one Masterdatabase (the publisher) and one / more Subscriber Databases (like Cash Points).
In the MasterDB the master datas are stored and changed and never on Subscriber databases.

On our subscribers the master datas never changed. Only dynamic data will be created. And these dynamic data i get with SSIS and send it back to the publisher.
If these dynamic datas for example sales orders a NAS on the publisher processed these data.

Regards

garak

Here a link for Merge Replication

Regards

vyanku

I want that when I replcate data it should replicate all data except book rate field. This field should not be replicated from publisher to subscriber.Because it will be different in publisher and subscriber.Also It should not pass null value a the time of replication.

garak

if the datas are send back to the publisher, what are you doing with this data? Processing?
With a little bit more infos whe can give our commendation.
With your last sentense i would not prefer Merge Replication. I would use Transac. Replication and SSIS Packadges to get the changed data sheduled in time.

Regards

vyanku

Yes U r write thats why I am using transactional replication.
I dont want to send back this data to publisher.
I just want to maintain value in "book rate" field differently in publication and subscriber.
So I want to skip that field from replication.But how can I do this?
How can I skip any field from table to replicate?
Is the DTS package is more useful than transactional replication in this case?
Is DTS is more safer and appropriate in this case? :-k

garak

What? After your last sentense i'm confused.

This you have (simple style):

Publisher >>>---- sends all fields from THE TABLE to ---->>>> Subscriber

This you want: (?)

Subscriber >>>

---

sends defined fields from THE TABLE back to

---

>>>> Publisher --- >> !!!!!

---

these Datas should not be send back to the other subscirbers !!!!

---

True or false?

If you will not send specific fields from a table to a subsc. from a publisher you can change this in the Publication Properties -> articles -> Publish Column = false

DenSter

So you replicate the production database, so that you have an identical copy of it, except you want to have different 'book rates'. What do you mean by 'book rates'? Different rates means different amounts. Amount are calculated by multiplying quantities and rates. Are you sure that these rates are the only exception?

Are you trying to keep two sets of books? Is that legal in India?

vyanku

If you will not send specific fields from a table to a subsc. from a publisher you can change this in the Publication Properties -> articles -> Publish Column = false

I had tried this .But it shows error.

I just want to send the whole data of the table except one field from publisher to subscriber.

kine

1) All changes on subscriber are done through special Stored Procedurs on the database. You can modify the SP to not update your field.
2) By default, if there is update on publiser, Delete and Insert pair is done on subscriber. You need to enable special trace flag to have UPDATE statement on subscriber... (see MS KB for more info about trnsactional replication and update pairs...)
3) You can exclude the field from replication and have it only in the target table and it will not be overwritten, but you need to solve the DELETE/INSERT problem...

bbrown

One way to approach this is replicate into a staging table. Then use triggers to push the appropiate data to the real tables. This lets you be selective about what and where the data moves. It also lets you support scenarios where there may be schema differences between the databases. Systems involved in replication are not always exact copies of each other.

vyanku

Thanks My problem is solved .
I had done changes in update sp. Its working fine. \:D/