Microsoft Azure

In Azure SQL DB, what does “The connection is broken and recovery is not possible” mean?

Last Updated March 1, 2019

All it really means is, “Click Execute again, and you will be fine.” If I had a dollar for every time I saw this error message in SQL Server Management Studio: The connection is broken and recovery is not possible. The error says: The connection is broken and recovery is not possible. The connection is…

There’s a bottleneck in Azure SQL DB storage throughput.

Last Updated February 26, 2019

Brent Ozar

Azure SQL DB

As you pay more for Business Critical Azure SQL DB servers, they’re supposed to get more storage throughput. The documentation on this is kinda hard to read, but boiling it down, for every core you add to a Gen5 server, you’re supposed to get 2,500 IOPs. That should scale linearly: insert speed should go up evenly with…

What does Azure SQL DB Automatic Index Tuning actually do, and when?

Last Updated February 18, 2019

Brent Ozar

Azure SQL DB, Indexing

Azure SQL DB’s Automatic Tuning will create and drop indexes based on your workloads. It’s easy to enable – just go into your database in the Azure portal, Automatic Tuning, and then turn “on” for create and drop index: ENGAGE Let’s track what it does, and when. I set up Kendra Little‘s DDL trigger to log index changes, which…

How fast can a $21,468/mo Azure SQL DB load data? (Updated)

Last Updated March 19, 2019

Brent Ozar

Microsoft Azure

Update March 19: Microsoft has since acknowledged a hidden limit, then documented it, then raised it – but it’s still disappointingly slow. In my last post, I explored how fast a $5,436/mo Azure SQL DB Hyperscale could load data. I’d had a client who was curious about spinning up their dev environment up there to…

How fast can a $5,436/mo Azure SQL DB Hyperscale load data?

Last Updated February 12, 2019

Brent Ozar

Hyperscale

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…

How Azure SQL DB Hyperscale Works

Last Updated February 10, 2019

Brent Ozar

Managed Instances, Microsoft Azure

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 –…

What Azure SQL DB Managed Instances Don’t Support (Yet)

Last Updated June 13, 2019

Brent Ozar

Managed Instances

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: Support for other collations – sounds like right now…

Azure SQL DB is Slow: Do I Need to Buy More DTUs?

Last Updated November 30, 2018

Brent Ozar

Microsoft Azure

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…

Azure SQL Managed Instances and Database Corruption

Last Updated March 18, 2018

Brent Ozar

Backup and Recovery, Managed Instances

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” –…

Azure SQL DB Managed Instances: Transactional Replication

Last Updated March 20, 2018

Tara Kizer

Managed Instances, Replication

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…

Azure SQL Managed Instances Brain Dump on CPU, Memory, HA/DR, and Backups

Last Updated February 27, 2019

Brent Ozar

Managed Instances

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…

How to Restore a SQL Server Database into Azure SQL DB Managed Instances Redux

Last Updated March 16, 2018

Erik Darling

Managed Instances, Microsoft Azure, SQL Server

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. There’s nothing about…

Azure SQL DB Managed Instances: New Wait Stats

Last Updated March 16, 2018

Erik Darling

Managed Instances, Microsoft Azure, SQL Server

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…

Azure SQL DB Managed Instances: We’re All GUIDs

Last Updated March 18, 2018

Erik Darling

Managed Instances, SQL Server

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. Transact-SQL CREATE DATABASE YourMom; SELECT * FROM sys.master_files WHERE name = 'YourMom'; 12345 CREATE DATABASE YourMom; SELECT…

Azure SQL DB Managed Instances: Trace Flags, Ahoy!

Last Updated March 16, 2018

Erik Darling

Managed Instances, SQL Server

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…” Then you hop on a managed instance (where you’re not allowed to turn Trace Flags on or off), and you see what they have turned on.…

How to Restore a SQL Server Database into Azure SQL DB Managed Instances

Last Updated March 16, 2018

Brent Ozar

Backup and Recovery, Managed Instances, Microsoft Azure

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: Set up a container in the cloud to hold our backups In…

How to Deploy Your First Azure SQL DB Managed Instance Preview

Last Updated March 16, 2018

Brent Ozar

Managed Instances, Microsoft Azure

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…

How to Back Up SQL Server to Azure Blob Storage

Last Updated March 12, 2018

Brent Ozar

Microsoft Azure

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: Update March 12 – if you want a better end result than what I get…

#PASSsummit Day 2 Keynote: Dr. Rimma Nehme on Azure Cosmos DB

Last Updated November 2, 2017

Brent Ozar

#SQLPass, Microsoft Azure

Dr. Rimma Nehme Summit day 2 keynotes have become special. Over the last few years, Microsoft has dedicated the day 2 keynote to a technical dive into an advanced, future-looking topic. Past examples have included future-looking guidance on Hekaton, columnstore indexes, and how Azure SQL DB protects data. I love Microsoft for doing this. It costs…

An Expensive Reason To Avoid AGs In Azure

Last Updated August 27, 2017

Erik Darling

Always On Availability Groups, Backup and Recovery, CHECKDB and Corruption, Cloud Computing, High Availability, Microsoft Azure, SQL Server

Cash Rules Most people, when they get through paying for Azure, and SQL Server Enterprise Licensing, are left with a hole in their wallet that could only be filled with something that says “Bugatti”, and has a speedometer with an infinity sign at the end. Recently, while working with a client, I found out that…