SQL Server Always On Availability Group (AG) Listener: step by step guide


Availability databases hosted on SQL Server Always On Availability Group (AG) can be connected using a unique Virtual Network Name (VNN) called Availability Group Listener. When Availability Group is enabled, clients can connect to databases in both primary and secondary replicas without explicitly specifying the SQL Server instance name. You don’t even need to know the instance name to connect to Availability Group (AG).

When you have configured read-only routing for secondary replicas and the application or client connection contains the application intent as read-only, the listener will redirect the connection to secondary readable replicas, Otherwise, the listener redirects both read-write and read-only intent connections to primary replica. If there is a fail-over, the listener will redirect connections to new primary and secondary connections based on the application intent.
Availability group listener consist of following objects:
  • Domain Name Systems (DNS)
  • Listener Port
  • One or more IP addresses (VIP)
The Listener is always owned by the SQL Server instance where the primary replica resides. At the time of failover, the new primary replica will own the listener.

Example:
  • DNS: salesag.mscorp.com
  • Port: 1433
  • IP: static or DHCP

Creating Availability Group Listener:

Availability Group Listener can be created while creating Availability Group or it can be created post creating the AG. The Listener creation options are available on Specify Replicas window and under the Listener tab.

If you want to skip the Listener creation while creating Availability Group, you can leave the default option Do not create an availability group listener now check the below image for reference:

New Availability Group - Specify Replicas
New Availability Group - Specify Replicas


Step by step guide to add SSISDB to SQL Server 2016 Always On Availability Group (AG)

Starting from SQL Server 2016, you can add SSISDB as Availability Database in Always On Availability Group (AG). This enables high availability and automated failover for SSISDB. This article provides step by step guide to add SSISDB to Availability Group.

Make sure, you have installed SQL Server Integration Services on both primary and secondary replicas.

Step 1: Create SSIDB on primary replica:

Right-click on the Integration Services Catalogs and click on Create Catalog Wizard option

Creating Integration Service (SSIS) Catalog
Creating Integration Service (SSIS) Catalog