Replication#

The goal of replication is to offload unnecessary data from the Online Database and to provide a operational reporting data source in a Operational Data Store (ODS). The ODS Database can support heavy report queries and a bigger retention time, allowing for a smaller Online database that will provide faster user access and application response times. Therefore, reporting should be based on the ODS database.

The following figure shows the interaction between Online and ODS databases:

The following topics provide more information on the replication mechanism:

1. Architecture
2. History Cleaning
3. Monitoring
4. Troubleshooting

Architecture#

The replication is performed using table specific insert/update statements that collect all new data from the Online Database and merges it into the ODS database. The replication between Online and ODS Databases is managed by a Job, lopping in the Online instance with the signature Critical Manufacturing Data Replication. The job loops through a configuration table that contains the list of all the items that should be replicated between both databases.

The replication Job consists of three steps (as described below):

1. The first step identifies newly created or changed tables and generates the proper insert/update statements responsible for the pulling and inserting of data
2. The second step consists on replicating the data from the Online instance to ODS looping through all the items available in the configuration table. The process basically "pulls" data from Online into ODS.
3. The third and last step is a sleep step before lopping back to the first step

At every new entity type, generic table or smart table created the system inserts the needed configurations and the system will automatically start synchronizing all the new data into the newly created tables.

History Cleaning#

The Online Database history tables will be cleaned by a T-SQL Script. The T-SQL Script iterates through all the EntityTypes that have history enabled, checks if the involved database tables are replicated, and finally deletes the records from the online database that have an exact match in the ODS database. This match is made by the primary key. In order to avoid Transaction Logs problems, all the deletes are made in bulk, i.e. 1000 deletes are made per transaction. The T-SQL Script will repeat the transaction as many times as required until all the affected rows are deleted.

Monitoring#

The replication job runs in a loop mode on the Online database. It consists of 3 distinct steps:

1. Definition of the replication instructions per defined table
2. Data replication
3. Sleep

To determine if the replication is running properly, check the job history for a successful loop through these three steps, ideally completing each cycle within a few minutes.

Troubleshooting#

Whenever an error occurs in the replication process, the Critical Manufacturing Data Replication job stops with status "Error" and is re-scheduled to run after one minute. The replication job will keep failing until the underlying error is solved. The error message for the failure can be checked in the job history details.

For any problems occurring with the replication process please contact the Critical Manufacturing support team.