Brent Ozar Unlimited®

How Azure SQL DB Hyperscale Works

Microsoft is starting to talk about the internals for Azure SQL DB Hyperscale, their competitor to Amazon Aurora. Aurora took the open source MySQL and PostgreSQL front ends and hooked them up to much more powerful cloud-first storage engines on the back end. Here’s a quick primer on how Microsoft is doing something similar – taking the front end of SQL Server (the query optimizer and query processor), and hooking them up to a new back end.

When your database server runs on physical hardware, it looks like this:

(I’m making some assumptions here, like enterprise-grade hardware with redundancy, because that’s the kind of gear I usually see backing SQL Server. You, dear reader, might be a rebel running it on an old Raspberry Pi. Whatever.)

Traditional database server high availability

Usually when DBAs sketch out a failover architecture, the drawing involves duplicating this diagram across several independent servers. Data is kept in sync by the database server (SQL Server, Postgres, MySQL, Oracle, etc) by having the database server replicate logged commands over to other replicas:

That works.

A way less popular method (but still technically possible) involves separating out the database server from its file storage. You can put your databases on a file server, accessible via a UNC path.

That way, if the database server blows chunks, you could spin up another database server, attach the data & log files, and keep right on truckin’. This is a fairly unusual approach these days, though, due to challenges with networking, slow time to spin up another database server, etc.

But what if you were going to redo this for the cloud?

Conceptually, separate out the storage.

The RAID card’s responsibilities are:

In our new cloud design, we’re going to build up a beefier storage subsystem. We’re going to replace the RAID card with a bigger design, and in this bigger design, we’re going to give the storage some more job duties than it had before (moving up the stack into the database server’s job duties.) We’re going to:

Here’s how the architecture would look:

When you do an insert, conceptually:

This comes with some interesting advantages:

Here’s how Azure SQL DB Hyperscale does it.

In a conventional SQL Server, for large databases, you might create multiple data files, and put each data file on a separate volume.

In Hyperscale, Microsoft does this for you automatically, but in a spiffy way: when you see a data file, that’s actually a different page server. When the last page server is ~80% full, they’re adding another page server for you, and it appears as a new data file in your database. (Technically, it’s two page servers for redundancy.)

Challenges for both Aurora and Hyperscale (I hesitate to call these drawbacks, because they’re fixable):

Both AWS Aurora and Microsoft Azure SQL DB Hyperscale take the same approach here, offloading deletes/updates/inserts to a log service, and then letting the log service change the data file directly.

The next level: scaling out the compute

A few paragraphs ago, I said that the log service could add more databases and more replicas of each database with zero additional overhead on the query processor. Well, why not take advantage of that?

In Azure SQL DB Hyperscale, this is done with an Always On Availability Group listener. When application wants to query a readable replica, the app specifies ApplicationIntent=ReadOnly during their connection, and they’re automatically redirected to one of the readable replicas.

Similarly, in AWS Aurora, applications connect to a different DNS name for read-only connections. AWS has an interesting feature: for some kinds of queries, Aurora will parallelize your query across multiple readable replicas. The DBA in me loves that, but at the same time, if your queries are so rough that you need multiple replicas to accomplish ‘em, you’re already sitting on a ticking time bomb: you’re not going to be able to run a lot of those simultaneously, and AWS charges you for each IO you perform. This is throwing money at bad queries – but hey, sometimes that makes sense.

Caching is of course a little trickier here. With traditional SQL Server Availability Groups, the database engine is receiving each log change, so it knows which pages need to be changed, and there’s no danger of it serving stale data from cache. Now, with the data pages being changed by the log service, there’s a risk that the replica could serve stale data. Me personally, I’m not too worried about that because that was always a possibility with Availability Groups, too. (Async replication traffic can get delayed (or even suspended), and the replica can get behind in applying changes.)

This design has a huge advantage: the replicas don’t need to talk to each other, nor do they need to be physically nearby. As far as they’re concerned, they’re just reading from data and log files. They don’t need to know anything about the magical Internet beans that are refreshing the data and log files right underneath them.

Who should consider Hyperscale and Aurora?

If all of these match, talk to your doctor about AWS Aurora or Azure SQL DB Hyperscale:

I’m a huge fan of this architecture. We picked AWS Aurora when we built SQL ConstantCare a few years back, but only because there wasn’t anything like Azure SQL DB Hyperscale at the time. Today, I’d have a much tougher time making that decision.

For the record, I’m writing this as an interested database geek, not as a sales or marketing pitch. We don’t specialize in Aurora, MySQL, Postgres, or Azure SQL DB Hyperscale – I’m just sharing it because I find the products really interesting. If you want to learn more about this stuff, here’s where to go next:

Oh, and the diagrams – Apple Pencil, iPad Pro, and the Procreate app. Highly recommended. I can’t promise that my art quality will get any better, but I’ll do more of these to illustrate concepts. It’s fun!