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

availability-groupslistenersql server

I'm implementing SQL Server 2015 Enterprise on my actual client, using Always On Availability Groups. Processes like Reporting, ETL (extraction of data), and read workload are being redirected to the secondary replica using the Listener and the "initial catalog" and "applicationintent=readonly" parameters.

The problem is: My client wants to force all non-admin users to connect always to the secondary-readable replica. BUT! make this automatically. The client does not want that the users have to add parameters to the connection in SQL Server Management Studio.

I'm testing logon trigger with no results. I can't find a way out to this.

Is this possible?

Best Answer

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.

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

There are several steps to configuring a server to accept ReadOnly traffic. The following link walks you through it, http://msdn.microsoft.com/en-us/library/hh710054.aspx ,but basically you need to configure each server in the AG and then set up the routing for each.

Here's the T-SQL involved:

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER01' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER02' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
GO

Sounds like you may be missing the configuration and/or routing information for the primary.

SQL Server – How to Query Readonly Replica in Always On Cluster

After some help and hints (especially thanks to Andriy M) I've managed to understand what I actually want and how to do it. This article from Microsoft helped as well: https://technet.microsoft.com/en-us/library/hh403414.aspx

First I had to create a read only listener. And then at the other server add linked server like this (borrowed from: https://stackoverflow.com/a/19585304):

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server     = N'cluster-readonly', -- just a name
    @srvproduct = N'SQL', -- anything except 'SQL Server'
    @provider   = N'SQLNCLI11',
    @datasrc    = N'ClusterListener', -- name of the readonly listener
    @provstr    = N'ApplicationIntent=ReadOnly',
    @catalog    = N'AnyActualDataBase' -- system database like 'master' is no good
GO

How do I check, where am I:

SELECT * FROM OPENQUERY([cluster-readonly], 'SELECT @@SERVERNAME')

Here's some trick - if I don't specify @catalog, then connection will be to the PRIMARY node for some reasons.

Best Answer

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

SQL Server – How to Query Readonly Replica in Always On Cluster

Related Question