MS SQL on AOG Connection Question

[PGN]

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?

[deepaksahirwar]

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 

[PGN]

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

[john_wang]

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.

[PGN]

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.

[john_wang · Accepted solution]

Hello @PGN ,

You may try different values as:

multiSubnetFailover:true;

or

MultiSubnetFailover=Yes;

or

MultiSubnetFailover=True;

Please make sure the last ";" is there. 

Good luck,

John.

[john_wang]

Hello @PGN ,

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

Regards,

John.

[duff4]

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

[PGN · Accepted solution]

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.  

[john_wang]

Hello @PGN ,

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

Best Regards,

John.