We have an Always On Availability Group with a primary and 2 secondary SQL Servers. We routinely fail over to do maintenance. This changes which server is primary/secondary. When we set up reports to be emailed we "hard-code" which server info is first, second, etc on the report. I would like it so that we could always have the primary server's report charts at the top of each email. Has anyone figured out a way to do this?
I thought possibly using a DNS entry which points to wlhichever server is primary (and we would have to remember to switch it every time we fail over). But I don't know how to get DPA to allow me to refer to the DNS entry rather than the actual server name.
Thanks for any info you have