I have an Always On Availability Group with a read-only secondary set up. We're running SQL Server 2016 Enterprise Edition SP1 CU4. Users are supposed to only connect to the secondary using the ReadOnly ApplicationIntent when connecting to the listener. I don't want to remove their logins from the primary since this would prohibit access after a failover. I am looking at perhaps running a SQL Agent job that checks connections and kills sessions if they are running on the primary but it seems like there should be a better way to do that than running a job every minute or so! Do I need to look at creating an audit or is there some other mechanism to accomplish this?

  • 1
    Logon trigger is what coming to my mind, there may be other better way – Shanky Oct 24 '17 at 17:12
  • I'd agree with the easiest way being a logon trigger. It can check the status of one of the databases in your AG, and if it is the primary prevent logon for that user. – Nic Oct 24 '17 at 17:29
  • Note that if a logon trigger starts failing for some reason, then nobody gets logged in except the dedicated admin connection. So catch any errors and handle with care. – Tony Hinkle Oct 25 '17 at 1:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.