Find out Client Access with servername or sql-listener

Question

Find out Client Access with servername or sql-listener

ingmar_n

SSC Enthusiast

Points: 174
More actions
December 22, 2022 at 12:47 pm

#4128863

Hi,
we have an sql-2019-Always-On-Cluster.Normally the clients access via listener, e.g. sqlli9.
To test a failover, we have to know, how all clients access to the sql-server.
Of course most will use the listener-name, but if there are some clients woho access with the sql-servername, this won't work after a failover, because the servername will be another then.
Is there a command to show, how the clients take access?
In sp_wo or sp_woh2 only the hostname of the client is displayed...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

There is no command or check for this I'm afraid, you will just have to hope that your application teams have configured all of the connection strings to point to the listener, then failover and see what breaks.

As you have a list of the hostnames already connected to the current primary replica, it would be a case of logging into each machine and checking the application web.config or other configuration files to ensure they use the listener or hostname and change what needs changing accordingly.

ingmar_n SSC Enthusiast Points: 174 More actions · Answer 2

Thanks,

I tried it with netstat, but I can't see there the listener, too.

Somewhere this information must be - maybe I'll try it with wireshark...

ingmar_n SSC Enthusiast Points: 174 More actions · Answer 3

It sholud work with netstat:

netstat server-ip --> shows all connection to the server with server-ip

netstat listener-ip --> shows all connections to the server with listener-ip

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

You might try looking at the connectivity_error_ring_buffer_recorded Extended Event. It's one mechanism to track timeouts on connections. I think it might, possibly, maybe (did I put enough weasel words in?) provide the information you want. I have a blog post on using it for timeouts here.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 5

I saw this article on connectivity ring buffer error. Please see below

https://www.sqlskills.com/blogs/erin/the-security_error_ring_buffer_recorded-event-and-why-you-dont-need-it/

Here is a possible way to record the SQL logins into a custom table that may be tracked/reviewed.

DBASupport