Ygthkb

What is the reward?

Why write a book when there's a movie in my head?

Are one-line email responses considered disrespectful?

Did ancient Germans take pride in leaving the land untouched?

Boss asked me to sign a resignation paper without a date on it along with my new contract

bash aliases do not expand even with shopt expand_aliases

What is this mysterious *green square* on my Content Editor?

How to know you are over-explaining and oversimplifying a subject?

How do I make my single-minded character more interested in the main story?

SQL Server Service does not start automatically after system restart

Sets which are both Sum-free and Product-free.

Taking an academic pseudonym?

Is there any way to play D&D without a DM?

I am a loser when it comes to jobs, what possibilities do I have?

Does しかたない imply disappointment?

What happens if both players misunderstand the game state until it's too late?

Can I legally make a website about boycotting a certain company?

How bad is a Computer Science course that doesn't teach Design Patterns?

How can changes in personality/values of a person who turned into a vampire be explained?

What does @ mean in a hostname in DNS configuration?

How to Build a List from Separate Lists

Why does this quiz question say that protons and electrons do not combine to form neutrons?

Is there a configuration of the 8-puzzle where locking a tile makes it harder?

Python to write multiple dataframes and highlight rows inside an excel file

SQL Server 2012 AG replication issues

Availability Groups and Transactional ReplicationIs MySQL Replication Affected by a High-Latency Interconnect?Scaling Percona datacenters: setup and replicationSQL Server Replication Alternative SoftwareSQL Server: multiple failover cluster instances with disjoint storageMysql Master-Master with additional slavesSQL Server 2012 Replication - MSrepl_commands growing all set to in progressHow to configure multi-site SQL Server 2014 Always On Availability Group for DRAG listener issues with ApplicationIntent=ReadOnlyAvailability Groups using Multi-Subnet Clustering: Preferred Owners for Roles and Possible Owners for AG Listener IPsAlways On/SQL Server Replication for Dummies

1

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?

sql-server performance replication availability-groups

share|improve this question

edited 12 mins ago

Tony Hinkle

2,5851422

asked Jan 13 '16 at 16:03

cellyobcellyob

62

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  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
  

  
  
  
  

 | 
show 4 more comments

1

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?

sql-server performance replication availability-groups

share|improve this question

edited 12 mins ago

Tony Hinkle

2,5851422

asked Jan 13 '16 at 16:03

cellyobcellyob

62

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  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
  

  
  
  
  

 | 
show 4 more comments

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?

sql-server performance replication availability-groups

share|improve this question

edited 12 mins ago

Tony Hinkle

2,5851422

asked Jan 13 '16 at 16:03

cellyobcellyob

62

share|improve this question

edited 12 mins ago

Tony Hinkle

2,5851422

asked Jan 13 '16 at 16:03

cellyobcellyob

62

share|improve this question

edited 12 mins ago

Tony Hinkle

2,5851422

asked Jan 13 '16 at 16:03

cellyobcellyob

62

edited 12 mins ago

Tony Hinkle

2,5851422

edited 12 mins ago

Tony Hinkle

2,5851422

asked Jan 13 '16 at 16:03

cellyobcellyob

62

asked Jan 13 '16 at 16:03

cellyobcellyob

62