Attunity Replicate best practices

[nkarabin]

We are working with Attunity Replicate version 7.0.0.555. A few questions come up in my mind:

1. Currently we configured one Attunity task and attached there around 150 tables to replicate. Can you please advise us best practices? What is better to have one task for all the tables or multiple tasks for the smaller bunch of tables in each (or depending on general data size that should be replicated)? What are pros and cons?
2. Is Attynity Replicate has any restriction for the number of tasks on one machine?
3. Is there any data lose possible if Attunity task was stopped for some period of time (e.g. 24 h) and then resumed?
4. Imagine the case that Attunity task was updated by adding new tables to replicate. Is it possible to make full load only for newly added tables and do not reload the old tables?
5. Can Attunity Replicate server configuration be exported and automatically imported back by Phyton script?

[jonna]

Hi @nkarabin 

There is some help in the User Guide HERE at the start of Chapter 7. Whilst there is some general information, there is not really much detail but it might be of use to you.  

@anyone & @Qlik support - please can you offer some guidance ?  I am also particularly interested in  @nkarabin's point 1. and point 2.

Thanks 👍

Jonna

[slewis]

@nkarabin I'll offer my thoughts, but as I am not affiliated with Qlik, please consider them just my experiences.

1. The short unsatisfying answer is...it depends. If you are performing full loads each time the task runs and the 150 tables replicate in a satisfactory amount of time, then you're good with one task. If some tables need to be full loaded more often then others, then split them according to schedule. If some tables are CDC enabled and others are Full Load, then split them. If all tables are CDC enabled, but some have higher change rates then others, then it may make sense to split the tasks so that the high change rate tasks do not negatively impact the slower changing tables. Also consider that each task running against a source endpoint creates threads and allocates resources against that source endpoints DBMS. Splitting your tasks is easy, but test the impact on your source endpoints system to ensure simultaneous task runs are not negatively impacting your source database.

2. I have not come across any limit to the number of tasks that can be configured. However, the more tasks running simultaneously the higher the load on the Replicate server and the more likely it is to run short of memory or CPU.

3. Your question only seems to be relevant to CDC configured tasks, so I'll answer it with that assumption with another "it depends" answer. When your task is manually stopped it stores a "bookmark" of the last transaction processed. When the task is Resumed it will look at your source systems transaction log (called different things in different DBMS but I'll just use the common term) and pick up where it left off. However, this is predicated on the transaction log still being available. Some databases are configured to purge transaction logs after a period of time to save disk space. You can resume your task up to the point the transaction log is purged at which point you will have to perform a reload to get started again.

4. Yes, and I love this feature. Again, the question only makes sense for CDC enabled tasks. While the task is running, add the new tables. When you Save the task you will be prompted to stop the task. Allow the task to stop and the Resume the task. Qlik replicate picks up replication of the "old" tables and recognizes that new tables are present. These get full loaded and then switch into CDC mode once the full load is complete.

5. This is the question I cannot answer. I haven't seen any server level export. You might look at the Replicate API to see if this can be accomplished. If you find a positive answer, please post it as I'd also be interested.

[KellyHobson · Accepted solution]

Hey @nkarabin and @slewis ,

#5

 I would advise doing this with powershell (windows) or shell script (linux) so you can interact directly with repctl command.

Example shell

$./import_export.sh

where

$ cat import_export.sh

#!/bin/sh

export LD_LIBRARY_PATH=/opt/attunity/replicate/lib:/usr/lib64:$LD_LIBRARY_PATH

/opt/attunity/replicate/bin/repctl -d "/opt/attunity/replicate/data" exportrepository

sleep 5

#plus some process you need done here

/opt/attunity/replicate/bin/repctl -d "/opt/attunity/replicate/data" importrepository json_file=/opt/attunity/replicate/data/imports/Replication_Definition.json

exit

What’s your use case for importing and exporting in an automated way?

I posted recently a topic requesting a more defined way of promoting content between environments and it may be of interest to you.

https://community.qlik.com/t5/Suggest-an-Idea/Promotion-process-for-task-between-environments/idi-p/...

Additional ref using import/export repo

https://help.qlik.com/en-US/replicate/November2021/Content/Replicate/Main/Installation/Upgrading.htm

Best,

Kelly 

[Dana_Baldwin · Accepted solution]

Hi @nkarabin 

Regarding item #2, I am not aware of a limit on how many tasks can be defined on one Replicate server, but there is a limit as to how many can run simultaneously. As a rule of thumb we recommend up to 100 concurrently executing tasks per Replicate Server machine, your mileage may vary.

This is mainly limited by Windows limitations regarding resources for processes running as services. it can be worked around by changing the sharedsection parameter in the Windows registry.
Please see: https://stackoverflow.com/questions/17472389/how-to-increase-the-maximum-number-of-child-processes-t...

Thanks,

Dana

[Heinvandenheuvel · Accepted solution]

An older topic which came back to live.

 >> 1. Currently we configured one Attunity task and attached there around 150 tables to replicate. Can you please  advise us best practices? What is better to have one task for all the tables or multiple tasks for the smaller bunch of tables in each (or depending on general data size that should be replicated)? What are pros and cons?

150 table is not much at all. KISS One task for all. 1500 tables you might worry, 15,000 needs review and testing (slow startups). The biggest overheads for multiple tasks is typically the source change reader. This can be mitigated using a Logstream task design moving that overhead to the Replicate server.

>> 2. Is Attunity Replicate has any restriction for the number of tasks on one machine?

For a Windows server special windows settings are needed to handle more than 100 child processes for the Replicate service. No big deal. Still KISS. Too many tasks is too many thins to watch, too many logs, too many connections to source and target databases

>> 3. Is there any data lose possible if Attunity task was stopped for some period of time (e.g. 24 h) and then resumed?

Not as long as the (archived) change logs remain accessible. This can be mitigated using the logstream task design pulling the changes already from the source DB keeping them available on your own Replicate design terms versus having to deal with the (source) DBA choices.

>> 4. Imagine the case that Attunity task was updated by adding new tables to replicate. Is it possible to make full load only for newly added tables and do not reload the old tables?

That's the automatic, default, behavior.

>> 5. Can Attunity Replicate server configuration be exported and automatically imported back by Phyton script?

Yes. This is best done with the Enterprise Manager API using cUrl, Powershell, or indeed  Python which has its own documentation and example section. It can also be achieved using the REPCTL command through any scripting language of your choice. Others have provided some details already.

Hein.

[KellyHobson]

@Heinvandenheuvel @nkarabin 

Thank you Hein for mentioning Enterprise Manager API which can be integrated with Python.

Command list for reference:

https://help.qlik.com/en-US/enterprise-manager/November2021/Content/EnterpriseManager/EnterpriseMana...

Best,

Kelly

[Barb_Fill21 · Accepted solution]

Hi @nkarabin 

Just extra info regarding max number of tasks.  I have seen a range of tasks that could be running and as everyone else states it's dependent on Heap size, memory, CPU etc. on the Windows environment.  I have seen from 84 tasks to 104.  For the customer with 104 tasks, in trying to start the 105th, the task would not start.   I definitely agree with @Heinvandenheuvel  that it becomes quite a lot to try to manage, especially connections. And in fact, you may want to consider using Losgtream.   You can read about it in HELP on the Replicate Console. 

Hope this helps.

Thanks

Barb