Requirement to use Data Movement Gateway when using MSSQL as source

[StefanBackstrand]

Is it an absolute requirement to have a Data Movement gateway when using MSSQL server as source in QCDI? If the SQL Server is hosted in say Azure, why is there a need for a Gateway if the server is available to Qlik Cloud?

I get it for onprem servers, but cloud ones? I mean, there's no "Azure SQL Server" connector in QCDI, and no option for a "native" connection to an SQL Server (if available remotely).

I'm suspecting it has to do with the way QCDI wants to read from the database - either log or CDC based, but I don't fully understand why the Gateway can but not QCDI natively.

[TimGarrod]

Yes, it is a requirement to deploy a Data Movement Gateway to perform CDC on any database system supported by Qlik Cloud Data Integration.   While you may happen to allow Qlik Cloud to access your database, most enterprises do not allow their databases to be open to the public network.   

Additionally, log-based CDC and performance is often predicated on the CDC engine residing close to the source system.  By placing the Data Movement Gateway in your VPC (Azure it appears in this case) there are no latency concerns with reading logs or CDC payloads to ensure performance and latency. 

This also provides customers the ability to control the data movement path - from source to target via the gateway server, so that data does not flow through Qlik Cloud for high volume database replication. 

QCDI leverages an E-LT based processing paradigm, where-by we replicate the data to the target platform and then perform push-down processing in the cloud data warehouse.   Under this architecture, an additional hop through Qlik Cloud adds uneccessary latency, and possibly egress charges for customers running in a cloud environment.  

If you are delivering to Qlik Cloud for QVD's - the gateway is where the CDC engine resides , thus no support for CDC from Qlik Cloud directly.  If you are running bulk payloads for QlikSense analytics, then Qlik Cloud Analytics (as you know) can connect directly to your Azure SQL server.

Regarding connectivity creation in QCDI - if pulling from Azure SQL, you should use the SQL Server CDC connector, which has a configuration for the cloud provider - 

Hope this helps.

[StefanBackstrand]

Hi @TimGarrod ! I does help, thanks. Follow up questions:

1. So, data moved from lets say an MSSQL server via Data Movement gateway, to Snowflake DW (using Snowflake as Data Platform), is still going to count against the "Data Moved" quota in capacity licensing, yes?

2. Can you elaborate on "Push-down processing" as a term?

Thanks.

[TimGarrod]

1 - Yes, any data moved by Qlik Cloud Data Integration (from 1 source to a target) is counted as 'Data Moved'.   https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Admin/subscription-value... 

2- QCDI processes transformations with an ELT paradigm, which can also be referred to as 'push-down' as it pushes the transformation logic and processing down into the target database.    All transformations in QCDI are based on this model.  From the Storage processing tracking type 2 history for analysis to the transform and data mart tasks where you can build complex SQL or model-driven data mart logic, all of the processing is done in the target (e.g. Snowflake). 

[lmcsedyz]

I hope I am not idiot, but when I do not have those policies or firewalls, I should not have to use Data Gateway. Because for this case I have to pay for extra virtual, I do not need.  If I want connect to something simple as Azure AD, which already is on the cloud (to download for example all user/group members), why I am forced by Qlik to have this useless Gateway (in cloud <-> cloud scenario)? We are not forced to have gataways for connections for apps/automations, why here? What is is the difference, when I can use freely a lot of connectors or REST API, and here ia have to use Datagateway suitable for firewalls and onpremise use.