Attunity Replication Limitations and Considerations

Every coin has two sides similarly,  Attunity Replication tool has it's upside and downside, The list below is a list of Attunity Replicate Limitations (what Attunity Replicate claims)  but I called it  Attunity Replication Development guidelines before committing to a client. If you are unable to replicate because your source/target database are hitting the limitation list from the below, then it is time for your database to restructure or re-design. Simple database design best practices would have prevent this when the database was initially designed. I would use this list when working on developing a replication task.

 

When working with Attunity Replicate, the following limitations apply: 

1. Replicate does not support replication of Primary Keys that are LOB data types.
2. When replicating a table which has no Primary Key on the source endpoint, LOB columns are removed from the table at the target endpoint as no Unique Index column is created in the source table. 
3. When the Limit LOB size to option is enabled, replication of structured data LOBs (e.g. XML, JSON, IMAGE, etc.) may truncate (and thereby invalidate) the structured data in the target LOB.
4. In Batch Optimized Apply mode, if the target table has more columns than the source table, any values in the extra columns will be replaced with NULL. The workaround is to create two tasks. One task for the target table(s) with extra columns and the other task for the source table(s) which have the same number of columns as the target tables. Then, run the task for the target table(s) with extra columns in Transactional Apply mode and run the other task (where the target tables do not have extra columns) in Batch Optimized Apply mode. Note, however, that updating large tables in Transactional Apply mode may impact performance. 
5. When Replicate creates a new table in the target endpoint, it defines only one index on the table. The index will either be the Primary Key or the first Unique Key (according to alphabetical order) of the table. No other indexes will be defined in the target. If additional indexes are required, these will need to be defined manually.
6. If a Unique Index/Primary Key in any of the source tables contains NULL values in multiple rows, UPDATE and DELETE operations on one of the rows will UPDATE /DELETE all of the target rows (in the Unique Index/Primary Key) that have a NULL value. 
7. LOB columns are always created as nullable on the target database. If you create the target table(s) manually, then you must set all LOB columns to nullable. 
8. If you stop a task after Full Load completes, perform some changes on the source tables, and later resume the task from timestamp (by selecting the Start processing changes from run option), some changes may not be replicated to the target. This usually only happens if the transaction logs in the source database have been deleted due to a log purge policy. In this case, Replicate will resume the task from the last change in the current transaction log. 
9. When replicating tables without a Primary Key, there is no way to verify whether a record already exists on the target. This may result in data inconsistency when UPDATE and DELETE operations are performed on the target database. 
10. Replication of calculated values is not supported during Change Processing. 
11. If a task fails with a recoverable error on the target while it is starting, it will not read changes from the source. 
12. Cached changes may be duplicated in a target table that does not have a Unique Index. 
13. A unique index consisting of several ascending and descending columns will always be replicated to the target as ascending columns. In other words, the descending columns will become ascending columns. 
14. When the source table contains an identity column, Replicate does not create the identity column on the target table. In this case, the table will need to be created manually on the target endpoint. 
15. Replication of tables with the same name as any of the Replicate Control tables is not supported.  
16. CREATE TABLE operations performed on the source while a task is stopped will be applied to the target when the task is resumed, but will not be recorded as a DDL in the attrep_ddl_history Control Table. 

These are some replication designing principle every replication engineers should consider when working on replication, if these are not considered properly, you may see a difference in data or run into various issues. These limitations are not limited to Attunity Replicate but it also applies to other replication tool like golden gate,....etc....