patternsqlMinor
Recommended mirroring alternative?
Viewed 0 times
recommendedalternativemirroring
Problem
SETUP
SLA
My SLA terms to meet don't have heavy restrictions at this point so (odd I know!):
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.
- 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.
Solution
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
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
Context
StackExchange Database Administrators Q#123461, answer score: 7
Revisions (0)
No revisions yet.