... 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
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...
answered 04 May '11, 12:14
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;
answered 04 May '11, 11:49