March 30, 2009 at 7:26 pm
Have a system set up where we're doing a push transaction replication from database A to database B-- this should be set up as a straightforward, one-way transaction from the publisher to the subscriber, with no attempt made to have changes made at the subscriber be propogated back to the publisher.
This is definitely set up as just "Transaction publication," NOT "Transaction publication with updatable subscriptions" or merge.
The issue is that if we make changes to a table at the subscriber, those changes aren't getting overwritten like we were expecting-- for example, if I went into the subscriber and changed a price from $5.00 to $10.00, I would expect the publisher to set that price back to $5.00.
That doesn't happen.
We are getting new records, changes to existing records, and changes to other columns on the same row as our price change, but the price will never get reset unless we change the price on the publisher, or kick out a new snapshot.
Is this just "how it works," or is there something that we should have set differently to force the publisher to overwrite any changes made at the subscriber?
March 30, 2009 at 9:04 pm
I guess the first question I have is, why make the changes at the subscriber? In short, the way replication works (since you are not using updating subscriptions) is based on primary keys. When a record is updated at the publisher level, sql server will then only update the matching record on the subscriber. So lets say you update a customers phone number on the subscriber, the publisher has no knowledge of this. Then someone goes and updates just the customers address at the publisher. The publisher sends the update for the address to the subscriber but does NOT overwrite any other fields that were not updated at the publisher for the record. Basically,
Publisher LEVEL CMD = UPDATE dbo.customer SET ADDRESS = '111 North Mall Blvd' WHERE customer = 101
Subscriber LEVEL CMD = UPDATE dbo.customer SET ADDRESS = '111 North Mall Blvd' WHERE customer = 101
Just an FYI, if you go and delete or insert a record at the subscriber level directly, then someone goes and trys to run that same cmd at the publisher, Replication will fail as the command will either not be able to find the record its searching, or that pk will already exist on the subscriber and SQL Server will think its a different record. That's a pain to deal with with large datasets.
Hope this helps.
March 30, 2009 at 9:14 pm
No changes *should* be being made at the subscriber, but I can't take away the password from everyone. The hope was that if anyone did go rogue and started doing things they shouldn't be doing, the publisher would go back and clean up for us.
It sounds like we're down to wrist slapping and reinitializing snapshots when necessary.
March 31, 2009 at 6:13 am
Out of curiousity, are they dba's, sql developers, application developers? I work in a small shop so unfortunately and run into this same thing from time to time despite all my push back.
March 31, 2009 at 11:13 am
Even worse- network engineers. 🙂
March 31, 2009 at 2:03 pm
jim (3/31/2009)
Even worse- network engineers. 🙂
Oh Brother... good luck 🙁
* Noel
April 1, 2009 at 8:17 am
jim (3/30/2009)
No changes *should* be being made at the subscriber, but I can't take away the password from everyone. The hope was that if anyone did go rogue and started doing things they shouldn't be doing, the publisher would go back and clean up for us.It sounds like we're down to wrist slapping and reinitializing snapshots when necessary.
Why not assign these users to the db_datareader membership role? That way they will not be able to make any changes.
April 1, 2009 at 11:09 am
It's the group that is responsible for installing and maintaining the servers-- I can't keep SA from them unless I want to assume all responsibility for maintaining the hardware.
They normally *shouldn't* be touching the DB, but they are in the support chain, and the risk exists that someone who doesn't know as much as they think they do will try to solve something they should be escalating.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply