QLIK Configuration with SQL Server HA

[shubhampatni]

We have SQL Server HA setup using Always On Availability Group and we might have failover and failback. We might go through these scenario and so would like to know if there is any change or additional

configuration or tasks/steps need to perform on Qlik side.

Scenario 1-

If replication started from Qlik task and data is loaded to Qlik replication server or in-transit to replication server and not yet transferred to target (snowflake) and in this time frame if SQL server failover happened

but it is taking time apply the logs to secondary on SQL Server (so technically the transaction is valid or available on secondary for user).

Question: What will happen to the data which is being transferred to replication server or to target (snowflake), is it going to rollback or how we can confirm the data written on target is valid as per

SQL Server

Scenario 2-

We have to use listener name or IP as an SQL Server source for the Qlik task so listener will redirect to current primary SQL Server host

Question: Do we need to change the SQL Server source configuration if there is failover happened and the listener redirected to the new primary SQL Server host.

[BHR]

I can give you information on Scenario 2.

We have a similar configuration from SQL Server to Snowflake.  We've established a SQL Server Alias on the Qlik Replicate Application Server that handles the source failover or failback to the original node.  

you can create an alias using the SQL Server Client Network Utility. This has come installed automatically on every operating system from Windows 2000 on. To bring up the utility, click on Start, then Run, and run cliconfg.exe. To view or create aliases, click on the Alias tab

Good Luck!

Brett

[PGN]

Scenario 2:  

The SQL Listener is responsible for directing connections to the correct node.  If you use the SQL listener in your endpoint connection, then there is no need to change anything in Replicate.

In general, with Availability Groups (AOG/Always On), the best practice is to create a remote distributor and set up your AOG replication to redirect based on the current node.  There are a number of online sites that provide information and a step-by-step guide to do this.

[SwathiPulagam · Accepted solution]

Hi @shubhampatni ,

Scenario 1-

Question: What will happen to the data which is being transferred to replication server or to target (snowflake), is it going to rollback or how we can confirm the data written on target is valid as per SQL Server.

A) Replicate read data from transaction log files and it will not apply the data to the target until it finds a commit for the specific transaction. If there is a long running transaction on the SQL server side and replicate read those uncommitted transactions then it will be stored on a sorter location. For any reason, if the transaction rolled back on the SQL server then the transaction will not be applied on the target side. I can say data consistency will be maintained.

Scenario 2-

Question: Do we need to change the SQL Server source configuration if there is failover happened and the listener redirected to the new primary SQL Server host.

A) If you use listener name(replicate always points to primary node) in endpoint then replicate can handle failover situations very well. If it is not working as expected then please create a case we need to review the environment and suggest the solution.

Thanks,

Swathi