Azure is a top cloud vendor, providing a wide range of computing services. For databases, Azure offers solutions for relational database management systems (RDBMS), as well as big data workloads. RDBMS use cases include online transaction processing (OLTP) and online analytical processing (OLAP). For big data workloads, Azure offers solutions like key-value storage, as well as real-time and batch processing.
In this post, we’ll provide a comprehensive overview of the various database services offered on Azure, and show how NetApp Cloud Volumes ONTAP can help simplify Azure database migration and management.
This is part of an extensive series of guides about managed services.
In this article, you will learn:
As the popularity of the cloud increases, the way applications are designed is changing and so is the way that data is processed and stored. Today, many solutions use multiple specialized data stores instead of the general-purpose databases of the past. These solutions work through data pipelines, automating where data is stored, how it is processed, and by which tools it is ingested.
These changes have enabled users to move data workloads from traditional relational database management system (RDBMS) models to big data models.
RDBMS workloads
RDBMS workloads employ online transaction processing (OLTP) and online analytical processing (OLAP) methods. OLTP is used to support transaction-oriented applications. OLAP enables you to analyze data from multiple databases at once. In combination, these methods enable you to use extract, transform, load (ETL) processes to aggregate and leverage data.
Big data workloads
Big data workloads involve volumes of data that have one or more of these characteristics:
When using RDBMS workloads in Azure, your data is copied from OLTP systems to OLAP systems. This copying is typically performed via SQL Server Integration Services through Azure Data Factory, a service designed to allow developers to integrate disparate data sources.
You can also connect various visualization and data exploration tools to your Analysis Services servers to produce rich, interactive insights of your data. For example, Excel, Power BI, or third-party services.
In Azure, you can use the following services to store data for OLAP applications:
SQL Server Analysis Services (SSAS)
SSAS enables you to apply data mining functionality and is typically used for business intelligence. You can use SSAS with on-premises servers or via an Azure-hosted virtual machine (VM).
Azure Analysis Services
Azure Analysis Services is a fully managed option that mirrors many of the features available through SSAS. With it, you can connect to data sources across your cloud and on-premises resources.
Clustered Columnstore index for SQL Server
Clustered Columnstore index is an option that is available for Azure SQL Database and SQL Server 2014 and up. This method of storing your data indexes data in columns as opposed to rows. This allows for more efficient data processing, and reduced IO and storage requirements.
From SQL Server 2016 and up, you also have the option of using hybrid transactional/analytical processing (HTAP). HTAP enables you to process OLAP and OLTP workloads on the same platform. This removes the need for separate systems and multiple data copies.
Below is a compatibility matrix comparing the Azure database services according to OLAP requirements. It can help you determine which service best suits your needs and how these services differ. This matrix is taken from the Azure documentation.
SQL Server Analysis Services | Azure Analysis Services | SQL Server with Columnstore Indexes | Azure SQL Database with Columnstore Indexes | |
Managed service | No | Yes | No |
Yes |
Supports multidimensional cubes | Yes | No | No | No |
Supports tabular semantic models | Yes | Yes | No | No |
Integrates multiple data sources | Yes | Yes | No | No |
Supports real-time analytics | No | No | Yes | Yes |
Requires process to copy data from source(s) | Yes | Yes | No | No |
Azure Active Directory integration | No | Yes | No | Yes |
OLTP in Azure is typically connected to websites and applications via APIs. For example, App Service APIs and web apps. Generally, OLTP is not used on its own. More often, it is combined with the OLAP service, like those covered above.
In Azure, you can use the following data stores for OLTP:
See the table below to learn about the capabilities of each of these services.
Below is a compatibility matrix comparing the Azure database services according to OLTP requirements. This matrix can help you understand which service is best suited to your OLTP needs. This matrix is taken from the Azure documentation.
Capability | Azure SQL Database | SQL Server on Azure VM | Azure Database for MySQL | Azure Database for PostgreSQL |
Managed service | Yes | No | Yes | Yes |
Runs on platform | N/A | Windows, Linux, Docker | N/A | N/A |
Supported languages | T-SQL, .NET, R | T-SQL, .NET, R, Python | SQL |
SQL, PL/pgSQL |
In addition to the above services, Azure also provides a variety of options for big data workloads and other workloads requiring non-relational databases. Below are brief descriptions of the type of non-relational databases that are available through Azure.
Database Types |
Description |
Azure Services |
Document |
Document data stores use sets of object data values and named string fields to organize data. For example, JSON or XML documents. |
● Azure Cosmos DB |
Column-family |
Column-family data stores use columns and rows to organize data. This differs from a relational database in that columns are grouped, and rows don’t need a value for every column. |
● Cosmos DB Cassandra API ● HBase in HDInsight
|
Key/value |
Key/value data stores assign data values to unique keys. Stored data is hashed according to the key it is associated with. |
● Azure Cosmos DB Table API ● Azure Cache for Redis ● Azure Table Storage |
Graph |
Graph data stores use nodes and edges to store data. Nodes are the data elements and edges define the relationship between elements. |
● Azure Cosmos DB Graph API |
Time series |
Time series data stores contain sets of values organized by time. For example, telemetry data or Internet of things (IoT) sensor streams. |
● Azure Time Series Insights ● OpenTSDB with HBase on HDInsight |
Object data |
Object data stores enable you to store binary objects, such as files, video, or VM images. Each object contains stored data, metadata, and a unique ID. |
● Azure Blob Storage ● Azure Data Lake Store ● Azure File Storage |
When selecting a database in Azure, it helps to know exactly what features and compatibility you’re getting with each service. Below is a more detailed look at some of the most common services.
Azure Cosmos DB is a fully managed, multi-modal database service. It offers turnkey, global distribution, multi-master replication, automatic scaling, and single-digit millisecond read/write latency. You can use Cosmos DB with SQL, Cassandra, MongoDB, Table, etc., and Gremlin through integrated APIs. With Cosmos DB, you also have the choice of selecting from five levels of consistency, from eventual to strong.
Use cases for Cosmos DB include:
Azure SQL Database is a managed database service that you can use in Azure or on-premises via Azure Arc. It includes features for machine learning, scalability, data discovery, built-in security, and easy migration. You can use SQL Database as a Single Database for simple applications or Elastic Pool for multi-tenant applications.
Use cases for SQL Database include:
Azure Database for MySQL is a fully managed database service based on the community edition of MySQL. It includes all of the features that come with MySQL Community edition. It also includes built-in security, Advanced Threat Protection, and the ability to scale compute and storage resources independently. You can integrate this service with Azure Web Apps, WordPress, and Drupal.
Use cases for Database for MySQL include:
Azure Database for PostgreSQL is a fully managed database service that you can use in Azure or on-premises through Azure Arc. It includes features for intelligent performance analyses, built-in security, and horizontal scaling through Hyperscale. You can integrate it with a wide variety of tools and extensions, including PostGIS, Timescale, and Visual Studio Code. You can also use it with a variety of frameworks and languages, including Python, Java, Ruby on Rails, Node.js and PHP.
Use cases for Database for PostgreSQL include:
Azure Database for MariaDB is a fully managed database based on the community edition of MariaDB. It includes features for high-availability, scalability, and built-in security. You can integrate it with Azure Web Apps, WordPress, and Drupal. Use cases for Database for MariaDB match the use cases for Database for MySQL and Database for PostgreSQL.
Azure Cache for Redis is a fully managed, in-memory database service. It provides single-digit millisecond latency, flexible scaling, and built-in security. With Cache for Redis, you can replicate data across regions for greater reliability. You can also use caches in an Azure Virtual Network (VNet) for greater access control and security.
Use cases for Cache for Redis include:
Azure Database Migration Service (DMS) is a fully managed service you can use to migrate data from databases and data warehouses. With it, you can perform either online or offline migrations. Online migrations allow you to continuously sync your data for minimal downtime. You can use DMS to move data from a variety of sources, including SQL Server, Azure Oracle, MongoDB, and Cassandra.
When you use Azure Database Migration Service, the tool first helps you assess your current database assets, assesses your workloads, and provides recommendations for adjustments prior to migration. Part of this is accomplished through integration with the Data Migration Assistant, which can generate reports on your data.
Learn more about Azure migration in our series of blog posts on the subject.
NetApp Cloud Volumes ONTAP, the leading enterprise-grade storage management solution, delivers secure, proven storage management services on AWS, Azure and Google Cloud. Cloud Volumes ONTAP supports up to a capacity of 368TB, and supports various use cases such as file services, databases, DevOps or any other enterprise workload, with a strong set of features including high availability, data protection, storage efficiencies, Kubernetes integration, and more.
In particular, Cloud Volumes ONTAP helps in addressing database workloads challenges in the cloud and filling the gap between your cloud-based database capabilities and the public cloud resources it runs on.
In this article, we’ve looked at a number of popular Azure databases, the capabilities offered, the appropriate use cases for each database, and how to address cloud-based database challenges with Cloud Volumes ONTAP.
There’s a lot more to learn about Azure databases. To continue your research, take a look at the rest of our blogs on this topic:
Azure provides many options for deploying and managing SQL servers in the cloud. You can, for example, deploy a standalone database to an Azure VM. Or you can create an elastic pool of databases with automatic scalability. There are seven deployment methods for SQL servers in Azure, and it is critical that you choose the right one for your project.
You can learn about these models, and how to create a single database in Azure SQL database, in our article: Azure SQL Database: 18 Options for SQL Server on the Cloud.
Microsoft Azure and Oracle have partnered to make it easy for cloud admins to deploy Oracle databases on Azure. You can get support from both Oracle and Azure, including maintenance and assistance with backup and disaster recovery.
There are several Oracle cloud applications and services you can run on the Azure cloud. To understand your options, check out our article: Azure Oracle: Your First Oracle Database on Azure.
Azure Database Migration Service (DMS) provides fully managed assistance for anyone migrating on-premises databases to Azure database offerings. Learn what is Azure Database Migration Service (DMS), how it helps you automate the migration of your databases to the cloud, and what types of databases, data types, and migration types it supports.
Read: Azure Database Migration Service: Automate Your Cloud DB Migration
Azure SQL Managed Instance is a fully-managed deployment option designed to provide near-perfect compatibility for SQL migrations. Learn what is Azure SQL Managed Instance, what features and prices are offered, and what to consider before using this managed service.
Read: Azure SQL Managed Instance Explained: Key Features & Challenges
Organizations are increasingly adopting Azure storage for long-term and short-term retention of backup data as a low-cost and secure alternative to on-premises backup storage solutions. With its lower costs & scalability, Azure storage is ideal for backup data. Find out how to back up SQL Server to Azure to gain those benefits for your database.
Read: How to Back Up SQL Server to Azure
Azure SQL Database is a fully managed cloud-based database service, with built-in AI-based tuning, high availability, multi-layered security, and data protection. Learn how Azure SQL Database backup & recovery works, what are the use cases for Azure SQL Database backup, and what is accelerated database recovery (ADR).
Read: Azure SQL Database Backup & Recovery: The Abridged Guide
A successful business continuity strategy depends on how fast your data can be retrieved in the event of an unexpected outage. Investing in a well-rounded backup solution is critical for databases, and the cloud is the most cost-effective part of such plans. It’s critical for your cloud or on-prem SQL database to be protected. Find out how to do it using Azure Backup.
Read: Azure Backup: SQL Server Backups on Azure and How to Use Them
Oracle is the preferred RDBMS solution for line-of-business applications, owing to its proven performance, data integrity, security, and portability features. Learn about two options to deploy Oracle on Azure: the fully managed way with OCI vs the self managed way using Azure VMs.
Read: Oracle on Azure: Fully Managed vs Self Managed
Deployment of Azure SQL Server using the IaaS model in Azure comes with its own share of benefits—full control over the environment and configuration flexibility being the most prominent among them. Find out which Azure disk storage is best for your deployment.
Read: Azure Disk Storage for SQL Server
Learn about Azure’s managed PostgreSQL databases services, and discover a second option—running PostgreSQL in an Azure virtual machine, with storage leveraging Azure managed disks or third party solutions like NetApp Cloud Volumes ONTAP.
Self-managing PostgreSQL in a VM is a bit more challenging, but lets you achieve the maximum level of control and optimization, as you would have in your local data center.
Read: Azure PostgreSQL: Managed or Self Managed?
Together with our content partners, we have authored in-depth guides on several other topics that can also be useful as you explore the world of managed services.
Authored by NetApp
Authored by NetApp
Authored by Lumigo