Sql-server – Always On Availability Group, Always redirect user to read only instance

availability-groupsconfigurationlistenersql serversql-server-2012

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.

Best Answer

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

Related Solutions

Sql-server – SSMS Registered Servers with ReadOnly Application Intent

SQL Management Studio (versions prior to 2016)

Unfortunately, there are some caveats that make using Application Intent in SQL Management Studio somewhat painful:

To connect manually with ReadOnly intent, after bringing up the Connect to Server dialog from the Object Explorer, users must remember to:

Click Options >>.
Go to the Additional Connection Parameters tab.
Enter the additional parameter as ApplicationIntent=ReadOnly;
(Note: Users must not click the Options << button after entering the Additional Connection Parameters or the parameters will be lost.)
Click Connect.
Always launch query windows by right-clicking on the desired database in the Object Explorer view and choosing New Query to avoid running into the #3 caveat below.

The caveats that apply are as follows:

Although you can get SQL Management Studio to connect with Read Only Intent, it does not store the Additional Connection Parameters when a connection is added to Registered Servers.
Behavior when hand editing the locally registered servers in the RegSrvr.xml file to add the Application Intent is extremely inconsistent and will be overwritten any time a change is made through the GUI making this workaround unreliable.
The Always On database must be selected before the query window is opened; otherwise, the connection gets routed to the primary server. If you attempt to select the database using the query window's drop down after the query window has already been opened to a non-Always On database, you will get an error dialog. If you try to change the database to an Always On database with a USE statement after the query window has already been opened to a non-Always On database, the results look like this when you attempt to execute the SQL query:

      Msg 979, Level 14, State 1, Line 1
      The target database ('AlwaysOnDatabase') is in an availability group 
      and currently does not allow read only connections. For more 
      information about application intent, see SQL Server Books Online.

SQL Management Studio (versions 2016 or later)

SQL Server Management Studio 2016 or higher can connect with Read Only Application Intent (using the same 6 steps as prior versions) and it does store the Additional Connection Parameters. There are still some caveats:

The Object Explorer view will not enumerate any of the tables or other objects in the AlwaysOn databases. Attempting to expand them results in an access denied message.
You cannot have a Read Only and a non-Read Only connection to the same listener open at the same time.
Intellisense for object names in the database does not work. (Oddly enough, Objects get enumerated just fine in the Query Designer that you can launch using Design Query in Editor... from the right-click menu.)
Caveat 3 from the prior versions' caveats still applies.

Third-Party Products

LinqPad stores the whole connection string including Application Intent and the database when you save a connection and therefore might be a viable option for performing Read Only queries against Always On databases.

Sql-server – AlwaysON – Force users connection to use Readable Secondary Nodes

There is no way to complete this with just SQL Server and the interfaces it exposes. In order to do this you'll either need an appliance between the clients and SQL Server (the users connect to the appliance endpoint and the appliance does the redirection) or you can implement your own through a cname and some type of configurable load balancer like an F5.

There is nothing in 2016 to change this behavior AFAIK.

Best Answer

Related Solutions

Sql-server – SSMS Registered Servers with ReadOnly Application Intent

Sql-server – AlwaysON – Force users connection to use Readable Secondary Nodes

Related Question