A client asked, "How quickly could we spin up a full copy of our database in the new Azure SQL DB Hyperscale?" Their database size wasn't too far off from the 340GB Stack Overflow database, so I decided to migrate that to Hyperscale to see how the experience went. Hyperscale is Microsoft's intriguing competitor to Amazon Aurora. Hyperscale…
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 -…
Whenever a new product or feature comes out, I like keeping an eye on its support forums to understand the challenges users are facing.
I went through Azure SQL DB's Managed Instance feedback forum, and here are some of the interesting issues that have been raised:
You've got an Azure SQL DB, and your queries are going slow. You're wondering, "Am I hitting the performance limits? Is Microsoft throttling my queries?"
There's an easy way to check: run sp_BlitzFirst. sp_BlitzFirst is our free performance health check stored procedure that analyzes a lot of common performance issues and then gives you a prioritized list of reasons why your server might be slow right now.
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:
In our behind-the-scenes posts about building SQL ConstantCare®, I've written about how we picked serverless architecture for the application layer, and how we picked AWS Aurora for the database layer. Hosting costs were a big driving factor in those decisions - so how has that worked out?
Presented for your enjoyment, here are our total SQL ConstantCare® hosting costs (including both production and development environments) from AWS Billing Explorer for a rolling 30-day period as of when I'm writing this post:
Barely Metal
In Ye Olde Olden Dayyes, companies would outline hardware budgets and refresh cycles.
Every 3-5 years, they'd lick a thumb and hold it up to the CPU fans. If they felt a soft breeze, they'd buy the same number of CPUs. If they felt a hard breeze, they'd buy a few more. This made the hardware buy more expensive, and of course drove up licensing costs.
I just updated PasteThePlan, and the change you'll notice first is that we're using the latest version of Justin Pealing's html-query-plan. It adds missing index hints, yellow bangs for warnings like implicit conversions and no join predicate, and more. Check out this example plan:
Plus variable line widths for different amounts of data:
Disclaimer: I love Azure SQL Managed Instances (MIs) so far. They're really cool, and I'm having a fun time playing with them. I'm about to describe a bug, and I'm sure this bug will be fixed soon, so it shouldn't stop you from previewing Managed Instances. This post is not "MIs are t3h suxxors" -…
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.
Normally when we write blog posts, we try to explain something or tell a story. If you're looking for a solid educational post, stop here, mark this one as read, and go on about your day.
This post is just a brain dump of unorganized notes from our experimenting with Azure SQL DB Managed Instances Preview. Buckle up.
Anything Brent Can Do
There's some things you can do with Managed Instances, and some ways you can look at data that you can't do elsewhere.
For instance, you have the ability to start an restore ASYNC -- that means if your session drops, the restore will continue headlessly in the background.
Incidental
This is a long list that I haven't had a change to dig through yet -- all I did was compare them to which waits were occurring on my 2017 CU4 instance.
There are about 174 of them that I found, though some may just be generated by Hekaton that I don't have set up on my home servers.
Six is having problems adjusting to his clone status
Some funny things happen when you create databases up in a Managed Instance.
For Instance (HEH!), you may expect this to yield some fruitful results, but it Manages (HAH!) to defy logic.
[crayon-6a3799707f28a813871156/]
But we get blank results! Mon Dieu!
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've built your first Managed Instance, and now you wanna play around with real live data. You're going to need a backup in the cloud to do it - SSMS doesn't have a drag-and-drop option.
Big picture, here's what we're going to do:
The Azure SQL DB Managed Instance public preview is open, although it may take a week or two for new applications to get their new VMs. To start your application process now, go into your Azure portal and Create an Azure SQL Managed Instance. There are preview terms at the top - fill out the form to accept that now, because they're going through a big queue, and you're not my first reader. (You're still my favorite, though.)
When you're planning for disaster recovery, offsite backups in the cloud are an attractive option. SQL Server Management Studio makes it easy to back up to the cloud inside the GUI, and you can learn how in just 90 seconds:
https://www.youtube.com/watch?v=jpZAnqHw7L8
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.
There's a lot that goes into a Disaster Recovery plan. One of them is offsite backups. There are some businesses that don't have backups going offsite, let alone a Disaster Recovery plan.
This conversation has taken place more than I thought it would.
