topic Re: MS SQL on AOG Connection Question in Qlik Replicate

MS SQL on AOG Connection Question

PGN — Thu, 25 Jan 2024 20:32:24 GMT

We're beginning to use some target and source MS SQL servers using AOG servers on AWS. The AWS configuration is using two different subnets for the AOG listener. AWS is advising to use the MultiSubnetFailover=True in our connection strings. How do I incorporate this in Qlik Connection manager?

Re: MS SQL on AOG Connection Question

deepaksahirwar — Fri, 26 Jan 2024 12:59:19 GMT

Dear @PGN ,

You can implement a fail-over data connection in Qlik Connection manager by following these steps:

Create two connections to each database.

Set system variable ErrorMode = 0 so that the script execution does not halt when connection error occurs

Attempt the first connection to the primary database

Check system variable ScriptError to see if there is an error with the first connection

If there is, use the second connection to the fail-over database

Reset variable ErrorMode if needed

You can also use the MultiSubnetFailover parameter in your connection string to enable faster failover for all Always On Availability Groups and Failover Cluster Instances in SQL Server. This parameter will allow the client to attempt connections in parallel to different subnets.

I hope this helps you.😊

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer

Best Regards,

Deepak

Re: MS SQL on AOG Connection Question

PGN — Fri, 26 Jan 2024 13:12:22 GMT

I was going to attempt to use the MultiSubnetFailover parameter, but I don't know where to update the connection manager to add it.

Re: MS SQL on AOG Connection Question

john_wang — Fri, 26 Jan 2024 13:41:56 GMT

Hello @PGN ,

You may go to the endpoint, Advanced tab, and add an internal parameter, name is:

additionalConnectionProperties

Assign its value to "MultiSubnetFailover=True".

Hope it helps.

John.

Re: MS SQL on AOG Connection Question

PGN — Fri, 26 Jan 2024 14:29:49 GMT

Hi @john_wang. That's the information I was looking for. However, I get the following response:

SYS-E-HTTPFAIL, Cannot connect to SQL Server.
SYS,GENERAL_EXCEPTION,Cannot connect to SQL Server,RetCode: SQL_ERROR SqlState: 08001 NativeError: 0 Message: [Microsoft][ODBC Driver 18 for SQL Server]Invalid value specified for connection string attribute 'MultiSubnetFailover'

I tried True and Yes. Both respond with the same error.

Re: MS SQL on AOG Connection Question

john_wang — Fri, 26 Jan 2024 14:57:14 GMT

Hello @PGN ,

You may try different values as:

multiSubnetFailover:true;

MultiSubnetFailover=Yes;

MultiSubnetFailover=True;

Please make sure the last ";" is there.

Good luck,

John.

Re: MS SQL on AOG Connection Question

john_wang — Sat, 27 Jan 2024 01:40:48 GMT

Hello @PGN ,

Thanks for the response. Would you please share which parameter helped finally? It's helpful for all of us.

Regards,

John.

Re: MS SQL on AOG Connection Question

duff4 — Sat, 27 Jan 2024 07:11:23 GMT

Connect from what? If you install the sql management tools and a sql instance on your pc you just select the local instance. If sql is on a different pc you will connect with ip or dns name. The only thing you need to figure out at that time is account

Re: MS SQL on AOG Connection Question

PGN — Sat, 27 Jan 2024 20:08:27 GMT

Hi. Sorry, I thought I did respond somewhere. The final parameter value I used was

MultiSubnetFailover=Yes;

I was missing the semicolon. Just an FYI, for this version of the driver, "True;" did not work

Thanks so much for your assistance.

Re: MS SQL on AOG Connection Question

john_wang — Sun, 28 Jan 2024 09:15:07 GMT

Hello @PGN ,

Thank you so much for the confirmation. I really appreciate it!

Best Regards,

John.

Re: MS SQL on AOG Connection Question

SushilKumar — Mon, 29 Jan 2024 10:28:48 GMT

Hello team,

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Regards,

Sushil Kumar