Availibility Group Listener Name

Question

Post reply

Availibility Group Listener Name

tutupouet

SSCommitted

Points: 1512
More actions
November 14, 2012 at 3:30 am

#270157

Hi,
I want to know if there is a way to get the name of the Availability Group Listener in a query for a connection.
The @@SERVERNAME gives the server name and not the Availability Listener group name used to connect.
I want to know the DNS name used because I want to configure the ressource governor using the name of Availability Group Listener.
Thx

Viewing 8 posts - 1 through 8 (of 8 total)

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

Perry Whittle SSC Guru Points: 234040 More actions · Answer 1

Check the following system catalog for listener info

select * from sys.availability_group_listeners

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"

tutupouet SSCommitted Points: 1512 More actions · Answer 2

Thank you, but this system view displays a list of all listeners groups and not only one in which I made my connection.

Perry Whittle SSC Guru Points: 234040 More actions · Answer 3

yes, so you'll need to filter for your particular group then won't you

Check the link below for more info

http://msdn.microsoft.com/en-us/library/ff878305.aspx

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"

tutupouet SSCommitted Points: 1512 More actions · Answer 4

I have thrown a look at the link, but I cant find my answer.

I give an exemple :

2 instances : mssql1, mssql2

2 DB : db1, db2

2 AG : ag1 with db1, ag2 with db2

2 listeners : ag1_lis, ag2_lis

In SSMS I connect to ag1_lis, with a query I want to display ag1_lis and not mssql1 when mssql1 is the primary replica for ag1 or for both AG !!!

Leo.Miller SSChampion Points: 13051 More actions · Answer 5

Try using this, it works on my demo environment.

select dns_name from sys.availability_group_listeners GL

join sys.availability_group_listener_ip_addresses LIP

on GL.listener_id = LIP.listener_id

join sys.dm_exec_connections C

on LIP.ip_address = c.local_net_address

where session_id = @@SPID

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

tutupouet SSCommitted Points: 1512 More actions · Answer 6

Thank you Leo, this is exactly what I want but now I have a problem to use this in my case.

I want to use the dns_name to configure the reresourceovernor and I can't use this code because it's based on system views and the classifier function for the reresourceovernor must be schemabinding and we can't schemabind on system objects.

How can I resolves my problem ?

tutupouet SSCommitted Points: 1512 More actions · Answer 7

I found my solution for the resource governor

this is an example of my classifier funcfion

create function [dbo].[UDFClassifier]()

returns sysname

with schemabinding

as

begin

declare

@WorkloadGroup sysname,

@local_net_address varchar(48)

select @local_net_address = CAST(ConnectionProperty('local_net_address') as varchar(48))

if @local_net_address = 'XX.XX.XX.XX'

set @WorkloadGroup = 'Grp1'

else

set @WorkloadGroup = 'default'

return @WorkloadGroup

end

thanks a lot

Availibility Group Listener Name

Cookies on SQLServerCentral