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 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 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.
  
  

• 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.

So, PKRestrictionQuery for the PW001P01 table in repl_ExportTasks may be specified as:

PKRestrictionQuery for the PW001C06 table in repl_ExportTasks is defined as "SELECT C06.CODE FROM dbo.PW001C06 AS C06 WHERE C06.CODE <> 'FIN'"

In this case, repl_Distrib tables will be created both for the PW001P01 and PW001C06 tables.

A table PW001P05 depends on 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. 

Setup

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:

Open

 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:

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:
   
   

   Open

   

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. 

Set Communication Channels

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

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

You can set up replication of personal data between necessary sites using the routes in APM. For this, first, add the routes in General Codes, keeping in mind the following:

• 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.

So, we have to add the restriction query for two replication tasks on the main site only: HOST_P001OLEDOCS and HOST_PW001P01.

In our example case with defined conditions the queries will be the following:

• The query for HOST_P001OLEDOCS:

  SELECT ODP.DOCNO as DOCNO  FROM REPL_SITE STE JOIN PW001C109_VES VES  ON STE.NUMORGID=VES.NUMORGID              AND STE.SITENR=#DSN#         JOIN PW001P01_REPL_ROUTE RTE ON RTE.CODE=VES.CODE                AND Getdate() <= ISNULL(RTE.DATETO,GETDATE())                AND DATEADD(dd,-21,RTE.DATEFROM) <= GetDate()    JOIN repl_OLEDOC_PIN ODP ON ODP.PIN = RTE.PIN UNION SELECT DOCNO AS DOCNO   FROM repl_OLEDOC_PIN  WHERE PIN=-1

  
  
  

• The query for HOST_PW001P01:

  SELECT RTE.PIN as PIN FROM REPL_SITE STE JOIN PW001C109_VES VES ON STE.NUMORGID=VES.NUMORGID AND STE.SITENR=#DSN# JOIN PW001P01_REPL_ROUTE RTE ON RTE.CODE=VES.CODE AND Getdate() <= ISNULL(RTE.DATETO,GETDATE()) AND DATEADD(dd,-21,RTE.DATEFROM) <= GetDate()

  
  
  

So, according to our example queries, the system replicates data of all crew members linked to the replication routes with Date From starting from today and + 21 days to the vessel(s) linked to this route.