The problem

Since the release of SQL Server 2012, Microsoft made good on their promise of easily allowing read only workloads to be routed to the secondary. This is easily achieved by configuring AG routing in such way so that by changing the application’s connection string to Application Intent=Read-only the calls will be routed to the preferred secondaries.

However for many legacy (or not) applications, this is more difficult to do than it looks. It’s rare to find applications that will use different connections strings for different workloads. Usually all the connections the the databases are server by only one connection string.

So in order to make the above work, the applications will need refactoring, more or less serious.

Same situation when trying to offload the main server by using a secondary of a normal transaction replication.

The solution

There are ways of dealing with the above issues, that involve purely database work considering the application calls stored procedures. For non stored procedures calls, again the solution complicates.

First thing that we would need to do is to create a linked server to the targeted secondaries:

• in the case of replication the easiest way is to just target one secondary. Targeting multiple requires a little bit of more coding skills, but it can be extrapolated from the current post
• in the case of availability groups, we would need to make a more “special” kind of linked server. We need to create a linked server to the listener of the same availability group, and we need to mark it with the above Application Intent=Read-only as seen below

@provstr=N'ApplicationIntent=ReadOnly;User ID=myLinkedServerUser;'

Pay attention to the AG configuration over routing.

The second step is to clone the stored procedure and give it a suggestive name “myStoredProcedureRemote“

The third step is to create from the original stored procedure a wrapper for calling the remote one through the linked server. The easiest way to achieve is to call using the 4 part notation using the linked server. However this has some limitations:

• TVPs cannot be used
• There is no way to configure anything on the call. If we want to stop it or route it again to the primary we need to change the code.

I personally prefer to use dynamic sql. We can have many options:

• We can configure routing. There are many options here
  • Create a table for routing
  • Add the source and target stored procedures
  • Add the target server / linked server
• We can recreate the exact call on the secondary server
• We can rebuild the TVPs exactly as it would have been on the primary
• With a simple update on the above routing table we can disable the routing, change the servers, etc

The forth step is .. well there is no forth step. After the above is completed, the traffic should be offloaded to the secondaries.