Qlik Replicate and Third Party SQL Server Backup Tool

[SandipManjrekar]

Wondering if Qlik Replicate can integrate with third party SQL Server Backup Tool such as Quest Litespeed , Idera SQLSafe

[Michael_Litz · Accepted solution]

Hi Sandip,

Yes, there are some third party tools that replicate can interact with.

Light speed is a good example of one.

The key thing the 3rd party backup tool need to work with replicate is a command line that will allow the task to convert the 3rd party backup into native formula.

Alternatively you can configure a task to stay in the on-line Tlogs.

And the last possibly best option is to use the SQL Server (MS-CDC)  source endpoint which does not require access to the Tlogs at all.

Thanks,
Michael

[SandipManjrekar]

Thanks Michael for your prompt response.

I was wondering about using Quest Litespeed for one of our very large DB rather than MSSQL Server based on the fact one of the Best Practices Tip on Qlik Replicate mentioned that 'Replicate can read it quicker' , can you please give me more information on the internals. I always under impression Qlik Replicate can read Native MSSQLServer backups quicker than any third party tools so a bit confused by the same.  Sharing the KB link here - https://community.qlik.com/t5/tkb/articleprintpage/tkb-id/qlik-support-knowledge-base/article-id/204...

[Michael_Litz]

Hi Sandip,

It is not so much that native tlog format is quicker , it is the only format we read.

Even with a 3rd party tool, replicate will need to convert the file to native format which is why there needs to be a command line to allow this to happen.

Litespeed does have the command line that can be called.

Thanks,
Michael

[SandipManjrekar]

Hi Michael

Sorry to keep bothering. Trying to understand more  . Please comment if missing anything on following things which I understand from your earlier comments . 

1) Replicate reads only native tlog format, for conversion to native tlog format - it either depends on MSSQLServer  e.g. fn_dump_dblog (Not fully exposed to qlik replicate) or third party backup conversion command line tools (also not fully exposed to qlik replicate) 

2) Third party tools such as Quest(Dell) Litespeed further optimized reading SQL Server backup files and taken off that load from attunity replicate server and fine-tune well with MSSQLServer internals.

Is this the reason litespeed can be more beneficial with qlik replicate .  if any other key criteria which helps qlik to read faster  please let me know.

No matter what I would suggest management and Qlik SMEs to weigh in benefits of Litespeed \ Idera SQLSafe in one of the POC

Thanks , Have a good weekend 

Sandip

[Michael_Litz]

Hi Sandip,

I will try and clarify my responses.

There are two separate process going on here.

The 1st one relates to what back up tool the MS SQL admin will use to do backups of the Tlogs for the SQL server database. We do not advise on which backup tool you chose to use. It really doesn't matter to replicate as replicate can be configured in a number of ways. We do not have any performance numbers as to what tool is best for your site.

The 2nd one is how replicate can be configured to read changes from SQL server. There are 3 common configurations I will discuss.

A.) Set up the replicate task with default settings for SQL server source endpoint. This setting will need to read the backup Tlogs to get changes. If for any reason the task needs to access the backup Tlogs they must be in native format. So if your SQL admin has decided to use native Tlog backups the task will be able to read them without any additional steps. If the SQL admin decides to use litespeed then the task sourcre SQl endpoint will need to be configured with a command line call to litespeed that will convert the litespeed format to native format.

B.) Configure the task to  as above and then set the endpoint to Always stay in the on-line Tlog. With this setting the task will not try and read the tlog backups because it will not allow any transactions to be backed up until it has processed them.

C.) Configure the task source to use the SQL (MS-CDC) endpoint. With this as the source endpoint the task will never even try to read a Tlog backup as it will use the MS-CDC information to capture changes that were made to the source database tables.

NOTE: One thing to consider is no matter what form of Tlog backup you choose to use you must consider the retention policy to keep the backups on-line and available. We usually recommend that the retention policy around 3 days. That way if the task ever needs to be started with a time stamp you could go back 3 days without having to restore them.

NOTE: If you are suggesting that replicate read the litespeed backups directly then you would need to open a feature request to have product management review.

NOTE: Since you are just setting up the replication environment you may want to have a site review performed by the Qlik  professional services department which would be able to explore the options in greater detail.

I hope this helps out.

Thanks,
Michael

[MoeyE]

Hi Michael, 

You mentioned in point (A), "If the SQL admin decides to use litespeed then the task sourcre SQl endpoint will need to be configured with a command line call to litespeed that will convert the litespeed format to native format"

I just need some further clarification. Is this done in the Replicate SQL Endpoint settings or is this done on the SQL Server itself?

Do you have more information on how this is done? 

Thanks,

Mohammed