Configure Database Connection Redirection to Enable Read-Only Queries

V9.1

Overview

Starting with Cora SeQuence V8.8, you can configure the ApplicationIntent property to enable read-only database queries.

In a high-availability SQL solution, if you don’t specify the ApplicationIntent property, the default mode is ReadWrite. When you set up the ApplicationIntent property for a data provider, the ApplicationIntent property is added to the connection string used to connect to the database, and enables read-only connection redirection for Always On availability groups. This feature is useful to improve system performance, and can be configured for any of the Cora SeQuence  providers, such as portal view providers or providers used to retrieve read-only data.

In Cora SeQuence, the ApplicationIntent property is named availabilityGroupRouting.

Configuration

To enable read-only database queries, you first set up a read-only connection string property and then set the availabilityGroupRouting property to ReadOnly. 

Prerequisites

Before you configure the connection redirection, make sure that:

• Your SQL database setup supports connection redirection for Always On availability groups.
  For more information, see this article.
• You have the SQL listener IP.

Enable connection redirection

1. Open the Flowtime web.config file.
2. Under the persistence>databasesection, add a new property named readonlyConnectionString.
3. In the readonlyConnectionStringproperty, change the data sourcevalue to the SQL listener IP.
4. Under the Persistencesection, locate the relevant provider.
5. Add the availabilityGroupRouting property at the end of the provider tag, and set it to ReadOnly.
6. Save the file.

Sample

<persistence>
      <database provider="System.Data.SqlClient" credentials="XXX" connectionString="MultipleActiveResultSets=true;initial catalog=DBName;persist security info=True;data source=DBserverName;packet size=4096;" commandTimeout="1200" readonlyConnectionString="MultipleActiveResultSets=true;initial catalog=DBName;persist security info=True;data source=SQLListenerIP;packet size=4096;" />
<providers>
     <add type="PNMsoft.Sequence.Data.Sql.SqlAuthenticationDataProvider, PNMsoft.Sequence.Data, Version=8.0.0.0, Culture=neutral, PublicKeyToken=0a1a1b90c1c5dca1"availabilityGroupRouting="ReadOnly
" />

Disable the read-only query configuration

If required, you can disable the read-only connection redirection.

1. To disable the redirection configuration, do one of the following:
   • Delete the availabilityGroupRouting property.
   • Set the availabilityGroupRouting to ReadWrite.

V8.8

Overview

Starting with Cora SeQuence V8.8, you can configure the ApplicationIntent property to enable read-only database queries.

In a high-availability SQL solution, if you don’t specify the ApplicationIntent property, the default mode is ReadWrite. When you set up the ApplicationIntent property for a data provider, the ApplicationIntent property is added to the connection string used to connect to the database, and enables read-only connection redirection for Always On availability groups. This feature is useful to improve system performance, and can be configured for any of the Cora SeQuence  providers, such as portal view providers or providers used to retrieve read-only data.

Configuration

In Cora SeQuence, the ApplicationIntent property is named availabilityGroupRouting. When you add the availabilityGroupRouting property to a data provider, the resulting connection string for that provider is set to ApplicationIntent={Value}

Prerequisites

Make sure that your SQL database setup supports connection redirection for Always On availability groups.
For more information, see this article.

Enable connection redirection

1. Open the Flowtime web.config file.
2. Under the database providersection, change the data sourcevalue to the SQL listener IP.
3. Under the Persistencesection, locate the relevant provider.
4. Add the availabilityGroupRouting property at the end of the provider tag, and set it to ReadOnly.
5. Save the file.

Sample

<add type="PNMsoft.Sequence.Data.Sql.SqlAuthenticationDataProvider, PNMsoft.Sequence.Data, Version=8.0.0.0, Culture=neutral, PublicKeyToken=0a1a1b90c1c5dca1"availabilityGroupRouting="ReadOnly" />

Disable the read-only query configuration

If required, you can disable the read-only connection redirection.

1. To disable the redirection configuration, do one of the following:
2. Do one of the following:
   • Delete the availabilityGroupRouting property.
   • Set the availabilityGroupRouting to ReadWrite.