Notes on SQL

Random articles from a puzzled DBA

Overview

For a future article, I require a Listener for the Availability Group (AG) that was created from this article.

This post describes the simple steps required to create a Listener.

What is an Availability Group Listener?

Basically, an availability group listener is a virtual network name that you can use to access a server within an AG, without knowing the name of the actual SQL Server instance. Since the listener routes traffic, the client connection string does not need to be modified after a failover occurs. After a failover, the Listener will always point traffic to the correct instance.

Test Environment

I have a two-node AG, named ‘Azure’ (for reasons that will become clear in a following article), that does not have a Listener. For future work a Listener is required.

Basic Example

The two-node AG shown below, does not have a listener. This can be checked simply by expanding the ‘Always On Availability Groups’ node within SQL Server Management Studio (SSMS) and drilling down to the ‘Availability Group Listeners’ node.

Figure 1: Checking for a Listener via SSMS

Using SSMS to show that there is no Listener for this AG
SSMS Details for the AG

Creating the Listener

Creation is quite a simple process, with a couple of things to watch out for.

The Listener name and IP addresses must be unique. If you have more than one AG it cannot be the same name or have any of the IP addresses as any other Listener on any other AGs on that instance.

Right-click on the ‘Availability Group Listeners’ node within SSMS and select ‘Add Listener…’.

In the resultant dialog I have given the Listener name as ‘AzureLsnr’, Port 1433 and a network mode of ‘Static Ip’. Selecting ‘Static IP’ produces another dialog to add IP addresses. In my setup the IP address of 192.168.0.63 will work

Figure 2: Creating the Listener

Creating the Listener via the Wizard

Click on ‘OK’ and then ‘OK’ again to complete the creation.

The Listener is now listed within the AG details of SSMS.

Figure 3: Showing the Listener

Showing the Listener

Testing the Listener

Disconnect from the instance and reconnect, but specifying the name of the Listener, not the instance that you want to connect to.

In this case it is connected to ‘AG2-Node1’, as that is the primary node at this point in time.

Figure 4: Connected to Node1, Via the Listener

Connected to Node1 via the Listener

Now, I failover the AG to Node2. My connection via SSMS has not changed but I am now connected to the new Primary server – Node2.

Figure 5: Connected to Node2, after Failover

Connected to Node after Failover

Conclusion

Life for those that need to connect to your AG can be made considerably easier with the use of a Listener. This was a very basic example of the creation and use – more information can be found by following the links below.

References

What is an Availability Group Listener?

Configure a Listener for an Always On Availability Group

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: