Recommended mirroring alternative?

Question

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.

Answer 1

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

Answer 2

Option 1

Not proceeding with [AlwaysOn Availability Groups] due to the increased complexity and licensing cost at this time

I disagree with this characterization of AG (complexity is a shaky reason to not pursue what has become a fairly standard feature in MSSQL) and the claim of licensing costs being a deal breaker.

The fact is, you're on 2014 which ended mainstream support years ago and using a feature (mirroring) which has been deprecated for more years than that (though knowing Microsoft it will stick around for another decade or so).

If you upgrade to any newer version, you're going to get Basic Availability Groups, which I honestly believe fits your use case. Here's why:

...looking to avoid Enterprise licensing cost.

That's cool, Basic Availability Groups are available on standard editions once you upgrade. You'll want to check with Microsoft, but chances are you may also be able to not pay licensing on the secondary replica as well. You could save money.

It looks like one of the biggest bottlenecks is the approx 300mb network pipe...

You got bottlenecks - AG has compression.

Ideally, I'd run mirroring asynchronously...

You want async? Basic AGs have it.

Option 2

All that being said, if you're on 2014, in AWS, and want simplicity, just toss the databases into RDS with Multi-AZ for high availability and call it a day.

Answer 3

So achieving the best solution with the least amount of money happens to be my speciality.

I would recommend using Redgate software's SQL Compare tool and run database syncs (Typically completes in less time than tlog shipping.

I do these in 3 minute intervals, I have my DB read server separated from my DB write server.

Focus reporting and DB retrieval from the DB read servers and focus only writes to the DB write server. (Practice is more difficult than theory but your customers will thank you).

Use standard SQL licenses on your server but remember the most important part of setting up your network ALWAYS CREATE A DEDICATED NIC FOR BACKUPS and DB Syncs and tell that non-prod data to step aside. (Just remember that you might need to add a route rule for the backup NIC)

Always validate that traffic is flowing as you intend. Now depending on the size of your DB I could make some recommendations on some settings that would speed up all operations back from seconds to MS but I'll wait for someone to ask that question