We just went away from SQL Server clusters in SQL Server 2008 to SQL Server 2012 availability groups – have five servers with replicas in a secondary datacenter and syncing over a dedicated line/LAN layer 2 connection - switch to switch.

We had an incident that for the duration of about 2 hours we had several logs showing that replication was terminated and then reestablished with the termination lasting about 10-20 seconds at a pop…happened “a ton” of times in those two hours and our users were complaining of latency and problems accessing the databases on the AG. This happened only on 2 of our 5 AGs that are going between the 2 datacenters.

Assuming intermittent network issues, took a look at switches, firewalls, (all redundant), the eline between and found no indications of any loss of connection issues. No indicators that the SANS had issues either. During those 2 or so hours we also got “blocking” alerts from the DB warden database that runs on all the AGs …for the two that had the replication issues.

If I have ruled out network connectivity issues, should I be looking at the SQL transactions that were running at the time? Is it possible that something in our code/long running job/query could have caused long waits or something that would have triggered the replication errors? If we have replication issues, does that cause latency on the residing databases in the AG?

I am NOT a DBA, unless you count me as accidentally forced into it by default – I'm the network and sys admin/datacenter manager and SQL Server has just fallen into my realm of responsibility so this one has me grasping for explanation to our users.... any suggestions or insight?

  • Are your AGs configured to run in Sync or Async ? I would recommend running in Async unless you have a business need. Also, make sure you enable Trace Flag 1448 - since you are using replication with AG. – Kin Shah Jan 13 '16 at 16:13
  • 1
    @cellyob - are you actually using SQL Server Replication as well as Availability Groups or are you using the term "replication" to mean "sending data to the secondary data center"? – Max Vernon Jan 13 '16 at 16:17
  • "several logs showing that replication was terminated and then reestablished" what are the logs? SQL Server error logs? Can you include the exact errors? – SQL Hammer Jan 13 '16 at 19:32
  • All - thank you for entertaining my 'newbie-ness'. To answer your questions: Kin - we are using Synchronous for business reasons. – cellyob Jan 14 '16 at 16:39
  • @SQL Hammer When we had the complaints, we got this message in the SQL server logs: "source: spid06s A connection timeout has occurred on a previously established connection to availability replica 'xsql01' with id... Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role." then a second or so later we get onnection for availability group 'SQLAG01' from availability replica 'xSQL01' with id ..to 'xSQL01' with id ..has been successfully established. This is an informational message only. No user action is required. – cellyob Jan 14 '16 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.