Azure Synapse Analytics - dedicated SQL pool

[bilzekek]

Hi,

I have just tested Replicate with Azure Synapse Analytics as target and MS-SQL (on-prem) as source.

in Replicate, I configured a target endpoint connection as type Azure Synapse Analytics

It seems that Replicate will first land the data in a data lake before moving them into the dedicated SQL pool on Synapse - is this correct? It appears quite slow for a full-load test I did, compared to full-loading the same data to a target that was SQL server on prem (about 2x in time it took compared to target was SQL server on prem).

[SwathiPulagam]

Hi @bilzekek ,

While loading the data from Source to target Synapse first the CSV file will be copied to the External table and from the external table, the data will be inserted to actual Target table.

Whereas SQL Server Target, Replicate will use the BCP load which will be faster compared with Synapse.

Since both are different technologies we cannot compare Synapse with SQL server target as they have their own Pros and Cons.

Thanks,

Swathi

[bilzekek]

Hi @SwathiPulagam 

Thanks for the answer. Is it true that Replicate will only create 1 CSV file at a time? or am I missing a setting somewhere on either Qlik Replicate or Synapse that will improve load times? it seems very slow even and I cannot imagine how large tables will be ...

[bilzekek · Accepted solution]

Just an update if anyone else is experiencing the same. 
I was able to reduce load time with around 67% by adjusting settings in Replicate.


Under task settings -> Full Load -> Full Load Tuning:

- increased "maxmium number of tables" 

- increased "commit rate during full load"

Under Table settings -> Parallel Load.

- Used "Data ranges" and added appropriate segments according to the specific source table.

Server:
Finally also increased cores (4 -> 8 )on the VM machine running Replicate.