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

MetaHertz's gravatar image

MetaHertz
76337
accept rate: 0%


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

permanent link

answered 05 Aug '11, 15:41

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

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.

(05 Aug '11, 15:45) Volker Barth
Replies hidden

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.

(05 Aug '11, 15:56) Yufei Guo

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

(05 Aug '11, 16:01) Volker Barth

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.

permanent link

answered 05 Aug '11, 15:56

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×78
×44
×39
×38

question asked: 05 Aug '11, 15:38

question was seen: 1,247 times

last updated: 05 Aug '11, 16:01