We have an Always On availability group with a primary and a read-enabled secondary. We have a user for the implementation team who uses the database to check the correctness of the data that they are intending to put in the database.

The users only have rights to read from the database but when they connect (through SSMS) through the AG Listener they always connect to the active node.

I've tried to get them to access the read only instance directly but they're stuck in their ways and after a day or two they're all back on the active node again.

Is there a way for SQL Server to say this user is always going to be with intent readonly and redirect them there?

NOTE: I've tried setting 'ApplicationIntent = ReadOnly' in the additional connection parameters but this doesn't seem to redirect to the secondary node, and isn't the ideal solution as they will inevitably forget to set it up for new starters.

SQL Server 2012 Enterprise, Availability group 1 primary, 1 readable secondary with synchronous commit.

I'm not intending for a user to connect to a linked server, or through any other server. The users connect directly to the database through SSMS (no other application) and I would like the AG Listener (or something around there) to be able to direct that user to a secondary node if one is available (as it only has read access there is no point in it accessing primary) without the user having to do anything, since they move around machines and will forget to add the application intent. Also I find adding that to the additional connection parameters does not always direct you to the secondary node.

  • in addition to the comments below, you need to specify the database in the AG to which you're connecting. – swasheck Oct 1 '18 at 20:59

I don't have an answer to your entire question (though I gave an answer to a similar question today https://dba.stackexchange.com/a/137844/36812) but you mentioned that using ApplicationIntent=ReadOnly doesn't work properly.

Have you set up read only routing URLs? Because it's not done out of the box and if you don't do it then these settings and that flag won't work. I think if you got that working then you could start to re-evaluate your requirements.

Instructions on MSDN https://msdn.microsoft.com/en-us/library/hh710054.aspx and most easily done in PowerShell.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica

Ste, what you are wanting is a point and click solution or a setting somewhere. Unfortunately, none of these currently exist in "off the shelf" form. It would be realllly nice if Microsoft included this in the connection settings for a Registered Server so it could be saved, but alas they do not.

This leaves you with one of two options:

  • Write Something Yourself
  • Change User Behavior

I ran across a Microsoft download for SNAC - SQL Native Access Client, looking for something that might fulfil your requirement(s). This would allow you to write a small bit of code to give users a button to access directly to the Read-Only secondary. https://blogs.msdn.microsoft.com/alwaysonpro/2013/08/02/connect-to-sql-server-using-application-intent-read-only/

The other possibility is to force users to fill in the connection parameters dialogue when connecting through SSMS. To force this behaviour, you'll need to modify logins on the primary and secondary servers, denying connect to the primary and allowing connect to the secondaries. You can use a SQL Agent job to check the server's status and set logins accordingly.

Admittedly, I haven't attempted to do the latter, but in theory it should work.


It looks like this is now a feature in SQL Server 2019.

From Secondary to primary replica read/write connection redirection (Always On Availability Groups) in the official documentation:

SQL Server 2019 preview CTP 2.0 introduces secondary to primary replica read/write connection redirection for Always On Availability Groups. Read/write connection redirection is available on any operating system platform. It allows client application connections to be directed to the primary replica regardless of the target server specified in the connections string.

For example, the connection string can target a secondary replica. Depending on the configuration of the availability group (AG) replica and the settings in the connection string, the connection can be automatically redirected to the primary replica.

  • I'm not sure I can see how this new feature is relevant here. The OP's scenario appears to be different from the one this feature is intended for. The OP wants to redirect read-only connections to a secondary (read-only) node, while the documentation for this new feature says that it's for redirecting read/write connections to the primary node. Could you please elaborate on how the new functionality could be used to solve the problem in question? – Andriy M Nov 19 '18 at 10:06

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.