There are many database technologies available on Azure that you can choose from. Why would you use Azure SQL? There are so many new solutions, some very specialized and super cool, so you may be wondering why I’m telling you to use Azure SQL in your next project. Well, after I joined the Azure SQL PM team, I discovered that there are many, really, many features that developers don’t know they exists…and as a result Azure SQL is still used like its development and evolution stopped 20 years ago…when Azure SQL was not yet there, and only its on-prem brother, SQL Server, existed. Well, really a lot of improvements have been done, really making Azure SQL definitely a post-relational, cloud native, database. Let me show you why, using just 10 simple points. I’ll go over each point in detail in the next blog posts.
Batteries Included
Azure SQL is the relational and post-relational database that I’d like to say has “batteries included”. I borrowed that term from Python, one of my preferred languages, and that’s also the main reason why it is a great database for developers. A lot of features that you would normally find in different, more specialized, products are nicely integrated into just one. Using different specialized products can bring great advantages but also poses a huge challenge: data must be moved around all those products and the burden of keeping consistency is on the developer’s shoulders. And it is a big one, one that will increase application complexity by an order or magnitude. And as a developer I just prefer to follow the KISS principle. Azure SQL allows me to focus only on what it’s unique to my company and application, delegating all the responsibilities related to data to something specialized on that. That’s a big win for everyone.
Post-Relational features for the modern apps
JSON is what developers love and breath, so native support to JSON is a must for a modern database. Azure SQL provides pretty good support to generate, consume, manipulate and query JSON. But that’s not enough. Graph support, via node and edge types, transitive closure and the ability to navigate arbitrarily deep relationships using a very concise (similar to the well-known “Cypher”) is available in Azure SQL right away. Also full Open Geospatial Consortium (OGC) support is provided, for modern, location aware, applications. This makes it easy to store geospatial data and query data for closest locations, find all locations within a certain perimeter (perfect for geofencing) or, more in general, perform complex geospatial manipulations. All these features are available to be used right when you need it and you can rest assured that you’ll get all support and benefits from the existing post/relational engine that will optimize your graph, spatial and JSON query too.
Complete storage control and abstraction with Columnstore and Rowstore
With Azure SQL you can decide if a table should be saved using a row-by-row technique or a column-by-column one. Or a mix of the two. And this is really a killer feature as it allows the creation of solutions that can quickly access a single row or can easily read, aggregate and analyze hundreds of millions of rows.
Ultra-high concurrency with Lock-Free Memory-Optimized tables
Memory-Optimized tables use a sophisticated lock-free mechanism, known as Multiversion Concurrency Control (MVCC) to guarantee extremely high concurrency and still provide complete transactional support. Extremely high means hundreds of thousands of transactions per seconds, and more.
Time Traveling with “As-Of” queries
Azure SQL gives you the ability to automatically track and execute “as of” queries by enabling Temporal Tables. All changes to a table will be transparently tracked and made available via the “History” table. Again, completely transparent to applications and so useful for auditing, reporting and business analytics.
Encryption, Masking and Row-Level Security
Azure SQL provides several levels of encryption, from a column to the entire database, just right out of the box. Always Encrypted makes sure your data is encrypted while in-flight to and from your application. Row Level Security allows you to set who can see certain rows and who can’t, and Data Masking allows you to expose some part of your data while keeping privacy intact.
Blocking and Non-Blocking Transactions
With Azure SQL you can decide if you want to use locks to make sure your data is protected by other, concurrent modification, or use row-versioning to still get transactional consistency but without having to worry too much about locks. By default in Azure SQL you will be able to read data while it is being written without having to compromise with scalability or consistency (Read Committed Snapshot Isolation Level is set to “on” by default).
Say what you want, Intelligent Optimizer will take care of the rest
SQL is a declarative language: you will tell the engine what you want instead of what you want it to do to get things done. The query optimizer will figure out what is the best way to return the data you asked, by generating an Execution Plan (aka DAG in NoSQL world) and executing it. It will take into account data size, data distribution, index availability, memory, concurrency and time constraints and it will generate an execution plan that likely is the best possible given all aforementioned constraints. All this is possible as in the query optimizer there are 40+ years of research and 25+ of use-cases optimization, learned from the field, working hand-to-hand with customers. Something that cannot be found in any other database, at the moment.
Complete Monitorability and Full Insights
You’ve done your work, and everything works perfectly. Until it doesn’t. How to understand what to fix or improve? Azure SQL provides complete transparency on its inner workings. You can go from high level monitoring down to analyzing that a single task is doing. All via Dynamic Management Views and Extended Events and other tools that come included in the product.
Wide range of prices and elasticity
Azure SQL comes in a lot of different flavor and options. Same engine, and the same features, but a variety of Service Level Objectives (SLO) allow you to choose the best price/performance balance for your app. Extremely low latencies, incredible scalability or aggressive savings are on the table for you to choose. And moving from one to another is easy and can be dove via code, so you can use a performance sensitive SLO during working hours and move to a more cost-aware when less stress is expected on the database. Or you can automatically scale up and down using the Serverless option. Databases can also be pooled together to balance costs and resource usage in multi-tenant scenarios with Elastic Pools. Whatever you need to create a balanced solution, you’re covered.
Wide range of platform and deployments options
Azure SQL has drivers support for all the most common languages be it C#, Java, Python, Node or Go. With .NET the newly released Microsoft.Data.SqlClient is the way to go, open source and backward compatible with System.Data.SqlClient, but Azure SQL loves all developer equally, so look for your preferred language here “Getting Started with SQL Server” (as Azure SQL in SQL server are just different twins) to get a kickstart. And just because Azure SQL in SQL server are just different twins, everything you will use on Azure SQL it will be easily usable on SQL Server which runs on Windows, but also on Linux and even on Edge devices. A database that spans from the cloud to the edge. That’s pretty cool, isn’t it?
Hey Davide! Nice post. Do you mind if I use this in one of my presentations? With all the credits of course.
Please do! 🙂
I am trying to understand where CosmosDB is positioned in relation to AzureSQL.
What should be the way forward?
CosmosDB is on the same arena of those “NewSQL” dbs that have some kind of special algorithm like FaunaDB, CockroachDB, YugabyteDB (Calvin and Spanner respectively).
CosmosDB is a NoSQL database, while AzureSQL is a (post-)relational database. The way forward completely depends on the use case and/or the knowledge your team has already acquired. I'm sorry I cannot be more specific than this, but there really isn't a one "definitive" answer. It would be answering to something like "Which is better Windows or Linux?" (And we love them both!). The only possible answer is another series of questions: "for doing what?...
Or you can use Oracle Cloud Free Tier and have access to the same database features and more for free (Autonomous database included) and later if you need more upgrade, this way the development is free 🙂
2 VMs free plus balancer and many other stuff is free, I have been using it for testing and development for over 6 months and it is way cheaper (1 USD in total)
Azure SQL offers 12 month free, and it also offer the Basic tier that is priced at 0.01$ per hour or less than 5$ per month. But thanks for bringing this to our attention!
What a pity that at this time, even with a starter student account you cannot use all the free resources made available 🙁
I hate to tell you but there is NOTHING in this post that is Azure SQL specific – every single item also applies to SQL Server. There are pretty good reasons to use SQL Server instead of Azure during development (including more access to the load generated and system level events to see issues with overloading). And while I like Azure SQL – you should actually name reasons.
Correct (if we set aside Hyperscale, which doesn't exist on-premises). Point is, who never used SQL Server may not know that Azure SQL is SQL Server, and thus may not be aware of those amazing features. All the new generations of developers may even never use SQL Server at all, as they may only work on cloud-born applications. So making sure that we tell loud and clear what Azure SQL can do, without assuming any...
be careful with using azure sql.
specially during coding or development.
every drop/create database costed me 25$, which for less than a week ballooned to 600$.
there should be a warning or notification if too many drop/create operation is being done on azure sql.
There is not cost in creating/dropping databases, but costs are billed per hour (or per second if using the Serverless offer). To reach 600$ in a week, you must have created a lot of the highest SKU (P15 or v80) databases. Also make sure to use a Dev/Test subscription so that prices will be the lowest possible (as you’ll only pay for the hardware, not for any licensing costs).