The company that I work for runs our DMS database in an HA group with Always On. So there is a listener that we connect any applications to.

We also have SSIS on the secondary replica of the HA group, which seems to be pulling data from the primary replica which puts a strain on the application and the end users.

Is there a way to set up a reverse listener where the application or user will be pointed toward the secondary replica instead of the primary replica?


  • If you can upgrade to SQL Server 2016, read-only routing would solve your problem easily. – LowlyDBA Jan 17 '19 at 15:33

You could, but it wouldn't be an out-of-the-box solution. You could create another cluster role ("My Role" for purposes of this discussion) with just a network name and IP address, and use that name to connect to the secondary. You would then need to have an external program constantly monitoring which node owns the AG and which owns My Role, and move My Role to the AG secondary when needed.

You could conceivably control it with a cluster script resource, but the learning curve for doing that would be steeper.

| improve this answer | |

Check out this article on MSSQLTips.com on how to configure readable secondaries and read-only routing in SQL Server 2012 AlwaysOn Availability Groups.

Basically, as long as you're only doing read workload, you enable readable secondaries and set up routing in your AG and any requests with Application Intent=ReadOnly in the connection string will connect to the secondary and perform its read workload there.

If your application needs to do any write workload, it must use a different connection that does not specify read-only intent in the connection string otherwise it will error when trying to perform the writes.

Also, make sure you validate your licensing scenario to make sure your secondary is properly licensed for performing read workloads.

| 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.