3

SETUP

  • 2 Amazon EC2 SQL 2014 servers in different availability regions.
  • Synchronous mirroring
  • S3 for archiving full backups & logs at night
  • Right now for simplicity, nightly full compressed backups
  • Tran Logs every 15 mins (would do more if can reduce the runtime)
  • Ola Hallengren backups running on both principal and mirror servers

SLA

My SLA terms to meet don't have heavy restrictions at this point so (odd I know!):

  • "Opps Deleted Something" restores would be a nice to have, but I don't a hard delivery requirement on this right now. I do log backups every 15 mins right now.
  • High-Availability: not fully required, downtime of an hour would be acceptable if needed
  • Point in time recovery: I'm able to provide due to the transaction log backups every 15 mins, however 2 hours would be acceptable for now.

Network Bottleneck

It looks like one of the biggest bottlenecks is the approx 300mb network pipe that seems to get overloaded by the need to synchronously mirror to another EC2 instance. I could maintain a lot more databases on the same instance, reducing costs likely if I removed mirroring.

Mirroring or Alternative Approach For performance with reasonable availability

I'm looking for the best value while still delivering a reasonable uptime. Since I'm on SQL standard, this means that asynchronous mirroring is not an option (we are running sql 2014).

As I've evaluated options I'd appreciate another perspective on basic disaster recovery. The overhead of mirroring seems to be a big bottleneck, but if I remove it then I'm concerned about how to provide better availability. Ideally, I'd run mirroring asynchronously, but we'd need to move backwards in sql server editions, and not sure if that would be the best approach

always on high availability groups

Not proceeding with this due to the increased complexity and licensing cost at this time. Open to exploring in future, but at this time looking to avoid Enterprise licensing cost.

3

It sounds like you may benefit from a Failover Cluster if all you're looking for is availability. You can run them on standard edition and if you take an N+1 route, you can have two nodes for HA and have one in a DR site ready to take over. You'll need to work on your storage availability for the DR side, but that's a whole other can of worms.

Another option may be Log Shipping since you're already taking log backups every minute. You can ship the logs off to your HA/DR server.

You can get more information on how to do it and the implicatioons for AWS here:

Implementing Microsoft Windows Server Failover Clustering and SQL Server AlwaysOn Availability Groups in the AWS Cloud

  • that link seemed focused on always on, and didn't really give something specific to my question about failover clustering (outside of availability groups). If you have more specific info it sure would help for future folks as well. – SheldonH Feb 3 '16 at 22:30

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.