I am in the process of a Database recovery and sending out extracted databases to our dozen or so remote clients.
This time I would like to have some better logging for the remote databases, to help in troubleshooting any errors that may come up and improve our replication architecture.
My understanding is that I run "SET REMOTE FTP OPTION PUBLIC.OUTPUT_LOG_SEND_ON_ERROR = 'YES'" against the remote db's, and use the [-ro "remotes.txt"] switch for the consolidated dbremote.
Also, there is the option to call a stored procedure when a SQL statement fails in replication, SET OPTION PUBLIC.Replication_error = 'sp_LogReplicationError' (for example)
1) Can i use the PUBLIC keyword in both cases to apply to all the unique remote db's and the consolidated db ? (or would I need to specify the remote name?)
2) If so, should I simply apply these against the consolidated db before extraction, since I have to extract all remotes ?
Any thoughts on other ways to proactively get "event logging" back from the remotes?
oh, and (3) Is there a case when dbremote will TRUNCATE data in a table because of Replication errors?
asked 05 Aug '11, 15:38
This is not a real answer, but several years of dealing with SQL Remote have taught me to test, test and test every relevant change in a test environment before going into production...
That being said, I guess the answers are "yes", "yes", and "no".
answered 05 Aug '11, 15:41
Another caveat: AFAIK, SQL Remote's OUTPUT_LOG_SEND_ON_ERROR facility (with the related options) has been introduced in version 8.0.2. Therefore if your remotes are still on v7, I don't think they will be able to use this feature.
answered 05 Aug '11, 15:56