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.
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?
Author: Davide Mauri, Senior Program Manager, Azure SQL
- Hiring: Azure Software Engineer - January 12, 2023
- 10 reasons to use Azure SQL in your next analytics project - November 3, 2020
- A Developer’s Guide to Building AI Application - September 4, 2020