sql server

ThePolyglotDBA

SQL Server Replication – Recovering from a Network Outage

In continuing my on-going issues with SQL Server replication the past couple of weeks, we had a data center outage on Monday, which resulted in a forced failover to our DR data center.  Our MySQL database applications successfully failed over and required no followup.  We did run into 1 peer-to-peer row conflict, which I was able to address, similar to the issue late last week I documented here.

Things looked to be  running along fine, and I could see that my DR database was up to date with my production database.  My monitoring system was showing some undistributed transactions, but nothing that seemed too outrageous.  But I was continuing to get a few Network error notifications, which lead me to look at the SQL Server Replication Monitor.  Everything was green, but my undistributed transactions were showing well over an hour.

Continue reading

ThePolyglotDBA

SQL Server Peer-to-Peer Replication – When a Conflict is Detected

Yesterday we hit the dreaded conflict in our production/DR SQL peer-to-peer replication setup.  My first reaction is usually “oh <expletive>, we’re going to need to rebuild replication”, but we happened to have the best possible scenario where we had an update-update conflict.

I’d like to note that I personally hate working with SQL Server peer-to-peer replication (we’re running SQL 2008 R2 version), and it looks like some of the limitations of the solution are still an issue in SQL 2012.

  • We have to quiesce (i.e. stop the application) in order to add new articles (tables, views etc), to the replication topology.  Big pain + off hours work.
  • Plus, unlike with MySQL replication when setup in multi-master, I can’t just not log my correction statements so they don’t replicate back.
  • This is why I’m excited for the Always On Availability groups in SQL 2012/2014, with a multi-subnet setup.  I’m still evaluating our environment options but this helps address some of the limitations in our peer-to-peer replication environment.

Continue reading