Description

Partial replication is an advanced replication feature that replicates only the specified records. Using partial replication, an administrator can enhance the replication bandwidth.

The basic principles of the partial replication are as follows:

• 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 is 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 tmp 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.
  Image Modified
•  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.

...

A table PW001P05 depends on the both tables. A record from PW001P05 will be exported only if it's linked to records in PW001P01 and PW001C06 that are allowed for exporting and the date of modification of the record in PW001P05 is greater than the lowest LastExportTime value of the corresponding records from repl_Distrib_PW001P01 and repl_Distrib_PW001C06. 

...

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

Now, switch to the Replication Tasks tab under Views -1 to add the restriction queries. 

 Follow the steps below:

1. In the list of tasks, select the task to which you want to add a restriction.
2. In the Restriction Query column, double-click the cell where a restriction query is to be added. When the Browse button appears, click it to open the SQL Editor dialog:
   
3. In the SQL Editor dialog, enter the SQL query or click Load to define the directory to the statement.
4. Click Save to... to specify the save directory on your computer or Save&Close to apply the changes in the editor. 

...

• All active crew (active means Employment End Date is empty), who are linked to the following organization units in Personal Details > the Employment tab: Hotel Personnel, Club Personnel.
• Crew with activities in the period, e.g.: 31 days (there can be any value that satisfies you your needs) back and ahead.

...

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.

Example. Let’s review an example of how to set up partial replication by replication routes.

...

• routes must be linked to a vessel or vessels.
• crew members should be linked to the needed route in the Replication Routes datagroup. For more information, see the Datagroups manual > the Replication Routes datagroup chapter.

Each route has a Date From. Starting from this date, persons’ data, linked to a particular route, will be replicated to the vessel(s) linked to this route depending on the restriction queries where additional conditions are defined.

...