SQL Server Performance Forum – Threads Archive

Prohibiting changes to subscriber database

We are replicating a website back-end database between two quite remote servers and all had been going well until we actually threw the unwashed public at the site hanging off the subscriber today. For a while all was well but then started getting this error: The row was not found at the subscriber when applying the replicated command. (Error 2059<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Now my understanding of this is that it is caused by changes (deletes specifically) of rows on the subscriber end. Now we have an seperate updates DSN pointing back at the publishing server but there is the small chance that there is something sitting in the web application which will try to update the database. What I’d like to do is guarantee that any attempt to update from this application will fail and an error be returned. The application is using a particular SQL user account and I have disabled db_datawriter for that account, leaving just db_datareader. Now here is the problem however – I also have a custom role db_executor with EXECUTE permission as we make extensive use of stored procedures for both reading and writing, so I am concerned that there may be some page that will execute one of the write SPs. Any guidance on what to do here would be much appreciated.

You have to two options…
1. Remove the write access to those tables involved in replication…
2. You can use "Continue on data consistency errors." agent profile to continue even when you get the this type of error but you may end up with incosistent data…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Many thanks for that MohammedU. I don’t want to use the "Continue on data consistency errors" indeed due to the inconsistent data problem. I denied INSERT/UPDATE/DELETE for that user at the database level and hope this is sufficient?

See thishttp://msdn2.microsoft.com/en-us/library/ms151174.aspx on that error issues. Any particular reason for disabling the DATAWRITER privilege for that account as it is for application. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Hi Satya, I disabled datawriter on the destination replicated DB as I don’t want anything written into that DB at all – other than by the replication.

Is your applicaton connected to destination database by chance? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

I believe ivanmca is doing one way transactional replication, if you modify the data specially delete cause the replication to fail.
If you are using two way replication you can give the read/write access to the data…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Yes, MohammedU is exactly right there, it’s one way. We apply changes directly to the publisher DB, shouldn’t be any changes at all to the subscriber. But it is _possible_ that there is some rogue code which is trying to apply changes to the subscriber.

Run the sql profiler/trace to figure it out which process is causing the modifications to the replicated tables…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

It may not be the rogue code but as you said the part of replication does that changes, if you don’t want that changes to happen then disable the replication for those columns. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>