> We are currently doing an exhaustive test in our QA enviornment to make
> sure
> that all stored procedures, sql commands, etc. do not cause any
> replication
> errors. During our tests I have caught a couple of errors (e.g. one table
> had cascading deletes checked and we weren't explicitly deleting the items
> in
> the table with the foreign key relationship). No matter how much people
> test
> they are bound to miss something at some point in time that acutally gets
> placed in the production environment.
>
> In the case above, you can fix the issue for future TXs, but there are
> already items in the distribution database that will continue to fail when
> you start the distribution agent. All other items to be replicated are
> then
> halted from being sent to the subscriber. I need some advice or link to a
> reference article on how to handle these issues in a production
> environment
> with the end goal of minimizing or having no downtime to the TX database
> (e.g. the publisher).
>
> I've taken a look at the MSrepl_commands and MSrepl_transactions table and
> most of the columns have binary data which doesn't give me too much
> insight
> on where the problem might be. Ideally, I would like to fix the root
> cause
> of the problem to ensure that future TXs are handled properly, remove each
> exception in the distribution database, and repair the subscriber
> appropriately by manually handling the exception.
>
> In our QA environment, I just fix the problem, drop the publication, copy
> the publisher db to the subscriber, and then setup the publication again.
> This obviously is not the best solution in a production environment.