... to rebuild data on the consolidated, or to initialize a new consolidated from existing remotes, that sort of thing.


asked 04 May '11, 11:37

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

Regarding the whitepaper that Volker pointed to. I had a quick read. The gist of the whitepaper remains correct. With today's ML, I'd recommend using normal upload scripts instead of using forced-conflict mode, and have the upload scripts deal with conflicts directly. It should be more efficient and give you more control.

Regarding restoring the consolidated in general. I'm curious as to how this happened. Our take on the problem is to require the consolidated to have a solid backup and restore system in place, since that is a well-solved problem. We understand that this isn't always easy to do, particularly if the consolidated is out in the field somewhere...

permanent link

answered 04 May '11, 12:14

RussC_FromSAP's gravatar image

accept rate: 18%

In this case, "initialize" applies rather than "rebuild"... the brand-new consolidated is going to be constructed from data in many existing remotes.

(05 May '11, 06:10) Breck Carter

In this case, it's a kinda-sorta "refresh". ML is not in use currently and as in the whitepaper case 3 (thanks for your review and comment on it, Russ), the data is owned by the remotes. The need is to recapture that accuracy, place it on the cons, and install synchronization between them. Scripted uploads sound like the way to go.

(05 May '11, 07:38) Margaret Kam...
Replies hidden

Scripted uploads will definitely help when the remote is SA. Afterwards, if you intend to have normal (log-based) synchronization after the consolidated is created, you should switch away from scripted uploads, because it is best, and far easier, to use normal sync instead of simulating it with scripted uploads.

(05 May '11, 09:43) RussC_FromSAP

There's a very old whitepaper on this subject (though I have frankly not studied that case...).

As they say: HTH!

permanent link

answered 04 May '11, 11:47

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Assuming you are using a SQL Anywhere remote, I'd use scripted uploads in combination with a schema upgrade stored procedure that drops the scripted upload definition and creates a log scan publication after the initial scripted upload synchronization has succeeded.

Here's the schema at the remote database. Note the call to the secret (i.e. use at your own risk since we don't expose the definition) stored procedure at the end of the schema upgrade SP so that we don't download all the same data from the consolidated again after dropping and re-creating the synchronization subscriptions.

create table Admin (
  admin_id bigint default global autoincrement(1000000) primary key,
  data     varchar(64)

create procedure admin_insert()
result ( admin_id bigint, data varchar(64) )
  select admin_id, data from Admin;

create publication p1 with scripted upload ( 
  table Admin using ( admin_insert for upload insert )

create synchronization user "rem1";
create synchronization subscription to p1 for "rem1" 
  type 'tcpip' address 'host=localhost' option sv='v1';

create procedure sp_hook_dbmlsync_schema_upgrade()
  declare @pub_name varchar(128);
  declare @pub_id unsigned int;
  declare @site_name varchar(128);
  declare @last_download timestamp;

  select value 
    into @pub_name 
    from #hook_dict
   where name = 'publication_0';

  select value 
    into @site_name 
    from #hook_dict
   where name = 'MobiLink user';

  select publication_id 
    into @pub_id
   where publication_name = @pub_name;

  select last_download_time 
    into @last_download
    from SYS.SYSSYNC 
   where publication_id = @pub_id
     and site_name = @site_name;

  execute immediate 'drop synchronization subscription to ' || @pub_name || ' for "' || @site_name || '"';
  execute immediate 'drop publication ' || @pub_name;
  execute immediate 'create publication ' || @pub_name || ' ( table Admin )';
  execute immediate 'create synchronization subscription to ' || @pub_name || ' for "' || @site_name || '" type ''tcpip'' address ''host=localhost'' option sv=''v1''';

  select publication_id 
    into @pub_id
   where publication_name = @pub_name;

  call dbo.sa_sync_sub( @pub_id, @site_name, 'set lastdownloadtime', @last_download );

  UPDATE #hook_dict SET value = 'on success' WHERE name = 'drop hook';
permanent link

answered 04 May '11, 11:49

Reg%20Domaratzki's gravatar image

Reg Domaratzki
accept rate: 37%

Should have mentioned that your upload_insert scripts at the consolidated database will need to handle the situation where the row may already exist using the sample above.

(04 May '11, 11:56) Reg Domaratzki

"...secret..." - that's yelling for the fascinating "undocumented" tag, isn't it?

(04 May '11, 12:07) Volker Barth

Yes, SA10 remotes, soon to be SA12, Oracle consolidated. MobiLink is not currently being used.

Scripted uploads sound like the way to go. Your suggestion of the schema upgrade stored procedure is exactly what would work since the "upload everything sync" is, as you have realized, a one-time thing.

The secret SP is going to be invaluable!

(05 May '11, 07:51) Margaret Kam...
Replies hidden

Just out of curiosity: Do those remotes already use disjunct PKs (GLOBAL AUTOINCREMENT or the like - as part of a "already prepared to being consolidated" design)? - Otherwise I would think that making them unique would not be the easiest step...

(05 May '11, 07:57) Volker Barth

Yeah, I wish they did, Volker. You are quite correct. That is not going to be the easiest step....

(05 May '11, 08:09) Margaret Kam...
Replies hidden

But Breck and you seem to have found a valid approach, which is not as difficult as I would have thought:)

(06 May '11, 07:33) Volker Barth
showing 4 of 6 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



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:


question asked: 04 May '11, 11:37

question was seen: 11,804 times

last updated: 06 May '11, 07:33