A few days ago I was working on changing the Endpoint URL and Port for a 2016 SQL Server Availability Group to use a private network interface. We wanted this so we could have the Synchronizing (or Mirroring) traffic have its own dedicated network, which would keep it away from the network traffic of client connections to the database server. This would help increase the throughput of data to and from the databases in the Availability Databases list.

Our setup is a straight forward Windows Cluster with two Nodes. We have two stand alone SQL Server Instances running on these Nodes. Each Instance has an Availability Group defined with one Node the Primary Replica and the other Node the Secondary Replica. We run the Replicas in Synchronous Commit Availability Mode with Automatic Failover. All connections are allowed to the Primary Role and the Readable Secondary is set to no.

At the time this was initially setup, the servers had only one network interface for all traffic. A new “private” network interface was installed for the servers to talk directly to each other.

Now it was time to change the configuration of our Availability Group to use this new “private” network. Here are the steps I used to do this (You would need to use your EndpointName, PortNumber, IPAddress, and SQLServer\Instance).

— Step 1 : On the Primary Server
USE [master]
GO
ALTER ENDPOINT [HighAvailabilityDisasterRecovery_EndpointName]
AS TCP (LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
GO

–Step 2: On the Secondary Server
USE [master]
GO
ALTER ENDPOINT [HighAvailabilityDisasterRecovery_EndpointName]
AS TCP (LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
GO

–Setp 3: On the Primary Server
USE [master]
GO
ALTER AVAILABILITY GROUP [AvailabilityGroupName]
MODIFY REPLICA ON ‘SQLServerPrimary\Instance’
WITH (ENDPOINT_URL = N’TCP://IPAddress:PortNumber’)
GO
ALTER AVAILABILITY GROUP [AvailabilityGroupName]
MODIFY REPLICA ON ‘SQLServerSecondary\Instance’
WITH (ENDPOINT_URL = N’TCP://IPAddress:PortNumber’)
GO

I check the Availability Group Properties and the Availability Replicas have the new IPAddress and Port for the Endpoint URLs. Awesome, just what I wanted. I now right click on the Availability Group and select Show Dashboard. Crap, I have the following errors.

In this availability group, at least one synchronous replica is not currently synchronized. The replica synchronization state could be either SYNCHONIZING or NOT SYNCHRONIZING.

In this availability group, at least one secondary replica has a NOT SYNCHRONIZING synchronization state and is not receiving data from the primary replica.

In this availability group, at least one secondary replica is not connected to the primary replica. The connected state is DISCONNECTED.

The availability group is not ready for automatic failover. The primary replica and a secondary replica are configured for automatic failover, however, the secondary replica is not ready for an automatic failover. Possibly the secondary replica is unavailable, or its data synchronization state is currently not in the SYNCHRONIZED synchronization state.

At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state.

This secondary replica is not connected to the primary replica. The connected state is DISCONNECTED.

I am thinking to myself, “What is wrong”? I know that I can ping back and forth between these two machines. So why can’t they Synchronize with each other?

I look back at my notes of when I initially set all this up and a section catches my eye. Our network is locked down and the Windows Firewall on these servers won’t let any traffic through that isn’t setup in the Domain Controller Group Policy for these servers.

Then it dawns on me. The traffic on this new “private” network is not being allowed through Windows Firewall. That is why the Replicas can’t synchronize!

I change the Inbound Rules for Windows Firewall with Advanced Security using Group Policy Management on the Domain Controller. I need to add the new “private” network IP Addresses to the Remote Scope of the rules. After that I do a Group Policy Update on the servers and check the local Windows Firewall to make sure they have the new changes in their Inbound Rules, which they do.

I go back and refresh the Availability Group Dashboard and the Replicas now show Synchronized.

Success!

Norman Paul
Senior Database Consultant
AWS Certified Solutions Architect – Associate Level
International Data Science Corporation
www.idscorporation.net

Leave a Reply

Your email address will not be published. Required fields are marked *