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.
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.
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-6abb6d2a9e1b9470474512/]
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:
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
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.
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?"
Background: I'm working with kCura to build a Faux PaaS: something akin to Microsoft's Azure SQL DB, but internally managed. In Part 1 of this series, I talked about why we're building it, and today, I'll talk about where.
The database team rarely gets to pick the cloud vendor.
Listen, I can explain. Really.
We had a client with a 5GB database, and they wanted it to be highly available. The data powered their web site, and that site needed to be up and running in short order even if they lost the server - or an entire data center - or a region of servers.
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…
In the on-premises, boxed-product version of SQL Server, you can build your own CLR assemblies and call them from your T-SQL code.
For years, this feature was missing from Azure SQL DB - Microsoft's platform-as-a-service database offering - and users voted that they wanted it.
Enter Query Store Query Store, in short, is a way to track query performance over time. In Microsoft's words, "The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review". It's like sys.dm_exec_query_stats but it persists across reboots! And it has execution plans! The Query Store is a…
In Azure SQL Database, no one can hear you scream query common system objects that tell you when a restart happened. You don't get the access you need to sys.dm_os_sys_info, sys.dm_exec_sessions, sys.traces, or sys.databases. The closest I've been able to get is to query sys.dm_os_wait_stats for several common wait types that seem to correspond with…
Your users probably shouldn't be able to view all of the data.
You might have regional sales managers who should only see sales for their region, or human resource staff who should be able to see employee details but not salaries.
Microsoft just announced a new round of D-grade VMs that have 60% faster CPU and local SSD than can go up to 7,000 IOPS in a canned IOmeter test. Before jumping to conclusions or, even worse, picking a cloud provider, it's best to look at these numbers critically. CPU Speeds The new CPU is being…
