0

I have installed SQL Server 2012 Clustered HA with Replica in Sync mode between two nodes connected by an high-speed VPN. The DB is encrypted. Everything works fine, but to do the replication, the HA system takes a lot of time.

The DB is made up of 90 tables of which only 10 are fast-moving. The average for the 10 tables is equal to 100,000 records. The others have from 1,000 to 25,000 records. Can you explain to me what is due to the slowness?

The Primary Server how often starts the synchronization mechanism with Secondary and the Secondary how often start the same sync with Primary server (I have chosen MASTER-MASTER HA replica)?

2
  • 1
    Are you using transactional replication along with alwaysON ?if yes you would need trace flag 1448. I recently faced this problem and the trace flag resolved it.
    – Kin Shah
    Sep 26, 2015 at 16:55
  • Please add more details. How many rows replicated per minute? What is the maximum and average size of a row? Are you replicating large documents? Are you running on a VM that may be under stress from other VMs on the same box? Et cetera. More details, clearly outlined would help.
    – RLF
    Sep 26, 2015 at 16:55

1 Answer 1

0

Because you're using Synchronous replication, all transactions have to be signed off by both servers before your query can complete. You say you have a 'high speed' VPN, but, you don't make any mention of latency or bandwidth.

I suspect that you'll never be happy with the performance of synchronous replication, due to the additional latency. You should strongly consider setting the HA replication to Asynchronous.

Source: https://msdn.microsoft.com/en-us/library/ff877931.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.