• Adam Thurgar

Availability group multiple listeners

In some circumstances you may need to create multiple listeners for an availability group. It may be that you require each application to use its own listener, separate from other applications. This separation may lessen the impact of taking downtime. Or it could be that legacy applications have connection strings that cannot be easily changed and you want to create a listener for those connection strings and then you don't have to make any client changes.

Configuring multiple listeners is a manual process.

The steps to configure multiple listeners are:

1. Create AlwaysOn Availability Group, but skip the listener creation in the wizard or T-SQL

2. Go to cluster manager, create “client access point”, you can create multiple access points and assign IP's for each of them. It is recommend you use “OR” dependencies for IP's in each listener.

3. Make sure all listeners create are online and associate them with your availability group resource – make sure you use “OR” in dependencies – this means as long as one listener is online your availability group is considered online and available to your clients.

4. if you query sys.availability_group_listeners – you should be able to see all of them and that all the ports are “NULL”.

5. Now, you still cannot use these listeners. You need to go back to run T-SQL to assign port for each of them (you can assign different ports to each of them.

Example T-SQL syntax:

alter availability group [ag1] modify listener 'ag1_listener1' (port = 61435)

6. Finally query sys.availability_group_listeners again, you should be able to see the listeners and the ports that SQL Server is listening to them.

9 views0 comments

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t