Requirement to use Data Movement Gateway when using MSSQL as source
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.
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 -
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?
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).