... to rebuild data on the consolidated, or to initialize a new consolidated from existing remotes, that sort of thing. Breck |
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... 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! |
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) ) begin select admin_id, data from Admin; end; 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() begin 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 from SYS.SYSPUBLICATION 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 from SYS.SYSPUBLICATION 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'; end; 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
|