Reducing Loss of Connectivity when Recreating an Availability Group

  • Article

When you drop an availability group, the listener resource is also dropped and interrupts application connectivity to the availability databases.

To minimize application downtime, use one of the following methods to sustain application connectivity through the listener, and drop the availability group.

Method 1 Associate listener with new availability group (role) in Failover Cluster Manager

This method enables you to maintain the listener while dropping and recreating the availability group.

1. On the SQL Server that the existing availability group listener is directing connections to, create a new, empty availability group. To simplify, use the transact-sql command to create an availability group with no secondary replica or database:

use master
go
create availability group ag
for replica on 'sqlnode1' with
(endpoint_url = 'tcp://sqlnode1:5022', availability_mode=asynchronous_commit, failover_mode=manual) 
 

2. Launch Failover Cluster Manager and click Roles in the left pane. In the pane listing the Roles select the original availability group.

3. In the bottom-middle pane, under the Resources tab right-click the availability group resource and choose Properties. Click the Dependencies tab and delete the dependency to the listener. Click OK.

 

 

4. In the bottom-middle pane, under the Resources tab, right click the listener and choose More Actions and then Assign to Another Role. In the dialog box, choose the new availability group and click OK.

 

 

5. In the Roles pane select the new availability group. In the bottom-middle pane, under the Resources tab, you should now see the new availability group and the listener resource. Right-click the new availability group resource and choose Properties. Click the Dependencies tab and select the listener resource from the drop-down box. Click OK.

 

     

6. In SQL Server Management Studio, use Object Explorer to connect to the SQL Server instance hosting the primary replica of the new availability group. Drill into AlwaysOn High Availability, then drill into the new availability group and then Availability Group Listeners. You should find the listener.

7. Right-click the listener and choose Properties. Enter the appropriate port number for the listener and click OK.

 


  

This will ensure that applications using the listener can still use it to connect to SQL Server hosting the production databases without interruption. The original availability group can now be completely removed and recreated or the databases and replicas can be added to the new availability group.

IMPORTANT: If recreating the original availability group, the following steps will re-assign the listener back to the recreated availability group role: assign the listener back to the recreated availability group role, set the dependency between the recreated availability group resource and the listener, and re-assign the port to the listener:

1. Launch Failover Cluster Manager and click Roles in the left pane. In the pane listing the Roles select the new availability group hosting the listener.

2.  In the bottom-middle pane, under the Resources tab right-click the new availability group resource, which currently has a dependency on the listener and choose Properties. Click the Dependencies tab and delete the dependency to the listener. Click OK.

3. In the bottom middle pane, under the Resources tab, right click the listener and choose More Actions and then Assign to Another Role. In the dialog box, choose the recreated availability group and click OK.

4. In the Roles pane select the recreated availability group. In the bottom middle pane, under the Resources tab, you should now see the recreated availability group and the listener resource. Right-click the recreated availability group resource and choose Properties. Click the Dependencies tab and select the listener resource from the drop-down box. Click OK.

4. In SQL Server Management Studio, use Object Explorer to connect to the SQL Server instance hosting the primary replica of the recreated availability group. Drill into AlwaysOn High Availability, then drill into the recreated availability group and then Availability Group Listeners. You should find the listener.

5. Right-click the listener and choose Properties. Enter the appropriate port number for the listener and click OK.

Method 2 Associate listener with existing SQL Failover Clustered Instance (SQLFCI)

If you are hosting your availability group on a SQL Server Failover Clustered Instance (SQLFCI) you can associate the listener clustered resource with the SQLFCI clustered resource group, while dropping and recreating the availability group.

1. Launch Failover Cluster Manager and click Roles in the left pane. In the pane listing the Roles select the original availability group.

2. In the bottom middle pane, under the Resources tab, right-click the availability group resource and choose Properties. Click the Dependencies tab and delete the dependency to the listener. Click OK.

3. In the bottom middle pane, under the Resources tab, right click the listener and choose More Actions and then Assign to Another Role. In the dialog box, choose the SQL Server FCI instance and click OK.

  

4. In the Roles pane select the SQL Server Failover Clustered Instance (SQLFCI) group. In the bottom middle pane, under the Resources tab, you should now see the new listener resource.

This will ensure that applications using the listener can still use it to connect to SQL Server hosting the production databases without interruption. The original availability group can now be completely removed and recreated.

IMPORTANT: Once the availability group is recreated, re-assign the listener back to the availability group role, set up the dependency between the new availability group resource and the listener and re-assign the port to the listener:

1. Launch Failover Cluster Manager and click Roles in the left pane. In the pane listing the Roles select the original SQL Failover Clustered Instance role.

2. In the bottom middle pane, under the Resources tab, right click the listener and choose More Actions and then Assign to Another Role. In the dialog box, choose the recreated availability group and click OK.

3. In the Roles pane select the recreated availability group. Under the Resources tab, you should now see the recreated availability group and the listener resource. Right-click the recreated availability group resource and choose Properties. Click the Dependencies tab and select the listener resource from the drop-down box. Click OK.

4. In SQL Server Management Studio, use Object Explorer to connect to the SQL Server instance hosting the primary replica of the recreated availability group. Drill into AlwaysOn High Availability, then drill into the recreated availability group and then Availability Group Listeners. You should find the listener. 

5. Right-click the listener and choose Properties. Enter the appropriate port number for the listener and click OK.

Method 3 Drop the availability group and recreate with the same listener name

  This method will result in a small outage for currently connected applications, because the availability group and listener are dropped and then recreated.

1. Drop the problematic availability group. This will also drop the listener.

2. Immediately create a new, empty availability group on the same server hosting the production databases, and defined with the original listener name. Applications should now successfully re-connect by using the new listener. For example assume your availability group listener is 'aglisten.' The following transact-sql creates an availability group with no database or secondary, but creates a listener, 'aglisten' which applications can resume connecting through.

use master
go
create availability group ag
for replica on 'sqlnode1' with (endpoint_url = 'tcp://sqlnode1:5022', availability_mode=asynchronous_commit, failover_mode=manual)
listener 'aglisten' (with ip ((n'11.0.0.25', n'255.0.0.0')), port=1433)
go

3. Recover the damaged database and add it and the secondary replica back to the availability group.