May 28, 2020 at 3:43 pm
Due to the hurricane season around we have a requirement to have all the applications point out to using the AG listener name. However, we would like to identify if there are any connections that are coming through and hitting the actual SQL instance name instead of using the AG listener name...this would help us to find the one's that haven't switched over and fix them. I am not sure if Profiler trace or Extended events would help...can we trace this or is there possibly a better way to identify what we are looking for.
May 28, 2020 at 5:16 pm
Yes there's a way, you can use the sys.dm_exec_connections DMV to see the local_net_address, if the IP in there is the IP of your instance and not the listener, then that session is connecting directly.
Lucky, i thought my AG testing servers were deleted here, but i see they are up and running and could quick check this. lol
May 28, 2020 at 5:41 pm
Awesome but is there a way to instead get the listener name rather than the IP address or maybe convert that to give me the name instead of the IP.
May 28, 2020 at 6:22 pm
I don't think you can do it natively but using xp_cmdshell and writing some script using the IP Address to get the PC information from the network, else i would just get the list of servers and their IP address and use the case expression to get what i need.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply