1

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?

Improve this question
2
  • 1
    I don't think it could be done at "our" level... this needs to be done before SQL accept the session and before it does, there nothing you can do. Once the session is made on your primary node, you cannot redirect it to the secondary node (none that I'm aware) – Dominique Boucher Aug 20 '20 at 18:44
  • 1
    You could mess with DNS/routing so they think they're going to primary, I suppose, but it is probably less hassle tbh to just make them upgrade their stuff. – Aaron Bertrand Aug 20 '20 at 19:00
0

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.

Improve this answer
0

As you've already found out, ApplicationIntent=READONLY; isn't a standardized keyword when it comes to connection strings. Most drivers don't support it and generally, you'd either connect to a read-only replica node explicitly or just point to the primary node and resign yourself to not using a read-only replica. There is another option though, and once in place, it will provide client connections with a read-only location that points to a node in the AG of your choosing without requiring any special connection keywords.

Create a Read-Only Listener (not an official term)

One critical component of an Availability Group is that any listener you associate with said Availability Group must connect to the primary node. The great thing though is that regardless of edition, you can have numerous Availability Groups per cluster. A Read-Only Listener is, quite simply, a new, empty Availability Group with a new Listener Name, pointed at your read-only replica.

Make sure you reserve a new IP address before you start, but with a new IP address in-hand, create a new Availability Group as you normally would using a new, blank database (which I drop after the creation of said AG). Create your read-only listener using the new IP address and failover this new listener to your read-only replica.

When this is setup, and you connect to the primary node of your user databases, you will see a secondary Availability Group listed as (Secondary):

enter image description here

What the image above shows is our AG that hosts user databases is named SQL-P02 (which is the server SSMS is actively connected to in the screenshot). This is the connection I use when I want to connect to my primary node and perform read/write operations. The second AG SQL-P02-RO is the new AG that I setup. There are no databases associated with this AG, but I did create a new Listener and when SQL-P02-RO's primary replica is the same as the read-only replica in the SQL-P02 AG, it functionally becomes a Read-Only Listener. So basically, for any client that I want to point at the read-only replica, I specify SQL-P02-RO as my server rather than having to find a driver that uses the ApplicationIntent keyword.

Yes, this is a bit clunky, but the benefit here is that you can point any connections you wish to deem "read-only" to any replica in your AG. Most of the time you would choose your read-only replica as the target, but in the event of patching, a failover, etc. this could just as easily be pointed to your primary so you can minimize downtime for your read-only connections as well.

I use this approach across a number of servers currently with much fanfare. For whatever reason, our company is littered with apps that use the ancient jTDS drivers which most definitely do NOT support the ApplicationIntent keyword, but we needed to specify read-only connections and manually adjusting connection strings every time a failover occurred was just not a feasible approach.

If you have questions about this, leave a comment and I'll do my best to answer, but hopefully, this helps demonstrate you can specify read-only connections for an AG, albeit in a way that's not exactly as straightforward as using the ApplicationIntent keyword in a connection string.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.