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