SQL Server in cloud VMs, hybrid systems, and cloud infrastructure.
22 associated posts22 primary posts
For T-SQL Tuesday #199, Koen Verbeeck posed an excellent question: if your company moved up to the cloud, but after migrating, had to come back on-premises, what would be the big problems?
I've had clients in this exact situation! Here are some of my favorite gotchas from those experiences.
I'm finally back in the home office after running around Europe for a month. Let's talk through your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=Jl-QQEFkBVg
Let's say you built a web app and it became globally popular. You've got users scattered across North America, Europe, and Asia.
In theory, you could shard your data globally: build 3 sets of SQL Servers, one on each continent, and split up their data.
Lemme start this off by saying this is probably irrelevant to you. (It's irrelevant to me, too.)
If you're strapped for time, just skip past this blog post.
For the last twenty years, SQL Server performance tuning has relied on a few facts: SQL Server provided a lot of performance metrics with thorough documentation from both Microsoft and the community. You could fetch those metrics in a variety of ways, like Perfmon counters and dynamic management views. Many of the metrics were cumulative…
It sounds like a trick question, but I'm serious.
If your company's management is just now starting to consider the cloud in 2020, your reaction shouldn't be to cast a shadow on cloud vendors. Instead, think of it as writing your own resume: what the capabilities that you and your team are really proud of?
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 -…
When you start running SQL Server in cloud VMs - whether it's Amazon EC2, Google Compute Engine, or Microsoft Azure VMs - there are a few things you need to treat differently than on-premises virtual machines.
Fast shared storage is really expensive - and still slow. If you're used to fancypants flash storage on-premises, you're going to be bitterly disappointed by the "fast" storage in the cloud. Take Azure "Premium" Storage:
Our new SQL Constant Care® is an online service that analyzes your SQL Server's diagnostic data, then gives you personalized advice on what you should do to make your database app faster and more reliable. I blogged about the vision last week, and today let's talk about the things that drove our architecture decisions way back in 2015.
I knew Brent and Erik wouldn't touch replication, so I figured I'd give it a whirl.
My good, old friend replication
I have a love-hate relationship with replication. Mostly hate due to latency and errors, but it does serve its purpose. Before Availability Groups came out, I used Transactional Replication to copy data from the production OLTP database to another server so that we could offload reports.
When you talk to people from Microsoft
They're all "aw shucks" about Trace Flags.
"Don't really need'em!"
"Eh, haven't used one in years."
"Sometimes they're good for troubleshooting, but..."
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.
Background: I'm working with kCura to build a Faux PaaS: something akin to Microsoft's Azure SQL DB, but internally managed. You can catch up with what we've discussed so far in Part 1 and Part 2 of the series.
In the last post, I talked about measuring backup and restore throughputs across different instance types, regions, storage configs, and backup locations. It's a lot of work to answer questions like "How should we configure our new SQL Server VMs?"
In the cloud, treat your servers like cattle, not like pets.
In the cloud, systems administration is very different than the on-premises stuff you're used to. When you build VMs in the cloud with Infrastructure-as-a-Service (IaaS, meaning AWS EC2, GCE, or Azure VMs), you expect them to die. It's just a matter of time. If you're lucky, it'll be years from now, but if you're unlucky, it'll be tomorrow.
So you're hosting your SQL Server in the cloud - say Amazon EC2, Azure VM, or Google Compute Engine - and you've noticed that when you're running a backup or a DBCC CHECKDB, you suffer from extreme performance problems.
Queries run slow, and even worse, applications report timeout errors even just trying to connect to SQL Server. More symptoms can include database mirroring and cluster failovers. What's going on?
If you've ever heard the Away From the Keyboard, a podcast that I co-host with Cecil Phillip, you'll quickly discover that I love hearing stories about how products are created. Now I get to tell you the story of how Paste The Plan became real and the architecture around it. In a world... Here at Brent Ozar…
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.
The cloud is robust and reliable. The cloud solves all of our scaling needs. The cloud makes my poop smell like roses. While all of these statements are theoretically true it takes some effort to make them true in reality, especially when a database is involved. Who Is Deploying SQL Server in EC2? A question…
Most SQL Server DBAs don't even like MySQL, let alone NoSQL. They cringe when they hear people say things like, "I don't need transactions" or "The data will eventually be consistent" or "Indexes will be supported in the next version." SQL Server and Oracle people are constantly trying to out-feature each other, and the abject lack of features in the NoSQL world comes as a complete shock to both parties.
Erika's friends call her "Fancy Feast" because she only likes the best things in life. She's got champagne tastes, but we can't afford to buy the best of everything. I recently picked up a coffee table from Ikea, and it got me thinking about SQL Azure. In a perfect world, I'd have on-premise SQL Server for every database. I'd use cutting-edge storage and cluster everything. But this isn't a perfect world...
