Azure SQL Database vs. Cosmos DB — Which Should You Choose? | by Branislav Holländer | Jun, 2022

The Conventional One vs. the Trendy One

Photograph by Tobias Fischer on Unsplash

Did you latterly begin creating or porting an software to Microsoft Azure? In that case, one of many belongings you most likely must determine upon is the database resolution you wish to use. The Microsoft cloud provides a number of choices, with the 2 hottest being Azure SQL Database (and its two siblings, Azure SQL Managed Cases and SQL Server on Azure VMs) and Azure Cosmos DB.

Lately, I used to be evaluating these two alternate options for my software. I gained some insights I’d like to share with my readers. First, allow us to take a look at each merchandise individually and see which options they provide.

Azure SQL is Microsoft’s SQL Server providing on Azure. There are three companies associated to Azure SQL to select from:

  • Azure SQL Database
  • Azure SQL Managed Occasion
  • SQL Server on Azure VMs

Azure SQL Database is essentially the most “managed” service out of the three. It’s a PaaS providing enabling you to arrange SQL Server rapidly and effortlessly. A lot of the administration duties, together with backups, updates, and monitoring are taken care of for you. You additionally don’t have to handle the underlying infrastructure.

There are a number of service tiers to select from, together with a single database possibility with configurable reminiscence and compute, an elastic pool possibility that allows you to share sources between a number of databases in addition to a serverless possibility that may scale your sources dynamically relying on use.

SQL Server on Azure VMs merely allows you to run an SQL Server occasion by yourself provisioned Azure digital machine. This implies you get virtually full management over each facet of the deployment. This additionally means you have to to care about many of the administration duties you might be used to performing in your on-premises resolution.

Azure SQL Managed Occasion is a type of hybrid between these two. On one hand, it allows you to seamlessly switch your on-premises SQL Server database to the cloud. However, it provides you a lot benefits of the cloud, together with improved safety, automated patching, model updates, and automatic backups.

Since Azure SQL is usually appropriate with SQL Server, it additionally provides the identical transactional ensures (ACID) because the on-premises product. On account of these sturdy ensures, scaling out by including a number of server situations is usually restricted to read-only replicas. That is additionally true for the geo-replication function of Azure SQL.

Pricing for Azure SQL Database is predicated on the computing and reminiscence you require (vCores) in addition to the native storage and backup storage you configure. Total, the pricing appears simple to know.

Cosmos DB is the NoSQL providing on Azure. It’s designed from the bottom as much as assist excessive scalability, totally embracing the idea of eventual consistency. The principle goal functions are IoT and global-facing cloud functions. To this finish, Cosmos DB provides totally different consistency ranges, information entry strategies (APIs) in addition to scaling choices.

Since Cosmos DB is a NoSQL database, it usually conforms properly with how information is utilized by your software. As an illustration, an “merchandise” will be inserted into the database as a JSON doc and the output of a question is a JSON doc as properly. Databases and containers are used as namespaces over the gadgets with sure further features.

To assist excessive horizontal scaling, Cosmos DB makes use of a number of mechanisms. First, Cosmos DB partitions every container based on a partition key. These partitions are robotically unfold throughout bodily nodes. Moreover, international distribution of information is achieved by deploying your information throughout a number of Azure areas. This decreases latency and will increase responsiveness internationally. It additionally supplies a failover throughout regional outages.

Understandably, international distribution comes on the expense of consistency. In distinction to SQL Server which supplies sturdy ACID ensures by default, Cosmos DB lets you select between a number of consistency levels, every offering its ensures. Essentially the most excessive of those, eventual consistency, solely ensures that every one replicas will converge in some unspecified time in the future sooner or later.

The pricing mannequin of Cosmos DB is predicated on request models (RU). 1RU is the price of a 1KB point-read. Queries are correspondingly dearer. Request models appear easy sufficient, nevertheless it’s laborious to estimate the utilization given your explicit information entry patterns with out prior expertise.

Having described each options, chances are you’ll ask which one is best suited for you? Allow us to focus on some strengths and weaknesses of each databases.

Azure SQL Database Execs

  • Versatile deployment choices. Whether or not you need a totally managed service or in the event you merely wish to run SQL Server on Azure’s VMs, Azure SQL Database obtained you coated.
  • Aware of SQL builders. Since Azure SQL Database is actually the Azure implementation of the well-known SQL Server, most expertise immediately switch to the cloud providing.
  • Customizability. When you run your individual SQL Server occasion, you’ve got management over each setting.
  • Clear pricing. Pricing is predicated on the required reminiscence, compute, and storage sources.
  • Migration. Comparatively simple migration of your on-premises SQL Server databases to Azure SQL because of the identical underlying engine.
  • Stability and reliability. Microsoft SQL Server was initially launched again in 1989 and has come a great distance since then. It’s a mature, steady, and well-tested database engine.

Azure SQL Database Cons

  • Mounted schema. Typically, SQL databases are much less fitted to continuously altering software program necessities because of their fastened schema. Schema migration could also be laborious to handle. Though SQL Server can work with JSON data these days (thus bridging the hole between SQL and NoSQL), it requires particular syntax and feels a bit like an afterthought.
  • ACID ensures and distributed transactions. Whereas these options will be very helpful for some functions, in addition they drastically cut back scalability in case you don’t want them.
  • Restricted scaling. Whereas scaling up (by including extra reminiscence or compute to your SQL Server situations) is a fast and simple method to ramp up efficiency, it might turn into prohibitively costly past a sure level. Scaling out (by including a number of situations of SQL Server) is usually restricted to read-only replicas.

Azure Cosmos DB Execs

  • World distribution. Many corporations these days act worldwide. Their prospects anticipate the identical efficiency from functions no matter the place they’re situated. This makes the usual SQL database mannequin very inefficient. Having one write-enabled grasp node with a number of read-only replicas shouldn’t be adequate for duties resembling social community feeds or IoT information streams.
  • A number of consistency ranges. However, these functions normally don’t want the consistency ensures of the standard databases. They’ll depend on weaker consistency fashions resembling constant prefix and even eventual consistency. Having the liberty to decide on the consistency stage is a serious advantage of Cosmos DB.
  • Auto-indexing assist. Each container has an indexing coverage set to hurry up queries. By default each property is listed and vary indexes are enforced. That is adequate for many use circumstances. Nevertheless, superior indexing settings can be found.
  • Help for a number of languages out-of-the-box. There are ready-made APIs accessible for .NET, Java, Python, Ruby, and JavaScript. No want for complicated ORM layers.
  • A number of APIs. Cosmos DB supplies its personal Core API in addition to a number of different database APIs (Gremlin API, Cassandra API, MongoDB API, Desk API) that assist with migration from different NoSQL databases. This lowers the migration value and studying curve.

Azure Cosmos DB Cons

  • Tougher to know for conventional DB admins. Since Cosmos DB is a NoSQL database and doesn’t essentially present sturdy ACID ensures (relying on configuration), it’s tougher to know for DB directors used to coping with conventional SQL databases.
  • Complicated migration from SQL Server. Migration from a SQL Server-based resolution might show to be very expensive and in excessive circumstances might require rewriting main elements of the appliance. Subsequent optimization will then take up much more time. I’d solely advise doing this if the advantages of world scaling outweigh the efforts.
  • Arduous-to-understand pricing mannequin. The pricing mannequin is predicated on Request Items which can be unintuitive. The Cosmos DB Capacity Calculator within the portal might assist with value estimates offered you may estimate sure key metrics. Generally, Cosmos DB could also be fairly costly.
  • Public cloud-only. The one place the place Cosmos DB is deployed is in Microsoft’s datacenters. Presently, there isn’t a providing for hybrid or on-premises cloud options resembling Azure Arc.

Each merchandise have a spot within the Azure ecosystem. Whereas Azure SQL Database supplies simple migration out of your on-premises SQL Server resolution and lets you leverage your present SQL Server information, Cosmos DB was designed from the bottom up for international distribution and is totally managed.

If I needed to determine which database to make use of for a brand new challenge, I would definitely go along with Cosmos DB, as I merely love its scalability and suppleness. As a NoSQL service, interacting with the database merely feels extra pure in your software code than working for complicated ORM middleware required for conventional databases. Furthermore, the selection of a number of consistency ranges makes it very simple to decide on the fitting stability between velocity and consistency.

My fundamental concern with Cosmos DB is its steep studying curve for directors used to coping with conventional SQL databases. Organising your first Cosmos DB database feels overwhelming. You’ll have to learn by means of numerous documentation or watch numerous tutorials to determine the way to arrange the service to meet your particular necessities.

Nevertheless, I feel that this may enhance over time because the adoption price will increase. Cosmos DB has already come a great distance in a short while, and I can’t wait to see the place we’re headed subsequent.

More Posts