You have two basic options:
Infrastructure as a Service (IaaS) is just VMs running in someone else’s data center – be it Amazon, Google, or Microsoft. Job duties for a DBA are the same as on-premises virtual machines: you manage HA, DR, patching, backups, and performance tuning. It’s just that you may have slightly different techniques or setup steps to accomplish those – but no different than the amount of change you had to do to switch from physical to virtual machines.
Platform as a Service (PaaS) is very different. Amazon RDS for SQL Server and Microsoft Azure SQL DB are like getting DBA-as-a-service. They manage HA, DR, patching, backups, and in some limited cases, performance tuning.
Your opinion of these two options usually depends on your job role:
Production DBAs usually insist on Infrastructure-as-a-Service.
Production DBAs look at the missing features in Platform-as-a-Service offerings and say, “Wait, you’re telling me I don’t get SQL Agent? I can’t directly attach a database? I can’t run sp_configure or tweak my TempDB settings? Man, screw that, that’s not a real database!” Production DBAs tend to want the hands-on control that Infrastructure-as-a-Service provides.
The problem here is that Infrastructure-as-a-Service doesn’t make your job much easier: you still have all of the job duties you had before, plus you need to learn new tools for deploying and troubleshooting Windows instances. You need to learn to treat your servers like cattle, not like pets, and use automated deployment tools.
I’m not saying IaaS is bad – it’s wonderful. As a production DBA myself, I love it.
But everybody else wants Platform-as-a-Service.
Because everybody else never really liked production DBAs to begin with.
See, production DBAs are notorious for saying “No” – as in no, you can’t have SA access. No, you can’t enable that feature. No, you can’t put the SQL Server in the DMZ. Developers, managers, and end users would love to find a way to avoid hiring the Don’t Bother Asking role.
With PaaS solutions, you pay the hosting provider to handle many of the traditional production DBA duties. (In effect, the hosting provider has the new Don’t Bother Asking role, like when Microsoft removed SQL CLR support from Azure SQL DB with one week notice.)
Developers love PaaS because there’s no DBA around to say no. Managers love PaaS because they’re having a hard time filling production DBA job openings. DBA consulting companies love PaaS because they can help clients migrate to the cloud, relieving some of the production DBA pressures.
The PaaS market is still shifting a lot right now.
As 2017 comes to an end, there are 3 options for SQL Server:
Amazon RDS for SQL Server – Amazon buys the SQL Server boxed product, installs it in EC2 (VMs), makes some changes to it so they can do management, and then resells it to you. This means it’s the same boxed product you know and love, albeit with a lot of unsupported features (AGs, stretch databases, BULK INSERT, CDC, DQS, log shipping, database mail, distributed queries, linked servers…)
Microsoft Azure SQL DB – think of it as the next version of SQL Server, but hosted by Microsoft. While it shares some of the same code base, it is most definitely not the same as the boxed product. For example, cross-database queries are technically doable but practically a giant pain in the rear. This isn’t a great fit for lifting-and-shifting an existing application up to the cloud, but it is a wonderful fit for brand-new from-scratch applications.
Microsoft Azure SQL DB Managed Instances – only in private preview now, but think of ’em like the traditional SQL Server boxed product, but with Microsoft managing it for you. This is a great fit for lifting-and-shifting an existing app, but pricing is likely to be more expensive than Azure SQL DB. (That isn’t a bad thing – it just means you shouldn’t start here for new app builds.)
In 2018, we’re likely to see:
- Microsoft going general-availability with Managed Instances, revealing pricing and region availability
- Amazon switching to something better than database mirroring for RDS HA/DR
- Other players entering the market
I’m excited to see how this shapes up!
Update 2018/12/31: I had a note in my task scheduling tool (RememberTheMilk) to write an updated version of this post in December 2018. Not much has changed – Microsoft did indeed bring Managed Instances into General Availability, but that’s it. Nobody else has entered the market, and Amazon’s still using database mirroring. No sense in writing another post. See you in 2019!
25 Comments. Leave new
As a long term trad-DBA the only time I’m glad I’m nearly 60yrs is when the Azure update lands in my in-box.
PAAS is so attractive, I can see it! Everything is taken care of –
database backups, TDE, geo-replication,
automated tuning – may not be perfect but 90% of the time it’ll help,
and so on – the list is growing monthly.
My company can’t wait to put everything in the cloud. I mean everything. data…, services… the lot.
Using the herd analogy favoured by the cloud architects, DBAs are about to go way of cattle rather than pets – taken round the back of the coswhed and…BAM!.
So, youngsters, embrace the change, learn about databases sure , but move towards becoming Azure architects, learn how to exploit the cloud to achieve overall goals at the lowest cost, learn powershell and automate your compute…!
Until you get the bill……. we had a v v v high performance database app and ran some trails on AWS RDS. When real world workloads (remember that phrase when you check marketing prices) produced a projection cost of $64k a MONTH suddenly the on prem DBA massaged solution didn’t seem such a bad idea. Problem is some don’t find this out until they’ve migrated and the FD comes barrelling into the office asking what the f***ing hell this 5 figure bill is for! When you say it’s per month it don’t get better…..
I totally agree with patchyD if you have another 20+ years of work remaining, you better starting learning powershell, cloud development or some other technology. If not Walmart will be hiring a lot of DBA’s to check receipts at the door.
James — sure, if you specialize in fixing the kind of low hanging fruit that automatic tuning can handle, like A/B testing missing index requests from the DMVs, or forcing a single plan that’s the least bad.
Anyone doing more involved work will be safe for a long time. I’m personally excited that I probably won’t have to deal with the little problems anymore, so I can focus on the bigger problems. They’re far more interesting.
“automated tuning – may not be perfect but 90% of the time it’ll help” – uhhh, I would argue very strongly against that, given what I’ve seen in the cloud, but I’m glad 90% of your queries have suddenly been fixed by automated tuning.
Brent – I generally enjoy anything you and your team produces. But I get the distinct impression you would love for the Azure SQL PaaS offerings to just go away so that the traditional DBA doesn’t lose his or her job.
Database performance tuning in “limited cases”. You need to do your homework. It’s staggering how Microsoft is using machine learning to auto-tune databases at scale (and this will get even better over time). There are case studies where companies are managing hundreds of Azure SQL Databases, WITH NO DBA, because of automatic tuning. And, ““Wait, you’re telling me I don’t get SQL Agent?”. You mentioned Azure Managed Instances but fail to mention that it will provide full SQL Agent support. Finally, at least some of your tools don’t support Azure SQL Database at all (i.e., your new BlitzFirst Power BI toolset.)
Azure SQL PaaS services are here, they are very good and getting better each day. When Managed Instances goes live there will be even less reason to use SQL Server in an IaaS fashion or to keep infrastructure on-premise. You also didn’t mention that with SQL PaaS offerings you’re always on the latest SQL Server bits (if you choose to be).
OTOH, I’ll be the first to admit that automatic tuning won’t help much with poorly written queries or poor architecture. Thank God we now have the Query Store to help with some of that.
I see the future of the average DBA morphing into an Architect role. But I’m not sure many DBAs will be able to make this transition very well, at least not the DBAs I’ve ever worked with.
Randy – good news!
Nope – not only do I LOOOOOVE PaaS, we use it internally. Everything we’re building internally these days is 100% PaaS. We don’t manage a single database ourselves. I’m a huge, huge fan of PaaS for new builds.
However, it’s not Azure – for our use cases, the Microsoft stack wasn’t a good fit. More on that in 2018 – which coincidentally is why most of our tools don’t work well on Azure SQL DB. We just don’t have clients using it, but we do get clients on other clouds (both PaaS and IaaS.)
WOW! That’s something that’s simply impossible with boxed SQL Server products, right? Oh, no, wait, that’s the same way lots of companies work today on-premises. For example, one of my DBA jobs was at a $7B company with hundreds of SQL Servers (not databases – SERVERS) that had never had a DBA before. And brace yourself: that wasn’t yesterday. It was quite a while back.
I don’t think that’s quite true – I think it’s going to be 5-10 years before there’s no reasons to run IaaS. There’s still a lot of companies that refuse to move to the cloud due to privacy & security reasons. But I’m totally down with working with both PaaS and IaaS.
And that is where you need to do YOUR homework. Every year, I work with hundreds of DBAs during my consulting & training projects. These are folks who love performance tuning and can’t wait to help tune queries, indexes, and server settings in order to save their companies money. While Microsoft is indeed leveraging machine learning, that still doesn’t help with poorly written queries – and if you think those are going away, again, you need to do that homework.
All this “DBA is dead” talk is starting to sound eeriely familiar….. tape, hard disk, on prem computing, etc, etc 😉
Randy – also, you gotta recognize the hilarious irony in complaining that our tools don’t support Azure SQL DB.
If Azure SQL DB really doesn’t need a DBA…
what do you need our tools for, eh?
Brent,
Are most of your clients using RDS at AWS or are they using EC2 with SQL Server? I ask because I’m moving my environment over to AWS and I’m torn between having more control with EC2 and more automation with RDS. Is there a happy medium?
Dave – like with all architecture plans, it all starts with your RPO and RTO goal for HA, DR, corruption, and oops queries. Grab the HA/DR planning worksheet in our First Responder Kit. With RDS, you’re on sync mirroring and just one mirror, which often isn’t enough for ambitious RPO/RTO goals.
Having been working on using SQL2017 and AzureDB I’ll say AzureDB is super cool for development but it’s still lacking in a lot of core functional areas for production.
That said it is great being able to work with offsite developers on a common reference model and have it updated in real time.
The new SSIS makes it a hell of a lot easer to connect to too.
What’s unpredictable is when something doesn’t work. It certainly gets fixed first in Azure.
There’s also a whole lot of people out there with vendor apps that just don’t work on the PaaS options and where the vendors aren’t prioritising it.
The azure managed instances may help this, but you’re still reliant on the vendor supporting their apps in a PaaS environment, and at the very least that means them re-testing their entire applications on the new platform.
Some of the larger ERP systems just can’t be ripped out and replaced with something new without £xxx,xxx’s worth of investment.
IaaS SQL Server will still be with us for a while
What I’d like to see is AWS solve HA/DR for Web Edition on RDS, that or have “SQL Server” Compatible Aurora.
We can’t currently afford to roll out multiple Standard Editions instances for HA, Reporting etc but also don’t have the desire or again the budget to migrate our data storage to Postgres or Aurora.
I hope see BAG for AWS RDS, i can’t managed mirroring standard edition over the whole next year !
Alexandre – the whole idea behind PaaS is that you don’t manage it, period. So you’re not managing it today either. 😉
But on a more serious note – what do you expect to be different with BAGs as opposed to mirroring? (Just curious.) You know that for automatic failover, the only option is synchronous – it’s not like they’re going to let you use async BAGs and still do failover.
Brent, i’m facing performance problems with sync multi a-z aws rds and the workload is 90% of writes. You absolutely right bags is sync too thus that is not a good solution maybe the best could be moving for geo replication at Azure SQL.
Alexandre – exactly, SQL Server probably isn’t the right cloud product to tackle that pain point. Definitely time to consider alternative solutions.
Hi, I am coming to this a bit late, but it seems the best place to post this 🙂
I am trying to do some analysis on the costing of PAAS vs IAAS vs On Prem.
Obviously the costings have a lot of complexity and I would like a way to translate the SQL DB DTU to real infrastructure.
Does anybody know a good way to measure this?
David – sure, start here: https://sqlperformance.com/2017/03/azure/what-the-heck-is-a-dtu
Thanks very much, that is exactly what I am looking for. I was aware of the calculator but wasn’t sure if it was a realistic figure as it was not owned by Microsoft. Are there any real world figures to back this up?
David – since those would require companies sharing their performance data, what might you suppose that answer would be?
I don’t think the performance stats are particularly sensitive :-). I’ll give it a bash myself using my msdn azure credits, but I guess I’ll burn through it pretty quickly 😉
Thanks again
Hi Brent, AWS RDS has come along away over the last 6 months. ASG, Link sever, CDC are supported now, and I am suspecting alot more to follow this year(2019). We moved from a SQL in a box type to RDS and I can not express the amount of management it took away from us. We have loads of time now doing other things. Not that the Production DBA has gone a way. But the role turned more in to an advisory one.
CVR – yeah, would be nice to see something a lot more specific than “I am suspecting a lot more to follow” though, heh. A roadmap would be nice.