Platform-as-a-Service users (Azure SQL DB, Amazon RDS) often ask me:
- How can I move my data into the cloud? Can I just take a backup on-premises, and restore up in the cloud?
- How can I use the cloud as inexpensive disaster recovery?
- Once I go to PaaS, why can’t I just get a backup file with my data?
- How can I refresh an on-premises dev server from cloud production?
- How can I do cross-provider disaster recovery inexpensively, like have a primary in AWS and a secondary in Azure?
- Why am I locked into just one cloud provider once I go PaaS?
Until now, the single biggest problem has been that both Azure SQL DB and Amazon RDS SQL Server don’t give you access to backup files. If you wanted to get your data out, you were hassling with things like import/export wizards, BCP, or sync apps.
Today, Amazon RDS SQL Server announced support for native backup/restore to Amazon S3.
This is a really, really, really big deal, something Azure SQL DB doesn’t support (and I dearly wish it did). I get even more excited reading this because now Microsoft has to do it in order to remain competitive, and that’ll make Azure SQL DB a much more attractive product for traditional DBAs.
Here’s the use cases that it supports:
“I’m on-premises, and I want to use the cloud as DR.” Just keep taking your full backups as normal, but use a tool like Cloudberry Drive to automatically sync them to Amazon S3. When disaster strikes (or preferably, when you want to test and document this process long before disaster strikes), spin up an Amazon RDS SQL Server instance and restore your backups. Presto, you’re back in business. (I’m glossing over all the parts about setting up web and app servers, but that’s a developer/devops/sysadmin problem, right?)
“I have big databases, and I want to experiment with the cloud, but can’t I upload fast.” Ship your USB hard drive to Amazon with your backups, they’ll copy ’em into S3, and then you can spin up RDS instances. Got more data? Check out Amazon Snowball.
“I’m using the cloud, and I want cross-provider DR.” Run your primary SQL Server in Amazon RDS, schedule regular backups to Amazon S3, and then use a cross-provider file sync tool or roll your own service to push those backup files from Amazon S3 over to Azure or Google Drive. When disaster strikes at Amazon (or if you just want to bail out of Amazon and switch cloud providers), just restore that backup somewhere else. Same thing if you want to refresh a local dev or reporting server, too.
“I’m using the cloud, but I might outgrow Platform-as-a-Service.” PaaS makes management dramatically easier, but both Amazon and Azure set limits on how large your databases can get. Putting your database in Amazon RDS or Azure SQL DB is basically a bet that your data will grow more slowly than their database size limits. If you bet wrong – which is a great thing because your data skyrocketed, usually indicating that you’re in the money – you have an easy transition into IaaS (self-managed SQL Server in the cloud) rather than the painful hell of dealing with data exports.
This right here changes every SQL Server cloud presentation that I give. It’s really that big.
Fantastic, This is a feature really needed in Azure SQL Database and now the pressure will be on. Thanks for the post!
Can’t wait for this to hit Azure…wonder if we are talking weeks, months or quarters?
I love positive breaking news and this is great! Thanks Brent!
How long before that picture becomes a meme …. “YOU FORGOT THE WHERE CLAUSE!!”
Bonus points if the meme-maker uses the green board behind him to put him somewhere else…
Maybe the bridge of the Enterprise (TOS or USN aircraft carrier) or the cockpit of the Millenium Falcon…
This changes how I look at paas, that’s for sure.
This is a great opportunity for those looking for easier ways to implement cloud based DR options; looking forward to Microsoft’s response to this solution.
Excellent news to push Microsoft into following suit on Azure – trust it to take Amazon to do the blindingly obvious… with a suppliers own product!
AWS should do SharePoint Online too, so we can finally get Content DB backups!
Looks like you can only restore full backups and cannot append transactional backups.
If you cannot restore DIFF or TLOG backups then this doesn’t really work as a DR solution – unless you are cool with losing all data after your last full backup .
Hopefully I am wrong on this, but either way, still a great feature.
Kevin – in many shops I see out there, especially the ones who can’t afford a secondary warm site, a day’s worth of data loss is fine if they can be back up again within an hour. (After all, we’re talking disaster recovery here, not high availability.)
Or, if you’re dealing with a small enough database, more frequent full backups (say twice or three times a day) to cut down on the possible data loss.
As my twelve-year-old son would say, “Well, this is awkward.” The fact that Amazon got out of the gate with this first is a little egg-on-face, but it will almost certainly change Microsoft’s plans (or advance their timeline, if already underway) for Azure support of this functionality.
Wow! The cloud is slowwwwwwly catching up with what I have been able to do on-premises for years and years and years. It is amazing how little we expect from the great & wonderful Oz (I mean cloud)!
There is no better way to be impressed by Amazon RDS that by just signing up for a free account, and trying out their offering. For free, you get a fairly trivial-sized database server, but it’s enough to play with all of the features and check it out.
IMHO, the Azure dashboard is easier to understand. But having said that, getting Amazon RDS SQL Server set up correctly the second time (after you’ve screwed up the first time) is very easy!
Now that Amazon stepped up…are blog photos next? Maybe Brent superimposed on some major landmark instead of a green screen. 🙂
Just pickin’ Brent, thanks for everything you guys do whether you’re green or not!
Damian – hahaha, thanks sir.
Brent- Very well articulated article about why this is so important. Thank you for posting this!
Tim – thanks sir!
This would be incredible news if RDS, combined with code-first Entity Framework hadn’t already made PostgreSQL a clear choice over SQL Server, given the price point.
David – your comment would be incredibly insightful if the real world didn’t already have existing applications.
I am wondering who told you? Did the Bat Phone ring in you secret penthouse? Commissioner Ernie blast the bat signal? I did see the new bat mobile, awesome. Once again, thanks for info.
Ian – hahaha, I read the AWS blog religiously. There’s not usually good stuff in there for SQL Server, but when there is, it’s awesome.
Brent- I had some discussion with a Microsoft person who knows quite a bit about this thing. They pointed out a couple things. One item being that Azure SQL DB really isn’t same thing (internally or externally) as boxed-copy SQL Server that we have on-prem. And from what I’ve seen, AWS RDS does appear to be plain-vanilla SQL, with some special lock-downs and special bits around it.
Another big item I was reminded of, is that Azure SQL DB is a higher version level of SQL than any on-prem SQL. And of course, we’ve never been able to backup from a higher SQL version and restore it to prior versions of SQL.
So I think we can probably expect Azure to continue to make SQL data transfers smoother. But it may not be realistic to expect full two-way BAK transfers.
Tim – that doesn’t explain why we can’t do one-way backups. You’ve always been able to restore a database to a higher version of SQL Server, but we can’t do that with Azure SQL DB. Did you ask why?
(I’m kinda dancing around what I can say here.)
Nice post! I also like that this feature offers encrypted backups using KMS on all editions of sqlserver while Microsoft supports encrypted backups only on SE and EE. Way to go RDS!
This is awesome news! Does this new feature support restoring multiple backup files?
KL – no clue on that one.
I’m dying here. I forgot I had cloud-to-butt extension on. Oh my spleen!
This is pretty great for me. It’ll be great to move data around without giving access to my aws account.