Adding another listener into an existing AG

Question

Adding another listener into an existing AG

LearningDBA

SSCertifiable

Points: 6609
More actions
October 2, 2019 at 6:43 pm

#3685431

I needed to create a listener on one of the AG (This AG already has a listener). We have 3 node cluster. SQL1, SQL2,SQl3. SQL1 is the primary and 2 and 3 are secondary. However, when I created a listener on SQl1 using a WSFC manager, it is in a stopped state and the owner node shows 'SQL3' even though I created this on SQL1. How to get it to work? What additional steps I have to take? Will I need to restart SQL Services? It is a dev environment.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

After adding the listener in cluster manager, you need to execute: alter availability group...alter listener... to add the port for the listener. You can check if it has a port assigned by querying sys.availability_group_listeners

Sue

LearningDBA SSCertifiable Points: 6609 More actions · Answer 2

sys.availability_group_listeners only gave me 1 record (details of an existing listener).

Tried running an alter AG statement with modify listener with port.

ALTER AVAILABILITY GROUP [AG]

modify LISTENER N'Test' (PORT=1433);

Got this error: "The specified listener with DNS name, 'Test', does not exist for the Availability Group. Use an existing listener, or create a new listener."

Then tried running this command

ALTER AVAILABILITY GROUP [AG]

add LISTENER N'Test' ( WITH IP ( ('IP Address''),('SubNet Mask') ) , PORT=1433);

Got this error: the format for the ip address is invalid. please use a valid value for the ip address

Which value I should be using here? I got the IP address using the IPConfig.

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

LearningDBA SSCertifiable Points: 6609 More actions · Answer 3

This issue has been resolved. I was trying to create a client access point from cluster level and that's why it was just giving me all sorts of issues. I created a listener from the AG, assigned a port and it worked.

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]