One of the tools I have recently started using for Data Warehousing is Attunity Replicate. I haven’t dealt with data replication before as it always required some IT administration skills. Last month we had the Attunity Support Manager in New Zealand to run their Advanced Attunity Replicate training and learned everything about this nice application.

As you can guess from its name, Attunity Replicate replicates databases! In fact, Attunity can be used for any type of data replication. Copying data is an essential activity for Data Warehouses. It’s not a good idea to load data to the Data Warehouse straight from the operational database. ‘Extract Transform Load’ (ETL) is generally a heavy process and potentially it could lock the source database. Using a replicated database as a source for the ETL allows the operational and DW load processes to be separated.
Attunity Replicate features

• Attunity Replicate runs on its own server and doesn’t require installation on the source or target server.
• The replication process doesn’t interfere with the operational database, Attunity picks up data changes in a background mode.
• It picks up every transaction, so that none of the changes get missed even if the same record has been changed multiple times within a short period of time. You can describe Attunity Replicate as a Change Data Capture tool (CDC).
• Attunity does amazing things managing Data Definitions Language (DDL) changes, such as changes in the database structure, new columns or tables.
• It allows some simple transformation rules applied to the data. I don’t think that this could replace an actual ETL process, but there’s a wide range of audit fields that can be set up.

Attunity Replicate screen. Source: OptimalBI

My experience using Attunity Replicate
On my current project, we have multiple Oracle databases being replicated to the SQL Server to be used for ETL. Using these platforms with Attunity is easy, but prior to the training, I wasn’t sure if we used all of the Attunity features. On the training course, we’ve learned how to set up a replication task, how to manage Large OBject (LOB) fields for better replication performance and how to handle replication issues. Our replication turned out to be set up well.
The real benefit for me from the training is that now I’m confident in dealing with errors and warning messages. For example, it is possible to set up the level of detail in the replication task log. This can be set up to log all the details to be able to identify and fix the problem. Once the problem is solved, the level of detail can be changed to ‘normal’ to keep the log size reasonable.
As a part of an advanced task, we used an Open DataBase Connectivity (ODBC) interface to connect to the target database. Whilst this is not officially supported by Attunity it was a nice exercise, and I think our trainer enjoyed it as well. Attunity coped well with the unusual target database and replicated the data nicely.
Understanding all the settings and tuning features gave me confidence in using Replication correctly. But it’s the interface that got me hooked. I can’t name any other application with such a nicely designed interface!
I would definitely recommend Attunity Replicate for any sort of data replication.

‍
Masseuse of all the Data – Kate