Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
PGN
Creator II

MS SQL on AOG Connection Question

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?

Labels (2)
2 Solutions

Accepted Solutions
john_wang
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

PGN
Creator II
Author

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.  

View solution in original post

10 Replies
deepaksahirwar
Creator II

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
Creator II
Author

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
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
PGN
Creator II
Author

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
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
john_wang
Support

Hello @PGN ,

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

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
duff4
Contributor

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
Creator II
Author

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
Support

Hello @PGN ,

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

Best Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!