Framework to Manage Large OEM Invantive Data Replicator Environments

Wiki (en)
, ,
1

This article presents a sample of a small framework in addition to xxdru and xxhosting which enables you to more easily manage environments with many partitions such as large accounting firms.

Instructions

Execute the following steps:

  • Create a SQL Server database “repl_master“ which will contain configuration data for all your customers.
  • Each customer has one or multiple data containers.
  • Data containers such a Exact Online or Visma subscriptions are stored in a table oem_repl_data_containers defined as:
create table oem_repl_data_containers
( id                uniqueidentifier default newsequentialid() primary key
, data_container_id nvarchar(240)
)
  • The individual companies or partitions are stored in a table oem_repl_partitions which is a child of the data containers:
create table oem_repl_partitions
( id                uniqueidentifier default newsequentialid() primary key
, dcr_id            uniqueidentifier not null
, data_container_id nvarchar(240)
)
  • Define a view joining the data making them more easily accessible:
create view oem_repl_partitions_r
as
select dcr.data_container_id dcr_data_container_id
,      ptn.code ptn_code
from   oem_repl_partitions ptn
join   oem_repl_data_containers dcr
on     dcr.id = ptn.dcr_id
  • Now load a data container and some partitions:
insert into oem_repl_data_containers
( data_container_id
)
values
( 'https://start.exactonline.nl/8143826'
)

insert into oem_repl_partitions
( dcr_id
, code
)
values
( '6F4DB59B-F2C0-EA11-88A7-00155D00140E'
, '1215242'
)
  • On your Data Replicator jobs extend the database definition by an alias “master“ which opens a read-only connection to your central replication configuration database.
  • In your xxdru-based job the partitions for all the then current data containers can be selected using:
use
select ptn.ptn_code
,      sdr.alias
from   SYSTEMDATACONTAINERS@DataDictionary sdr
--
-- Filter to use solely partitions listed in the
-- master database.
--
join   oem_repl_partitions_r@master ptn
on     ptn.dcr_data_container_id = sys_context('USERENV', 'DATA_CONTAINER_ID', sdr.alias)
--
-- Select only active partitions.
--
join   systempartitions@DataDictionary spn
on     spn.data_container_alias = sdr.alias
and    spn.code = ptn.ptn_code
and    spn.is_active = true
where  sdr.alias is not null