SQL Server Performance

Prohibiting changes to subscriber database

Discussion in 'SQL Server 2005 Replication' started by ivanmca, Apr 5, 2007.

  1. ivanmca New Member

    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: <br /><br />The row was not found at the subscriber when applying the replicated command. (Error 2059<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />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.<br /><br />What I'd like to do is guarantee that any attempt to update from this application will fail and an error be returned.<br /><br />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.<br /><br />Any guidance on what to do here would be much appreciated.
  2. MohammedU New Member

    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.
  3. ivanmca New Member

    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?
  4. satya Moderator

    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.
  5. ivanmca New Member

    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.
  6. satya Moderator

    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.
  7. MohammedU New Member

    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.
  8. ivanmca New Member

    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.
  9. MohammedU New Member

    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.
  10. satya Moderator

    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.

Share This Page