Description

...

• A host site contains the full set of records.
• In case some records are excluded from the replication, the records depending either directly or indirectly (via foreign keys) are also excluded from the replication.
  

  Example. If a record from PW001P01 is not replicated to a satellite due to a partial replication restriction, then depending records from PW001P03 and other tables are not replicated either. It helps keep data consequent as well as avoid foreign keys violations.

• Deleted records are always replicated because currently it is not possible to track references from the deleted records in the repl_Deleted table.
• The partial replication implementation allows imposing a restriction on any table in the database. This approach gives much flexibility and allows solving different kinds of business tasks.
• If a record has been replicated, but it appears that it does not fit the restricting condition any longer, the record will be exported the last time.
  

  Example. Assume the partial replication is set up in the following way: only crew members who have current activities on a specific vessel are replicated to the corresponding vessel. A responsible person closes a crew member's current activity on the host site. So, the crew member and his/her activities are not supposed to be replicated any longer and the ship managers do not even know the crew member's current activity is closed. To resolve the issue, Replicator sends such data one more time after the changes have been done.

Technical Details

This section covers the in-depth details of the Replicator workflow.

• Replicator checks the PKRestrictionQuery field in the repl_ExportTasks view. If the field is empty, Replicator runs full replication. The mechanism of the partial replication is triggered only if a record in the repl_ExportTasks view is available. In such a case, Replicator checks if a table named as repl_Distrib_[Table_Name] exists where [Table_name] is a value fetched from the ImportTableName field of the repl_ExportTasks view. In case the table cannot be found, Replicator creates it. The table contains the PK columns from the [Table_name] base table and State, LastExportTime and SiteNr columns.

• Replicator executes the restriction query, gets PK values and inserts them into the repl_Distrib_[Table_Name] table or updates existing records. After that, Replicator updates the State field. The field may contain one of the values: 0, 1, 2, 3, where 0 means the record with a corresponding PK value that isn't to be exported (the record does not meet the restricting requirement). 2 or 3 means the record is to be replicated and 1 means the record does not meet the restricting condition but it met the condition during the previous replication so the record is to be replicated once again for the last time.
  

  Note
  PKRestrictionQuery may contain the standard abbreviations which Replicator uses as #DSN# , #LR# , #CSN# etc.

  
  

• Replicator gathers information about all the tables that depends on the base table. Then it creates a chain of temporary tables containing PK values of the records from the depending tables that must be replicated. The temporary tables are internal and they are visible and available for the current Replicator session only. Any other users are not able to see or access them. In case the replicator session is broken, the MS SQL Server deletes the temp tables automatically. The solution with temporary tables allows speeding up the Replicator workflow in comparison to building a complex SQL query in order to select data from depending tables.
  
• Replicator exports information by selecting only the records with PK in the distribute temporary tables and which modification date is greater than LastExportTime. Replicator can handle several restricting conditions. In case a table depends on several tables with restrictions, the lowest LastExportTime is then used.

Example. Assume that the partial replication is set up in the following way: only crew members with the current activities on a specific vessel are replicated. Besides, some competence documents are not supposed to be exported to the vessel.

...

Before getting started, let us go through the important points to be taken into consideration:

• The partial replication is triggered in case the PKRestrictionQuery field is not empty. The PKRestrictionQuery must select only the PK values. Do not use queries that select any other fields.
• For full replication, the PKRestrictionQuery field must remain empty. 
• The partial replication must be set up consistently for satellite and host sites: a satellite site must send to the host only the records meeting the same conditions used on the host. It allows preventing situations when obsolete data modified on a satellite by mistake is transferred to the host and overwrites the actual record.

To set up the partial replication, first, log into APM. Within the application, navigate to the Setup ribbon tab > Adonis Replicator > Views -1:

...

 In the Site Properties tab, make sure to:

• Set the sites taking part in the replication Active.
• Link the Organization to the correct vessel where applicable.
• Define the Current Site.

Add Restriction Queries

To add a restriction query, first, log into APM. Within the application, navigate to the Setup ribbon tab > Adonis Replicator > Views. Now, switch to the Restrictions tab under Views:
Image Modified

Now, proceed with the following steps:

1. Click the Add button on the ribbon bar. This enables a row in the grid.
2. By selecting the appropriate data from the drop-down menu, fill in the following columns: Channel Nr, Channel Source, Channel Receiver.
3. In the PK Restriction Query column, double-click the cell where a restriction query is to be added. When the More button appears, click it to open the SQL Editor dialog:
   
4. In the SQL Editor dialog, enter the SQL query or click Load to define the directory to the statement.
5. Click Save to... to specify the save directory on your computer or Save&Close to apply the changes in the editor. 

...

Navigate to Views -2 > Communication Channels where you can define the channels taking part in the partial replication, i.e. which sites will send/receive partial replication files. To do this, in the Partial column, select the checkbox for the channel from the main site (office) to a vessel:

Click Save.

Replication Restriction by Replication Route

...