1

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?

1
  • 2
    SQL Server 2015 doesn't exist.
    – James Z
    May 6, 2016 at 20:28

4 Answers 4

3

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.

1

Aparently this is practically impossible to do in an "clean" way. I have a open support ticket with Microsoft Premier, and the answer was that the only way is to create a trigger in the logon procces to disconnect users that are attempting to connect to the primary node, but there is no way to redirect that connections to the secondary automatically. So, I will continue with my investigation for a couple of weeks. Thanks to all.

1
  • Juan, How would disconnect a user from the primary node and not the secondary?
    – Ruan
    Aug 16, 2018 at 10:22
0

If you happen to want to do only allow access to the read only secondaries when using AAG's on SQL Server 2019 Linux in Docker Containers on Kubernetes:

https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-kubernetes-connect?view=sql-server-ver15

You can limit access to only the readonly secondaries by only making that K8s Service available. i.e adding to Microsoft's example the appropriate Internal Load Balancer annotation to the write endpoint. For AWS Load Balancers you can also use annotations like:

service.beta.kubernetes.io/aws-load-balancer-extra-security-groups

to enable more granular control of both the read and write services.

There are likely other network configuration options depending on the CNI used.

-1

It's possible but the client won't like it.

  1. Disable automatic failover of the AG.
  2. Keep applications using the listener.
  3. Remove all user logins from the primary.
  4. Setup user logins only on the secondary. Provide the secondary node name directly to the users instead of the listener.
  5. Failover manually when required (if it stays that way then you'll then need to switch all the logins over again and possibly have people start connecting directly to the old primary).

It sounds stupid but some people do use AGs in this scenario because they want the read only replica and don't care about the HA. Problem "solved".

9
  • This will not work. If there is no login on the primary you'll get an 18456 and that's the end of it. May 7, 2016 at 7:01
  • As I mentioned it is still possible to have them login directly to the read-only node skipping the listener :-) Lots of sites do that where they don't have failover enabled and can't add an intent to the connection string. May 7, 2016 at 12:33
  • If you login directly to the read-only node, there are two problems with that. The first is it completely is against what the OP is asking as you'll need a connection string change. The second is that we have NO IDEA if it'll stay a read-only node - thus, once again, incurring a connection string change. May 7, 2016 at 15:43
  • Step one was you turn off automatic failover. Now you know when it will stay a read only node (it may require extra jobs to make sure it stays that way after a reboot of both modes but that's part of having AGs). May 8, 2016 at 2:22
  • This still doesn't enforce what the OP wanted, nor does it address the potential for connection string changes. I'm not arguing, just pointing out the deficiencies. Also in Step 4, there is no "auto redirect" of failed logins per my first comment. May 9, 2016 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.