Best Practices: SQL Remote event logging

[Former Member]

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?

[VolkerBarth]

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.

[VolkerBarth]

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".

[VolkerBarth]

FWIW, we use the "replication_error" option to log all errors in a particular published table - so they get sent back to the consolidated / the remotes during replication.

Note: This does not work when replication is broken, i.e. when the according database does not apply the sent messages because of wrong log offsets. Therefore we always sent the dbremote log files (-o textfile) back to the cons and can then analyse what might have gone wrong.

[Former Member]

I absolutely agree with you and you should always test the behavior on the test environment first, if you are not sure what the behavior is.

Please try 3) on your test environment. I believe dbremote in the receiving and applying phase will log the error and skip the operation that caused replication errors. If the replication_error handle is defined, dbremote will call this handle with the error.

[VolkerBarth]

The latter goal (getting logs of failed SQL Remote runs) may be reached with the help of the OUTPUT_LOG_SEND_ON_ERROR facility - as long as the SQL Remote version does support that. (When our SQL Remote setup was designed, these options weren't available...)