We are running a payments (EFT transaction processing) application which is processing high volumes of transactions 24/7 and are currently investigating a better way of doing DB replication to our disaster recovery site.

Our current and previous strategies have included using both DoubleTake and Redgate to replicate data to a warm stand-by.

DoubleTake is the supported solution from the payments software vendor however their (DoubleTake's) support in South Africa is very poor. We had a few issues and simply couldn't ever resolve them so we had to give up on DoubleTake.

We have been using Redgate to manually read the data from the primary site (via queries) and write to the DR site but this is:

  1. A bad solution
  2. Getting the software vendor hot and bothered whenever we have support issues as it has a tendency to interfere with the payment application which is very DB intensive.

We recently upgraded the whole system to run on SQL 2008 R2 Enterprise which means we should probably be looking at using some of the built-in replication features.

The server has 2 fairly large databases with a mixture of tables containing highly volatile transactional data and pretty static configuration data.

Replication would be done over a WAN link to a separate physical site and needs to achieve the following objectives.

RPO: Zero loss - This is transactional data with financial impact so we can't lose anything. RTO: Tending to zero - The business depends on our ability to process transactions every minute we are down we are losing money

I have looked at a few of the other questions/answers but none meet our case exactly:

  1. SQL Server 2008 failover strategy - Log shipping or replication?
  2. How to achieve the following RTO & RPO with logshipping only using SQL Server?
  3. What is the best of two approaches to achieve DB Replication?

My current thinking is that we should use mirroring but I am concerned that for RPO:0 we will need to do delayed commits and this could impact the performance of the primary DB which is not an option.

Our current DR process is to:

  1. Stop incoming traffic to the primary site and allow all in-flight transaction to complete.
  2. Allow the replication to DR to complete.
  3. Change network routing to route to DR site.
  4. Start all applications and services on the secondary site (Ideally we can change this to a warmer stand-by whereby the applications are already running but not processing any transactions).

In other words the DR database needs to, as quickly as possible, catch up with primary and be ready for processing as the new primary. We would then need to be able to reverse this when we are ready to switch back.

Is there a better option than mirroring (should we be doing log-shipping too) and can anyone suggest other considerations that we should keep in mind?

  • 1
    Don't know why you wouldn't get it from the horses mouth, call microsoft spend a little money and get things done. – tony roth Jun 28 '12 at 13:48

You're asking the right questions and have a good handle on the problem, however the bar is set fairly high. You're hitting a wall looking for something that may be elusive with technology that was developed five years ago. And you've already test-driven two vendors in the field and they aren't up to the challenge.

Plan for the future. What you have now is what it is. You can try to re-engineer existing or older technology, but moving to the next version may be an option worth investigating.

I suspect that the shortcomings you describe with 2008 mirroring is why Microsoft introduced the AlwaysOn capability in SQL Server 2012. 2008 Mirroring is actually quite good unless you have a high-latency connection to the mirror and are using high-safety mode. If you have a high-volume of transactions and are dealing with money, high-safety or high-performance isn't an easy decision.

My own prediction is that the so-called "cloud" providers will actually turn out to be a natural fit for many DR scenarios. They have technology and expertise that most companies cannot afford, and are pressing the envelope on what's possible.

Asynchronous Database Mirroring (High-Performance Mode)

Introducing SQL Server AlwaysOn

AlwaysOn FAQ for SQL Server 2012

| improve this answer | |

You can also go for replication at the storage level. So you can get replication on the application Layer- your database and at the storage level.

Since this application is critical with low RPO and RTO, you can look for a Synchronous mirror, so that updates are made as soon there is a delta in primary side.

Cloud is a good option. It offers great agility, speed, pay-as-you-go model, economies of scale, global reach, etc but since this is payment and banking related requirement, you should choose private cloud for better security. On the flip side, private cloud will add to your opex and overall cost significantly.

So you might consider a replication technique at software and Infrastructure level.

| improve this answer | |

Your Answer

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

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