Why it needs SQL Server to enable replication when build Qlik replicate from sql database?

[Wison]

From this site(https://help.qlik.com/en-US/replicate/November2021/Content/Replicate/Main/SQL%20Server/sqlserver_rep... it says If you are using Microsoft SQL Server as the source in a Qlik Replicate task, you need to enable your Microsoft SQL Server database for MS-REPLICATION.

May I know what's the function of enable sql server replication, just used to mark which tables are needed to sync? because from the function fn_dblog, there is no any information which indicates which tables are synced?

And Qlik Replicate supports 2 change processing modes(Prioritize Online Logs and Prioritize Backup logs), if I only want to choose Prioritize Backup logs(for reducing the pressure on database), whether do I still need to build up the SQL Server replication?

I am new to Qlik Replicate, any response will be much appreciated.

[SushilKumar]

Hello Wison,

To Answer your Queries, its kind of prerequisite to fulfil as Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Use replication to distribute data to different locations and to remote or mobile users over local and wide area networks.

Transactional replication is typically used in server-to-server scenarios that require high throughput, including improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing. Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include integration of data from multiple sites. Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replications, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise.

The fn_dblog() function (formerly known as the DBCC command) is one of several undocumented functions for SQL Server; it allows you to view the transaction log records in the active part of the transaction log file for the current database rather than showing info about which tables are synced?

for sync SQL server use different option like MS_replication OR MS-CDC

Online Logs/T-Logs keeps the track of ongoing changes on the database and Replication tools need to scan those Changes to capture and prepare them to apply on the Target Endpoint to Sync the Data between Source and Target Endpoints.

below option depend upon the business requirement and use case scenario.

Prioritize Online Logs - This is the default. Replicate will first look for the changes in the online transaction logs. If the changes cannot be found in the online transaction logs, it will look for them in the backup transaction logs instead.

Prioritize Backup Logs - When this option is enabled, Replicate will first look for the changes in the backup transaction logs. This can improve performance when reading from the online transaction log is slow (e.g due to lock contention) or when using file-level access to access the backup transaction logs.

If the changes cannot be found in the backup transaction logs, it will look for them in the online transaction logs instead.

Backup Logs Only - When this option is selected, Qlik Replicate will try and find the changes in the backup transaction logs only. Selecting this method results in increased latency due to the interval between backups. The actual latency time will remain constant but will vary according to the backup schedule.
Online Logs Only - When this option is selected, Qlik Replicate will try and find the changes in the online transaction logs only.

if its answers your query then request you to mark as it as a Solution

have a good time,

Regards,
Sushil Kumar

[Wison]

Hi @SushilKumar ,

Firstly, thanks for your comment on it. 

From https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-MS-REPLICATION-vs-MS-CDC/ta-p... here, we can see "Having said that, Qlik replicate does not explicitly use any of the MS-Replication mechanisms (neither agents nor the distributor), but rather leverages the SQL Server publications and articles, to write additional data about DML and DDL operations into SQL Server TLOG."

I am not sure what is the additional data about DML and DDL, I have tried to select * from fn_dblog(null,null), there is no any special marks for replicated table and non-replicated table. So, my question is what is the function of enabling sql server replication, only used to mark which tables are needed to synced?

[SushilKumar]

Write additional data about DML and DDL operations into SQL Server TLOG means here Transaction log and metadata files are generally used for Restore and recovery only. The data needed for such operations is automatically recorded in the log files. However, a log-based application or replication tool may require few additional info to be logged in the online log to require to uniquely identify a row on Target Database. This additional info helps the replication tools to identify the rows which need to be updated on the destination side.

About using fn_dblog() is used to view the transaction log records in the active part of the transaction log file for the current database

To Answer your question is what is the function of enabling SQL server replication, only used to mark which tables are needed to synced?

Replication is a set of technologies for copying and distributing data and database objects from one database to another database and then synchronizing between databases to maintain consistency. There are two replication methods supported by Qlik Replicate – MS-REPLICATION based and MS-CDC. Qlik replicate. And it is Configured at Database level.

Qlik replicate use fn_dblog() and other system tables to get info about the table which are participating in the replication Setup. Whenever Replicate task starts it prepare the Table list and get the metadata of table from various System table during runtime and keep track of change happens on those participating tables and prepare statement to Apply on the Target endpoint.

As transaction Logs are frequently written by the SQL Server process. So Replicate task Scan for changes in the Tlogs and prepare statements to apply on the target for participating tables only.