Sql-server – SQL server AlwaysOn alternate option for readintent=true

availability-groupssql serversql-server-2016

I know applications need certain(newest) version of ODBC drivers to connect an AlwaysOn SQL instance replica and to use the option "applicationIntent= readonly"

Now my question is , if some application don't have have these newest drivers and I(DBA) want to block this application's read only connections to primary and want the readonly requests of this application always routes to a AlwaysOn secondary replica then how this can achieved without "applicationIntent= readonly" option? Does anyone have any handy script to achieve this?

Best Answer

I(DBA) want to block this application's read only connections to primary and want the readonly requests of this application always routes to a AlwaysOn secondary replica then how this can achieved without "applicationIntent= readonly" option?

The simple solution is to configure the application to connect directly to the host hosting the secondary replica. The downside is that if the secondary is offline, the application won't be able to connect.

Alternatively, if your SQL Instances are standalone instances configured to listen on all IPs (the default), you can add an additional Client Access Point (ie a Network Name and an IP Address), to the cluster in a new Application (Cluster Resource Group), and configure the preferred owners of the group, and/or the possible owners of the Client Access Point to control which node the the new Client Access Point run on. The application would be configured to connect to the Network Name, which could fail over to some node if the preferred owner node is offline.

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

There are several steps to configuring a server to accept ReadOnly traffic. The following link walks you through it, http://msdn.microsoft.com/en-us/library/hh710054.aspx ,but basically you need to configure each server in the AG and then set up the routing for each.

Here's the T-SQL involved:

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER01' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER02' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
GO

Sounds like you may be missing the configuration and/or routing information for the primary.

Sql-server – Always on viability/configuration for very specific purpose

The only possible concern in your setup is the Availability Mode and Failover option. If your virtual machines are in the same datacenter or relatively close geographically with a good connection between them you should look into using Synchronous-commit mode and Automatic Failover for the best recovery scenario with no data loss and minimal downtime, especially if it fails during a time it may be difficult to get someone to manually fail it over.

If they are they significantly geographically apart and connectivity isn't reliably 'good' then your setup is fine unless data loss is considered catastrophic. The Asynchronous route is primarily for use when performance is sporadic or far from guaranteed.

While specifying the connection has some value, it may be overkill in such a setting if for some reason your reporting application loses connectivity to the Secondary Replica. There is no reason it couldn't report off the Primary Replica aside from any possible performance concerns, but that may be a valid enough reason to insure the Primary denies the connection.

Best Answer

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

Sql-server – Always on viability/configuration for very specific purpose

Related Question