KB486865: MicroStrategy SQL Server ODBC driver configuration parameters for SQL Server Always On Availability Groups

Description

The SQL Server Always On Availability Groups feature introduced in SQL Server 2012 (11.x) is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Specific connection configuration parameters must be set to connect to a SQL Server Always On Availability Group, using the Strategy SQL Server ODBC driver for optimal performance and reliability.
 

Configuration Parameters

Hostname

Purpose
The name, IP address, or alias of the server or instance to which you want to connect.
 
Valid Values

virtual_network_name

 
where:
virtual_network_name is the virtual network name (VNN) of the availability group listener when using an Always On Availability Group.
 

MultiSubnetFailover

Purpose
Determines whether the driver attempts parallel connections to the failover IP addresses of an Availability Group during a multi-subnet failover. When Multi-Subnet Failover is enabled, the driver simultaneously attempts to connect to all IP addresses associated with the Availability Group listener when the connection is broken or the listener IP address becomes unavailable. The first IP address to successfully respond to the request is used for the connection. Using parallel-connection attempts offers improved response time over traditional failover, which attempts to connect to alternate servers one at a time.
 
Valid Values
1
 
Behavior
If set to

1

(Enabled), the driver attempts parallel connections to all failover IP addresses in an Availability Group when the connection is broken or the listener IP address is unavailable. The first IP address to successfully respond to the request is used for the connection. This setting is only supported when your environment is configured for Always On Availability Groups.
 
If set to

0

(Disabled), the driver uses the failover method specified by the Failover Mode connection option when the primary server is unavailable. Use this setting if your environment is not configured for Always On Availability Groups.
 
Notes

• When

  MultiSubnetFailover

  is enabled, the virtual network name (VNN) of the availability group listener must be specified by the

  HostName

  connection option.
• When

  MultiSubnetFailover

  is enabled, the

  AlternateServers

  ,

  LoadBalancing

  , and

  FailoverPreconnect

  connection options are disabled.
   

ApplicationIntent

Purpose
Specifies whether the driver connects to read-write databases or requests read-only routing to connect to read-only database replicas. Read-only routing only applies to connections in Microsoft SQL Server 2012 where Always On Availability Groups have been deployed.
 
Valid Values

0

or

1

 
Behavior
If set to

0

(READWRITE), the driver connects to a read-write node in the Always On environment.
 
If set to

1

(READONLY), the driver requests read-only routing and connects to the read-only database replicas specified by the server.
 
Notes

• By setting

  ApplicationIntent

  to

  1

  (READONLY) and querying read-only database replicas when possible, you can improve efficiency of your environment by reducing the work load on read-write nodes.
• When

  ApplicationIntent

  is enabled, the virtual network name (VNN) of the availability group listener must be specified in the Host Name connection option.

Default
0 (READWRITE)
 

Driver Version

8.0.2