JADE  Environment Development Ideas

Ensuring schema AND data consistency between RPS database and SQL server

Background. Changes were made to some classes that resulted in one table being dropped and having to re-populated and for 5 other tables being added to the RPS mapping and having to be populated. The 'tableAlter' script was generated and executed. This contained:
- the commands to drop the original table
- the commands to define the revised table and its procedures as well as the definitions for the new tables and their procedures


The execution of this script meant that the JADE RPS database and SQL server database are now 'schema consistent' . Then the RPS node went into the extract phase creating the 'autoBulkInsert' script and extracting the information to be used to BULK INSERT the table information.

Unfortunately the RPS node was manually (by mistake) terminated during this phase so the extract and load phases did not complete. 
 

When the RPS node was restarted, the JADE RPS node and SQL server database were 'schema consistent', therefore the datapump started and processed data changes to the SQL server database.  However, the two databases were NOT 'data consistent'.  

 
It took several hours for this issue to be identified and after live transactions had been applied to SQL and as a result the SQL server database had to be rebuilt. This process took a considerable length of time (5 days in total)
 
Request. There needs to be some way of ensuring that the RPS and SQL server database are not only 'schema consistent' but also 'data consistent'.  
 
If the datapump had indicated an inconsistency (schema or data) after the jadrap was restarted, we could have quickly identified what the issue was and taken appropriate manual steps to complete the extract and load.  
  • Martin Jagers
  • Aug 18 2019
  • Customer Feedback Requested
  • Attach files