Amazon Relational Database Service (RDS) now supports multi-availability-zone SQL Servers. This means you can have a primary database in one data center, and a secondary replica in another data center.
When your primary server goes down, OR when the entire AZ goes down, you can fail over to the secondary with zero data loss and keep right on going. When the primary comes back online, you can fail right back, again with no data loss.
Amazon uses replication for their MySQL, Oracle, and PostgreSQL multi-AZ feature, but they chose a different database technology when it came time for SQL Server. They’re using database mirroring, and there’s a few things you need to know before you rely on it.
Technically, database mirroring is deprecated. This means support will be removed in a future version of SQL Server. I’m here to say you shouldn’t care – if it meets your needs today, great. Keep right on using it, because it’s still around in SQL Server 2014, and that’s going to be supported for at least a decade. If the next version of SQL Server actually did remove mirroring support, you’d still have years to make Plan B.
Cross-database transactions are not supported. Distributed transactions and multi-db commits mean your application changes data in multiple databases inside a single transaction. Books Online explains the gotcha. Same thing here – if you’re already using Amazon RDS for SQL Server, and you’re doing cross-database transactions, and you don’t have a Plan B yet, you could start using multi-AZ RDS just to get yourself the beginnings of a safety net. However, you need to start working on Plan B Plan C.
Amazon’s implementation picks safety over performance. They’re using synchronous mirroring, which means whenever you do a delete/update/insert:
- The data is written to the primary’s log file
- The data is sent across the network wire to the secondary
- The data is written to the secondary’s log file
- The confirmation is sent back to the primary
- The transaction is considered committed, and the app is informed
This is going to add latency to your existing application because steps 2-4 aren’t being done today. If you’re worried about performance, this means you have to pay particular attention to each of those steps.
SQL Server also offers asynchronous mirroring where steps 2 through 4 are done in the background without impacting throughput. However, you don’t get automatic failover with async – after all, you wouldn’t want to automatically fail over to a replica that has less data than your primary. Also, async mirroring is an Enterprise Edition feature, and that’s only available in RDS if you bring your own licensing.
Mirroring protects you from some storage corruption. When the primary reads a page off disk and finds corruption, it requests a clean copy of the page from the mirror. This works because mirroring sends log transactions, not data file pages, over to the replica. The secondary responds (hopefully) with a clean copy of the page, and SQL Server fixes the corruption with nary an error message or outage. It’s called automatic page repair, and it gives you another measure of insurance for database reliability.
And finally, mirroring is actually pretty cool for this. You’re probably using Amazon RDS to reduce your systems administration overhead. You want to get more availability and performance without having to run your own SQL Server, and that means you probably don’t have your own full time DBA. Database mirroring meets those needs pretty well, and I can see why Amazon picked it. You’re definitely going to see slower transactional throughput – but hey, that’s where provisioned IOPs comes in. The faster you want to go, the more quarters you put in the front.
I wouldn’t necessarily recommend database mirroring if you were going to manage this infrastructure yourself. Mirroring has some challenges around cryptic error messages and scaling to large numbers of databases, but neither of those are weaknesses in this particular implementation. But with Amazon managing it, and having a relatively small number of databases involved (less than 100), it makes perfect sense.
11 Comments. Leave new
RDS Multi-AZ DB failover is within-region only; you can set up read replicas across region (like us-east-1 to us-west-2), but that’s for MySQL only (http://aws.typepad.com/aws/2013/11/cross-region-read-replicas-for-amazon-rds-for-mysql.html).
“This means you can have a primary database in US East (Northern Virginia), and a secondary replica over in US West (or wherever).”
Uh, Multi-AZ is cross AZ boundaries within a region, not across regions. Synchronous replication from N. Virginia to Sydney? Oh yeah, Amazon totally brokered a deal with physics to get improved latency for you.
Even Multi-AZ for MySQL, Oracle, and PostgreSQL doesn’t do that. Unless you count MySQL’s Read Replica feature which is asynchronous “log shipping”, if you will, and that can work cross region.
There’s always cross region snapshot copies to ship backups, but then you’d need a polling service to kick off the copy, check its state, and spin up a new instance from the latest copy. Like Log Shipping, but not incremental.
Ooo, great point, I should have used a different example there. Thanks! Will fix that in the post.
Hey Brent,
Is there any documentation for how much load a SQL Server instance could handle and still be suitable for Multi-AZ Mirroring?
Ie, could we look as perfMon counters (eg, Log Bytes Flushed / sec,) from an existing on-premise SQL Server installation and figure out if Mutli-AZ Mirroring would be a suitable solution and get an idea of what sort of latency would be introduced for writes.
Thanks,
Dave
David – that’s kind of like “how big is a piece of string?” It depends on your workload, your performance requirements, size of your instance, tolerance to latency, etc.
The easy way to get started is to set up database mirroring in your own shop, in circumstances where you control the components, and figure out whether performance is still tolerable. Then if it’s not, performance tune the app until users don’t complain about the speeds.
For example, in one shop I was working with recently, they stored large amounts of session state data in the main app database. Every page hit required reading and writing large amounts of data into the transaction log. Users were already unhappy with performance, so we started by having the app team move session state into a different platform (not a database), and then the database’s workload decreased enough that the overhead of mirroring (AGs in this case) wasn’t a problem.
Can I use secondary database as read only
Kundan – no.
A couple of things I noticed and not sure if mentioned above.
1. RDS SQL has their own pseudo-AG setup for the set of mirrored Databases….i.e. they control an all-or-nothing failover of the databases. I experimented by failing one database to force the single DB to failover….and expected all of them to go over. BUT, their auto-magical process attempted to fail it back and succeeded so all the other DBs stayed active on the original primary and my experiment came back.
2. their Version upgrade process (SQL2012RTM to SP2 for instance) is semi-manual. You have to initiate it or schedule it for a maintenance window. But if you have mirrored instances, RDS documents a “Snapshot, upgrade and reapply Mirroring” process. I would have thought you could upgrade the primary causing a failover to the secondary….then upgrade the secondary similarly causing a failover to the original….very little actual downtime for the DBs. But they don’t support this. So you need a longer downtime window.
I could be wrong on this, if so please let me know. I’m a longtime DBA but am new to RDS.
hi Brent,
The opening statement and the picture still needs to be corrected as the SQL Server database mirroring is limited to the multi AZ within a region.
looks like SQL Server can be either AG or Mirroring:
SELECT CASE WHEN dm.mirroring_state_desc IS NOT NULL THEN ‘Multi-AZ (Mirroring)’
WHEN dhdrs.group_database_id IS NOT NULL THEN ‘Multi-AZ (AlwaysOn)’
ELSE ‘Single-AZ’
END ‘high_availability’
FROM sys.databases sd
LEFT JOIN sys.database_mirroring dm ON sd.database_id = dm.database_id
LEFT JOIN sys.dm_hadr_database_replica_states dhdrs ON sd.database_id = dhdrs.database_id AND dhdrs.is_local = 1
WHERE DB_NAME(sd.database_id) = ‘rdsadmin’;
in my case it says
+———————+
| high_availability |
|———————|
| Multi-AZ (AlwaysOn) |
+———————+
i.e. not mirroring. I don’t see how to pick one or the other. Wondering if Amazon decides?
yeah, looks like RDS picks. Just spun up a 2014 where AOAGFC isn’t supported and it comes up with mirroring
+———————-+
| high_availability |
|———————-|
| Multi-AZ (Mirroring) |
+———————-+