Azure SQL DB Frequently Asked Questions

Azure SQL DB
20 Comments
Brent Reading Book

I’ve seen your search history.

Let’s get you some answers.

Is Azure SQL DB PaaS, IaaS, or SaaS?

Azure SQL DB is platform-as-a-service (PaaS). Microsoft manages most common database tasks for you, and they act as your DBA.

This also means that like a DBA, Microsoft will tell you “no” if you try to implement something that they don’t support. There’s no negotiations: the answer is just no.

Who is Azure SQL DB for?

Azure SQL DB is a good fit for new applications.

It’s a great fit for software-as-a-service companies and independent software vendors (ISVs) who want to put each client in their own database. Azure SQL DB gives you performance and cost controls because you can tune how much resources each database gets, or put them into pools of resources that are shared across lots of databases.

SaaS and ISV companies don’t usually have full time database administrators, so they like that Microsoft handles the basic care and feeding of Azure SQL DB. There’s no dealing with patching, clustering, backups, and recovery – Microsoft manages all that for you.

Who is Azure SQL DB NOT for?

It’s not a good fit for companies with existing applications that do cross-database queries. If your app is scattered across multiple databases, and you’re used to doing joins across them, you’re going to be frustrated with Azure SQL DB. It’s technically possible, but it’s a giant pain in the rear, especially for established databases with hundreds or thousands of tables in each database.

It’s also not a good fit for companies with a lot of SQL Servers that already talk to each other doing things like linked servers, replication, and Agent jobs that fire off tasks on different servers at different times.

If you fall into the above categories, that’s where Azure SQL DB Managed Instances come in.

Compare Azure SQL DB versus Managed Instances.

Azure SQL DB is for one standalone database, versus Managed Instances which are more like one standalone SQL Server with multiple databases on it.

If you’re used to managing SQL Server, you’ll probably prefer Managed Instances: they have things like cross-database queries and Agent jobs. Databases fail over together, too. It’s basically like IaaS Availability Groups in the cloud, managed by Microsoft for you. Make no mistake, though: they’re still PaaS, and they’re priced accordingly.

Compare Azure SQL DB versus Synapse Analytics.

Azure SQL DB is mostly for transactional systems like web sites. Synapse Analytics is for data warehouses.

In a transactional system, queries deal with a few rows at a time, and they complain when queries take more than a second or two to complete.

In a data warehouse, people queries deal with millions of rows at a time, and their complaints tend to start when reports take more than 10-30 seconds to finish. They’re used to taking a longer time because of the size of data they’re dealing with.

Could you run a data warehouse in Azure SQL DB? Sure, but…it’s going to be expensive due to Azure SQL DB’s throughput limitations. It’s much easier (and more cost effective) to load large amounts of data into Synapse Analytics. Just don’t expect Synapse Analytics to handle a thousand queries at a time each with millisecond-level response times: it’s not designed for that.

Is there a free tier for Azure SQL DB?

No. The cheapest Azure SQL DB is the Basic tier at $5 per month, but it only gets 5 database throughput units (DTUs). This is effectively useless as even the simplest queries will take seconds to complete.

This is frustrating for shops that are used to using the free SQL Server Developer Edition for their development work, and hosting that development server centrally for multiple developers to work on at the same time. That’s okay: the better approach is to have every developer use their own local development database, like in a Docker container, and –

What’s that you say? Your development laptops aren’t large enough to host your full database size? Well, no, of course not, but you’re not supposed to be developing with production data. You’re supposed to be using an empty database for your proof-of-concept work, and then testing with a manufactured set of similar-to-production data.

What’s that? I’m afraid you’re going to have to speak up – your monitor has a really crappy microphone. Oh, I hear you – you’re asking about your QA, load test, and user acceptance test environments, which use databases restored from production. Well, uh, I don’t have good answers there: there’s no free Developer Edition for Azure SQL DB, and you can’t restore your Azure SQL DB databases down to a regular SQL Server. There are some kludgy hacks around exporting all your data, but those are performance-intensive and slow. Let’s just ignore this business problem and move on for now.

What about Azure SQL DB Edge?

The Azure SQL Edge page says it simplifies application development, but that’s not really what it’s for. It’s an innovative new way for Microsoft to extract more licensing fees by getting you to host databases in more places.

Wait, I don’t think I said that quite right. I mean when you have important data that lives in remote places, like in sensors or frequently-disconnected devices, Azure SQL Edge lets you have all of the power and complexity of a database server there, without the ease of management or cost-effectiveness of a centralized…

Hold on, I don’t think that was quite it either. Lemme get back to you on that one.

Why is my Azure SQL DB slow?

Most of the time when I work with clients, it’s because they criminally under-sized the server.

Azure SQL DB can be provisioned in very, very small hardware sizes for hobbyists or development purposes, and it’s very inexpensive. However, I find that folks provision it in those inexpensive tiers, and then they’re totally disappointed by the performance. You’re not hitting Azure SQL DB architectural limitations – you just provisioned it too small.

To find out if you’re hitting your account’s limitations, install and run sp_BlitzFirst from my free First Responder Kit. It’ll tell you if you’re hitting your Database Throughput Unit limits:

Azure SQL DB is maxing out

If you’re hitting your limitations on CPU, throughput, memory usage, etc, then you have two simple choices:

  • Turn up your size to a higher number, or
  • Tune your queries & indexes

If you’re spending less than $500 per month on Azure SQL DB, it probably just makes sense to turn the size up. If you’re spending more than that, it’s probably time to attend a performance training class or hire me for performance tuning help.

How can I learn about Azure SQL DB for free?

If you prefer to learn via live online events, Microsoft’s running a free Azure SQL Digital Event on May 4.

If you like self-learning – reading through material and running demos yourself – check out Microsoft’s SQLWorkshops Github repo. Scroll down through the readme, and there’s a list of courses. Each course is very well thought-out with tons of resources and demos to run.

If you prefer books, get Azure SQL Revealed by Microsoft’s Bob Ward. It’s fairly new, so it’s still relevant – which can be tough to find in the constantly changing churn of the cloud.

Previous Post
Your Top 10 Ideas for SQL Server
Next Post
Free TempDB Training Class Next Monday

20 Comments. Leave new

  • Drew Furgiuele
    April 15, 2021 1:23 pm

    I wonder if the Edge database would work in a big open area… like space?

    Reply
  • Henrik Staun Poulsen
    April 15, 2021 2:11 pm

    I’ve used the “Elastic pool” option in Azure SQL DB, when I wanted developers have their own database. Then they share the CPU (and RAM). Storage is not (too) expensive, so that was not a problem.

    Reply
  • Mark Freeman
    April 15, 2021 4:26 pm

    There is apparently a way to declare a subscription as Dev/Test and Microsoft will charge much less for the Azure SQL Databases there, in exchange to a commitment to not run production loads on them. This may only be available to Enterprise Customers. Finding information about this online is challenging.

    Reply
    • Right, exactly – ANYTHING is available to Enterprise Customers. It’s all about how much you spend, and how good your negotiation skills are. You can get a pony if that’s what you’re into.

      Reply
  • Does First Responders kit run in Azure SQL? I tried running before and got some errors.

    Reply
    • Naveen – there are days when I wonder why I even bother writing a single line of documentation.

      Reply
    • Azure SQL DB – not supported. Some of the procedures work, but some don’t, and Microsoft has a tendency to change DMVs in Azure without warning, so we don’t put any effort into supporting it. If it works, great! If not, any changes to make it work would be on you. See the contributing.md file for how to do that.

      Reply
  • Well said, Brent. Here’s a little haiku for you:

    Microsoft Azure
    Such easy sprawl, very high expense!
    Cha-ching, Micro$oft

    Reply
  • Kevin Martin
    April 16, 2021 3:06 am

    Can confirm! Brent you are spot on as usual. I worked with all the Azure solutions mentioned except Azure SQL DB Edge. I thought I was going to get the chance to use it for an IoT solution for a medical device manufacture but it was for a pre-trial so it was not a full solution. 🙁

    Reply
  • Francesco Mantovani
    April 16, 2021 8:02 am

    I followed your 1h video on IDERA last month about Azure. Regarding Cross-Database Transactions you showed a slide where you divided the non-supported transactions in:

    1) Cross-db queries (within the same db)
    2) Cross-db transactions (between two databases on the same server)
    3) Cross-server transactions (between two servers)

    While I understand the number 2 and 3 I don’t get the number 1. What is a Cross-db queries (within the same db)? Because to me it looks like a normal query and I don’t understand why it won’t be supported in Azure SQL Database.

    Reply
  • Francesco Mantovani
    April 16, 2021 11:37 am

    You are right, I was wrong.
    Let me just rephrase that so people don’t get confused:

    1) Cross-db queries (between two databases on the same server)
    2) Cross-db transactions (between two databases on the same server)
    3) Cross-server transactions (between two servers)

    I put the video in case people want to have a look:

    https://www.idera.com/resourcecentral/webcasts/geeksync/sql-server-to-azure-migration/thankyou

    Reply
  • I got the one below last year it prepare for the DP300 exam. I found it to be insightful and it’s also free of cost

    https://azure.microsoft.com/en-us/resources/professional-azure-sql-managed-database-administration/

    Reply
  • Alex Friedman
    April 18, 2021 1:22 pm

    What, you didn’t hear me shouting at my screen? Tsk.

    Reply
  • Hi Brent ,
    did you find any way to by pass I/O limitation for Azure ?
    https://www.brentozar.com/archive/2019/02/theres-a-bottleneck-in-azure-sql-db-storage-throughput/

    and also to customize “file grow rate” for Azure SQL database ?
    Thank you
    Alex

    Reply
  • Richard Armstrong-Finnerty
    May 15, 2021 10:06 pm

    Talking of Azure and $$$: it’s worth knowing that if you delete an Azure product you keep getting billed for it until it’s completely deleted. If learning in Azure, where budget might be a constraint, this can add a “Where did that come from?” cost, especially if deleting Managed Instances (and, I guess, other SKUs), as they can take quite a while to get deleted.

    Seems a bit naughty to be charging for something that, pretty much instantaneously, becomes unusable.

    Reply
    • Hi Richard , do you have any proof (link to SLA or other resources) regarding comments “you keep getting billed for it until it’s completely deleted”
      We delete customer created copies of original azure sql databases ~7 days after creation. Would be good to know if we still get billed till database can’t be restored from deleted ..

      Reply
  • Hi Brent, I saw a lot of information about the json value feature in SQL Azure.
    I have one specific question, I have a table that we write all the json values (valid and not valid) in a column and then we proccess it to populate another table.
    As I read I should use computed columns if I want to optimize my query with indexes, but because I don’t know if the json is valid or not I fear that at some point my ETL would fail (when I get a not valid json)
    You consider its better to use fulltext index in this cases? What would you propose?

    P.D: I cannot use a constraint to only get the valid json.

    Thanks you very much

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu