March 14, 2006 at 3:21 pm
We have two subscribers to one publication and it is a transactional
replication with continuous distribution and only one way - publisher
to subscribers. The distributor is same as the publisher. The
database on one of the subscribers is not in sync with the published
database. One table is missing one record. There are no reported errors by any of the agents. I am looking for a starting place to see what exactly is causing the transaction not getting distributed to one of the subscribers.
The publisher is on SQL Server 2000 SP3a.
Thanks
KR
March 16, 2006 at 4:59 am
KR
Do you have any filters defined in your publication that may cause certain rows not to be copied? I suppose this is unlikely to be the reason if one subscriber is in sych and the other isn't. Is it possible that the data was copied over but that somebody has deleted one or more rows on the subscriber?
By the way, if you are using replication then I'd recommend upgrading to SP4 on both servers. I think there were one or two replication bugs that were fixed in SP4.
John
March 16, 2006 at 8:22 am
John,
After much digging it does seem that the missing data was once replicated and was probably was deleted, but the two users who have access to update this database are web app developers who tell me their code is just using the stored procedures we have created . None of the stored procedures do any table updates, deletes, or inserts. Anyway I have reinitailized the subscriptions, and I am planning on creating triggers on the tables to rollback any transactions that are attempted by any process other than replication. IF you have any suggestions how I can prevent users from able to make changes to the tables other than through permissions(the developers have full access on this server - it is a dev servers), please share.
Thanks
KR
March 17, 2006 at 6:27 am
KR
If you're thinking of creating a trigger to roll back transactions, I should think you'd be better off not allowing those transactions to take place in the first place. And yes, you'd have to do that by changing permissions. Your developers won't mind this if they only access the data through stored procedures!
Otherwise, how about writing a trigger that doesn't roll back the transaction, but merely puts a record of it into an audit table? That way you'll be able to catch the culprit red-handed!
John
March 17, 2006 at 8:21 am
That is probably a better idea! - creating an entry into the audit table. I will certainly look into that option. The only problem with the permissions thing is it is owned by the web developers, and hence sys admins on the box, so we don't have much control there.
Thanks Once again for all your input
KR
March 20, 2006 at 2:18 pm
Craete a user in the subscription DB with readonly access. Change the existing devloper access users' password. All developer should only have read-only access. The SPs have to update to have th proper permisson to be executed the the developer etc. Good luck !!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply