Qlik Replicate: SQL replication jobs

[SwathiPulagam]

If you use a non-sysadmin account to replicate data from SQL Source, then the DBA must update the owner of all SQL Server jobs belonging to ‘REPL%’ category to the SA account.

Qlik Replicate will not create or manage any of the SQL Agent jobs. If SQL Replication is enabled to run CDC task using Qlik Replicate, then as a prerequisite Distribution must be enabled on SQL server level and publication will be created for each database which Qlik replicate trying to replicate data from.

If the below option is selected for the SQL Source endpoint, then sp_repldone is managed by SQL Server which is responsible for releasing the T-log space to the Operating system from time to time.

Let’s discuss, what all SQL jobs will be created On SQL Server. All the jobs created to manage SQL Replication will be start with ‘REPL-‘ so it is easy to identify by its Job category.

When we enable distribution on the server level, the below seven jobs will be created, and the Job owner will be whoever created the distributor. If DBA created the distributor, then their id will be the job owner.  This is one-time activity on SQL Server level.

When we start Qlik Replicate task, it will create a publication and the publication will initiate two more SQL Jobs. One is QueueReader and the second one is Log Reader.  Only one QueueReader job will exist on the server, but LogReader will be created once per database and the Job owner for these jobs will be the id used in the Qlik Replicate UI.

If we use test123 SQL id on the Qlik Replicate SQL endpoint, then for the below two jobs, test123 will be the owner. Test123 may or may not have sysadmin role depending on the requirement.

Suppose if SQL Server has 10 databases and Qlik Replicate is configured to replicate CDC data from three databases out of ten, then the below jobs will exist on server:

• Seven SQL Jobs which created at the time of distribution creation
• One QueueReader Agent Job created for the first publication (No QueueReader job will be created from the second publication onwards)
• Three LogReader Agent Jobs will be created with each publication Qlik Replicate initiate

Note:  It is recommended to have a sysadmin role for all replication SQL job owners.

As a pre-requisite, as soon as we start running Qlik Replicate tasks, change the SQL Job owner ids to sa.

The below script will generate a script to update all Qlik Replication job owners to sa account.

Script:

SELECT  'exec msdb..sp_update_job @job_name = '''+j.name + ''',@owner_login_name = ''sa'';'

FROM msdb.dbo.sysjobs J

INNER JOIN

sys.server_principals P

ON J.owner_sid = P.sid where j.category_id between 10 and 20;

Output:

exec msdb..sp_update_job @job_name = 'Replication monitoring refresher for distribution.',@owner_login_name = 'sa';

exec msdb..sp_update_job @job_name = 'Replication agents checkup',@owner_login_name = 'sa';

exec msdb..sp_update_job @job_name = 'Agent history clean up: distribution',@owner_login_name = 'sa';

exec msdb..sp_update_job @job_name = 'Reinitialize subscriptions having data validation failures',@owner_login_name = 'sa';

exec msdb..sp_update_job @job_name = 'USREM-XYZ-AL_TEST-1',@owner_login_name = 'sa';

exec msdb..sp_update_job @job_name = 'Expired subscription clean up',@owner_login_name = 'sa';

exec msdb..sp_update_job @job_name = 'Distribution clean up: distribution',@owner_login_name = 'sa';

exec msdb..sp_update_job @job_name = '[USREM-XYZ].6',@owner_login_name = 'sa';

exec msdb..sp_update_job @job_name = 'Monitor and sync replication agent jobs',@owner_login_name = 'sa';

[narendersarva]

Hi Swathi,

If the user only has read only permission. Is there a way to use this user to retrieve data without giving it sysadmin or db_owner access?

Thanks
Naren

[SwathiPulagam]

Hi @narendersarva ,

To use a Microsoft SQL Server source in a Qlik Replicate task, the user specified in the Microsoft SQL Server endpoint connection settings can be either of the following:

• A member of both the db_owner database role and the sysAdmin fixed server role.

• A member of the db_owner database role but not a member of sysAdmin fixed server role.

Thanks,

Swathi

[narendersarva]

Thank you Swathi.