I was working with MS SSIS for almost four years before I bumped into Attunity Replicate. At first glance it looks like the two products can do the same job, they both are able to copy the data across different environments. Let’s have a closer look.
Attunity Replicate is made for data replication. Its main goal is to bring the exact copy of the data across databases, although Attunity is generous enough to add some simple data modification rules to the data replication. Microsoft SQL Server Integration Services is an ETL tool, ETL stands for Extract Transform and Load. Data transformation is an essential part of SSIS, but it also can copy data without transformation. So, MS SSIS can do the same task, whereas Replicate specialises on it.
Attunity Replicate can be set up to pick up all the source tables to replicate, alternatively tables can be selected by name or name patterns. In SSIS, each table should have its own task or package and each of the packages require deployment and scheduling.
Where a new table appeared at the source and its name matches the replication pattern, Attunity will create a table in the target database and start replicating it immediately. It amazes me how Attunity can handle data structure changes. No data will ever be missed. MS SSIS doesn’t have any automated solution to capture data definition changes. I guess it makes sense, as it leaves ETL developers to make a decision on how to process new columns. But if the task is to capture all the data all the time, Replicate does it better.
Once data is fully copied, the next step is Change Data Capture, CDC. Replicate captures new rows, data updates and deletions. These changes are automatically applied to the target tables and the history of changes is stored separately. With SSIS, the developer should explicitly describe how to manage changes, such as any data load logic and destination tables should be designed and developed. SSIS has a task called “Slowly Changing Dimension” which could generate some code to handle CDC. In my opinion, this task works slowly with large amounts of data. You can argue with me on this, but hey, try Attunity first and then you’ll see what I mean. Replicate can process these changes in no time. Also Replicate processes a list of tables in one go, so if multiple tables are updated in one transaction, this would be captured altogether. As each table is a different task in SSIS, the integrity of such transactions may be broken.
Replicate has a nice web-browser interface. The visual interface helps with setting up the replication task, it also simplifies the replication monitoring. Attunity shows how many inserts, updates and deletions happened since the last task start for each table, and when the last data update has happened. The SSIS development interface is also visual and reasonably simple. SSIS could show error messages and warnings, but row counts need to be set up separately for each flow. And even after the insert, update and delete record counters are set up, it’s not easy to access this information unless some extra monitoring logic is implemented.
In my opinion, Attunity Replicate beats SSIS approach for data replication on many criteria. Microsoft SSIS is a good tool for the purpose it was created; it’s great for business rules implementation, complicate transformations and intelligent filtering. But Attunity Replicate is great if you need to move data between different types of databases (and data files as well) with the elegant solution for tracing history and picking up the metadata changes.
Masseuse of all the Data – Kate
Kate blogs about the details that make the Data Warehouses work
Want to read more? Try How to Maintain Data Vault Standards or more from Kate
We run regular Data Vault course for business analysts, data architects, and business intelligence developers in Wellington and Auckland. Find out more.