SQL Server 2012 alwayson--Specifies a data synchronization link to eliminate commit latency issues caused by network jitter

Original: SQL Server 2012 alwayson--Specifies a data synchronization link to eliminate commit latency issues caused by network jitter

Event Cause: Recent research and development reaction, a database from 08 to 12 environment, the occurrence of write operation delay problem;

Event Analysis: After troubleshooting problems such as system resource contention, preliminary analysis may be caused by network jitter that causes the synchronization mode AlwaysOn node to frequently have a session timeout waiting to be committed.

After troubleshooting, the extended event found an irregular 35202 error, which is a copy of the connection recovery message.

Because of the complex network environment in the computer room, the database server and Application Server mix a switch, in the peak of business, because the uplink port traffic is full and the connection failure occurs repeatedly.

Since the network environment can not be modified in the short term, from the SQL Server servers themselves, only the data synchronization part of the transformation;

Existing environment:

SQL AG: Two-node synchronization mode, two nodes each with a single NIC connected to the switch, no direct connection jumper (WSFC no longer requires a separate heartbeat network)

Retrofit Program:

1, two nodes each enable a network card, the use of direct connection to communicate, and configure the private address

server_a:10.0.0.11

server_b:10.0.0.12

2, delete the two node endpoint, manually re-create the LISTENER_IP for the direct-connect IP Endpoint

3. Change the endpoint_url of each copy in AG

4, waiting for data resynchronization;

The third step of the script is as follows, to operate on two nodes separately, note that LISTENER_IP is the IP of the direct-attached network card

1 /** * * * * object:endpoint [hadr_endpoint] Script DATE:2015/1/6 16:06:17 * * * * **/2 DROPENDPOINT[Hadr_endpoint]3 GO4 5 /** * * * * object:endpoint [hadr_endpoint] Script DATE:2015/1/6 16:06:17 * * * * **/6 CREATEENDPOINT[Hadr_endpoint] 7State=STARTED8           asTCP (Listener_port= 5022, LISTENER_IP=(10.0.0.11))9           forData_mirroring (ROLE=  All, authentication=WINDOWS NEGOTIATETen, encryption=REQUIRED algorithm AES) One GO

View Code

The fourth step of the script is as follows, in the primary replica execution can

1 ALTERAvailabilityGROUP [Alwayson01]2MODIFY REPLICA onN'node_01'  with(Endpoint_url=N'tcp://10.0.0.11:5022')3 4 ALTERAvailabilityGROUP [Alwayson01]5MODIFY REPLICA onN'node_02'  with(Endpoint_url=N'tcp://10.0.0.12:5022')

View Code

Note: The two copies are not synchronized after the endpoint is deleted, but the databases in the Listener and AG group are not affected, and the service of the primary replica is still normal for the application;

Reference article:

http://blogs.msdn.com/b/alwaysonpro/archive/2013/11/01/ Configuring-a-dedicated-network-for-replicating-changes-from-primary-to-secondary-replica.aspx

SQL Server 2012 alwayson--Specifies a data synchronization link to eliminate commit latency issues caused by network jitter