I know that we can use ApplicationIntent=ReadOnly to connect to the secondary read-only nodes in an AG setup. Is it possible in some way to tell the DNS server which node is which on failover so we can just point an alias to the read-only node and not have to use a different connection string? I realize this might take some handling in events or something similar, but it seems like this would be useful to give people an alias to use for reads.

I realize that it wouldn't be correct 100% of the time, especially right after a failover, but wondering if this is possible in some way.

  • 3
    The connection string attribute isn't just to facilitate seamless redirect to the secondary, it also ensures no write operations are attempted, allows you to reject read-only queries (say, during maintenance), and in 2016 will allow you to have load balancing of read-only workloads. What exactly is your goal in not having to change the connection string? I think you lose a lot of the benefits if you hack it through DNS. – Aaron Bertrand Oct 8 '15 at 13:13
  • Mostly just an easier way to point people to the read-only instance. We only have the 2 nodes right now and would use the read-only instance for researching issues, running ad-hoc queries, and such. I was mostly wondering if there were some easier way to just say "connect to readonly.sqlserver.local" for those cases. The DNS alias would be a convenient way to do that. For apps, we'd use the proper connection string - this would be more for ad-hoc connections/queries. – Peter Schott Oct 8 '15 at 16:22
  • 1
    Well you could use DNS to point clients at the listener, but I would still use the ApplicationIntent attribute in those connection strings. – Aaron Bertrand Oct 8 '15 at 16:36
  • I agree with @AaronBertrand you lose benefits hacking it with DNS defeating the purpose of why it's setup in the first place. Also, there's not really much of a difference in typing in another DNS SQL Server instance via SSMS for the ad hoc query writers than them going over a couple tabs and adding the ApplicationIntent = ReadOnly. In either case, someone has to make a conscious decision to do it one way or another, so I'd go with using it as it was designed to be used. – Pimp Juice IT Oct 9 '15 at 10:32
  • Thanks. It was a curiosity - wondering if it were possible to somehow keep that DNS alias updated w/ the current secondary node. I'll chat w/ our DB team about setting up a registered server w/ the proper settings for read-only for when we need that. – Peter Schott Oct 9 '15 at 15:19

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.