MSOLEDBSQL - ApplicationIntent (Qlikview)

[viz301]

We have upgraded the OLEDB SQL driver on the QlikView Publisher. Now the new connections to the data source are connecting with MSOLEDBSQL.1  provider. This is all working fine but Application Intent parameter is not working. The source database is SQL server in Always On configuration. When the QlikView is connecting to the listener DB node it is not redirecting the request to the secondary nodes based on the  parameter ApplicationIntent=ReadOnly. The QlikView version is May 2024. Can anyone help with this. 

[brunoricardo]

Not an expert! However, from chat gpt and some googling:

1 - Modify the Connection String:

• Update your QlikView connection string to include Application Intent=ReadOnly with the appropriate spacing.
• Ensure that the provider is correctly specified as MSOLEDBSQL.1.

2 - Verify SQL Server Configuration:

• Confirm that your Always On Availability Group is configured to route read-intent connections to the secondary replicas.
• Ensure that the secondary replicas are set to allow read-only connections and that the read-only routing list is properly configured.

3 - Test the Connection:

• After making these changes, test the connection to verify that it directs to the secondary replica as intended.
• Monitor the connections to ensure that read-only queries are being routed correctly.

By adjusting the connection string to use Application Intent=ReadOnly and verifying your SQL Server settings, you should be able to direct QlikView's read-only workloads to the appropriate secondary replicas in your Always On configuration.

[diegozecchini]

Hi
try to check that the connection string used in QlikView explicitly contains ApplicationIntent=ReadOnly along with the MultiSubnetFailover=True (if applicable). You can use the conn string:

Provider=MSOLEDBSQL.1;Server=YourListenerName;Database=YourDB;ApplicationIntent=ReadOnly;Integrated Security=SSPI;

then you can test the connection manually using PowerShell or another query tool:

$connection = New-Object System.Data.OleDb.OleDbConnection
$connection.ConnectionString = "Provider=MSOLEDBSQL.1;Server=YourListenerName;Database=YourDB;ApplicationIntent=ReadOnly;Integrated Security=SSPI;"
$connection.Open()
Write-Output "Connection successful"
$connection.Close()

If this works outside of QlikView but not inside QlikView, then the issue is likely with how QlikView handles this parameter.