Hi

Is something like the following viable, in a replicated scenario:

Take backup of the consolidated DB, then use this backup to extract client sites (instead of using the main running DB instance)?

Thanks Ivan

asked 05 Sep '11, 05:48

ivankb's gravatar image

ivankb
265101121
accept rate: 50%


I guess you are refering to the technique as described here:

"Creating multiple remote databases".

It does use a copy of the consolidated database to extract data from there. It does tell how to take care that log offsets are fitting.

permanent link

answered 05 Sep '11, 06:29

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

Thanks, I had a look - it looks about right but it doesn't mention log offsets which is a bit of a worry. Starting the backup of the consolidated db in order to run the extract could mean it then has additional changes, without some sort of log offset this could be a problem?

(05 Sep '11, 06:46) ivankb
Replies hidden

The point is that the subscriptions are created and started from the original database (as usual), so log offsets are fine. And when extracting several remotes, it's the multiple REMOTE RESET with one final COMMIT that takes care that offsets can be set in one single transaction (REMOTE RESET itself does no AUTO COMMIT in contrast to START SUBSCRIPTION).

The "make a backup immediately after starting the subscriptions" rule makes sure that the extracted data itself fits to the state of the data as to the according log offsets. - So you're somewhat saying "My extract does guarantee that the data fits the log offsets...".

FWIW, we have used this technique for several years - until we switched to a custom extraction process which was wayyyy faster than DBXTRACT and allowed us to extract from the production database directly.

(05 Sep '11, 07:09) Volker Barth

"Handle with care" and "Test very thoroughly" - as usual with SQL Remote setups:)

(05 Sep '11, 07:11) Volker Barth

Ok, just one more bit of clarity I still need here. Scenario: - start consolidated DB copy (which was made immediately after start sub subscriptions) - add or change a record - extract a new DB

Now this extract has the change or added record. What happens?

I am not suggesting I would do this, it is easy to not do it, but trying to understand the mechanics better. Thanks

(05 Sep '11, 12:31) ivankb
Replies hidden

The newly extracted remote db will contain the changed/added record whereas the production db will not. So data will be inconsistent between the consolidated and the remote.

In contrast, adding data on the consolidated while the extract is being made from the copy will generate according SQL Remote messages for the "remote under construction". Once the remote is created and starts replicating, it will apply the already existing messages and will add the changes from the consolidated.

To make it short: The copy should only be used to extract data, not to change it in any way. After extraction is done, it should be erased.

(05 Sep '11, 12:38) Volker Barth

Ok, so the key thing for me then is that the log file of the copy DB is not essentially a problem in terms of any changes logged to it - which may move any local offset. This offset is only important in the live DB.

Naturally no changes should be made to any "user data" in the copy DB, because of course this wont then be consistent with the live DB.

(05 Sep '11, 13:07) ivankb
Replies hidden

Yes, the transaction log of the copy database is not used at all - SQL Remote has to run against the "live" database and scan its log. So in the end you even might be able to use the copy db without its log to do the extraction. (Note that I haven't tested this myself.)

All that being said, I still would recommend to check whether a custom extraction process (against the live database) may help to speed up the extraction in contrast to DBXTRACT. If that is feasible, I would prefer such an approach because the particular steps to make and use the database backup are, well, somewhat complex and error-prone...

(05 Sep '11, 16:22) Volker Barth
showing 3 of 7 show all flat view
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:

×73
×37

question asked: 05 Sep '11, 05:48

question was seen: 1,029 times

last updated: 05 Sep '11, 16:22