Shulou(Shulou.com)05/31 Report--

This article introduces how to set up AlwaysOn in SQLServer 2012 to solve the submission delay caused by network jitter. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Cause of the incident: there has been a recent R & D response. After a database was switched from 08 to 12, there was an irregular delay in the submission of write operations.

Event analysis: after excluding the problems such as system resource contention, the preliminary analysis may be due to network jitter, which may lead to the problem of session timeout waiting for submission in synchronous mode alwayson nodes.

After investigation, an occasional 35202 error was found in the extension event, which is a message of replica connection recovery.

Due to the complex network environment of the computer room, the database server and the application server are mixed with a switch. during the peak period of business, the connection failure occurs frequently due to the full traffic of the uplink port.

Since it is impossible to transform the network environment in the short term, we should start from the SQLSERVER server itself and only reform the part of data synchronization.

Existing environment:

SQL AG: for the synchronous mode of two nodes, each node has a network card connected to the switch and no directly connected heartbeat cable (WSFC no longer requires a separate heartbeat network)

Transformation plan:

1. Each of the two nodes enables a network card, communicates in a directly connected way, and configures a private address.

The code is as follows:

Server_A:10.0.0.11Server_B:10.0.0.12

2. Delete the endpoint of the two nodes, manually recreate the endpoint3 whose Listener_IP is a directly connected IP, change the endpoint_url4 of each copy in the AG, and wait for the data to be synchronized again

The script for the third step is as follows. To operate on the two nodes separately, note that Listener_IP is the IP of the directly connected network card.

The code is as follows:

/ * Object: Endpoint [Hadr_endpoint] Script Date: 16:06:17 on 2015-1-6

* / DROP ENDPOINT [Hadr_endpoint] GO

/ * Object: Endpoint [Hadr_endpoint] Script Date: 16:06:17 on 2015-1-6

* / CREATE ENDPOINT [Hadr_endpoint] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.0.0.11)) FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES) GO

The script for step 4 is as follows, which can be executed in the master copy.

The code is as follows:

ALTER AVAILABILITY GROUP [Alwayson01] MODIFY REPLICA ON Ninth Node 01' WITH (ENDPOINT_URL = Ninth TCP Varix Greater 10.0.0.11V 5022')

ALTER AVAILABILITY GROUP [Alwayson01] MODIFY REPLICA ON Ninth Node 02' WITH (ENDPOINT_URL = Ninth TCP Varix Greater 10.0.0.12 Vortex 5022')

Note: after deleting the endpoint, the two replicas are out of sync, but the databases in the listener and AG group are not affected. For the application, the service of the master replica is still normal.

This is the end of how to set up AlwaysOn in SQLServer 2012 to solve the submission delay caused by network jitter. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.