Storage
Mobility
Protection
Analysis and control
TOPICShare
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:
- Database Workloads in Azure
- Online analytical processing (OLAP) in Azure
- Online transaction processing (OLTP) in Azure
- Non-relational databases in Azure
- Azure database services
- Services for Azure database migration
- Simplifying Azure databases with Cloud Volumes ONTAP
Database Workloads in Azure: RDBMS vs Big Data
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:
- Too large or complex for traditional database methods
- Needs to be processed in real-time or batches
- Unstructured or semi-structured data such as JSON files, time series data, or key-value data
OLAP in Azure
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.
Azure Databases: OLAP Capability Matrix
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
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:
- Azure SQL Database
- SQL Server on Azure VM
- Azure Database for MySQL
- Azure Database for PostgreSQL
See the table below to learn about the capabilities of each of these services.
OLTP Capability Matrix of Azure Databases
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 |
Non-Relational Databases in Azure
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 |
Azure Database Services
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
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:
- IoT and telematics—supports real-time bursts of data and processing.
- Retail and marketing—supports catalog data and event sourcing.
- Gaming—supports low-latency requirements, large request bursts, and social graphs.
- Web and mobile applications—supports flexible schema and complex data types needed for social applications and personalization.
Azure SQL Database
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:
- Web and mobile applications—supports high-availability and performance needed for mission-critical applications.
- Software as a service (SaaS)—supported by Elastic Pools and Hyperscale, which allows up to 100TB per database.
- Development and testing—supports easy setup and replication of data and environments for testing and moves to production.
- Business continuity—supports continuity with service level agreement (SLA) promising 99.995% availability.
Azure Database for MySQL
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:
- Analytics—integrates with machine learning services and visualization tools.
- Retail and eCommerce—supports product catalogs and session states for reliable order pricing and customer experience.
- Web and mobile applications—connection to Azure App Services enables fast development of scalable, cross-platform or native applications.
- Finance management—supports the security and resilience needed for financial applications. Can also integrate with Power BI for financial analytics.
Azure Database for PostgreSQL
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:
- Digital marketing—can integrate with Redis Cache and CMS on Web App for storage of customer data needed for personalization.
- Analytics—integrates with Azure Machine Learning Studio and Power BI.
- Finance management—provides the security needed for financial applications along with integration with Power BI.
- Retail and eCommerce—supports product catalog and session state data.
- Web and mobile applications—supports scalability needed for mission-critical applications.
Azure Database for MariaDB
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
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:
- Data cache—supports use as a cache for other database services and enables you to scale throughput.
- Web applications—enables you to quickly store and access session data and can simultaneously connect with multiple Web App instances.
- Messaging—supports pub/sub messaging and real-time message routing. You can use it to feed data out through Web App or API App instances.
What is Azure Database Migration Service?
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.
Simplifying Azure Database with Cloud Volumes ONTAP
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.
Learn More About Azure Databases
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 SQL Database: 18 Options for SQL Server on the Cloud
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.
Azure Oracle: Your First Oracle Database on Azure
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: Automate Your Cloud DB Migration
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 Explained: Key Features & Challenges
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
How to Back Up SQL Server to Azure
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 Backup & Recovery: The Abridged Guide
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
Azure Backup: SQL Server Backups on Azure and How to Use Them
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 on Azure: Fully Managed vs Self Managed
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
Azure Disk Storage for SQL Server
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
Azure PostgreSQL: Managed or Self Managed?
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?
See Additional Guides on Key Managed Services Topics
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.
AWS Big Data
Authored by NetApp
- AWS Big Data: 6 Options You Should Consider
- AWS Data Lake: End-to-End Workflow in the Cloud
- AWS Data Analytics: Choosing the Best Option for You
AWS database
Authored by NetApp
- Types of Database Services Offered on AWS
- AWS Oracle: How to Lift and Shift Your Oracle DB to Amazon
- Overcome AWS RDS instance Size Limits with Data Tiering to AWS S3
AWS serverless
Authored by Lumigo
- AWS Serverless Application Model
- AWS Step Functions - Lumigo
- Aurora Serverless v2: Why You Need It, New Features and Pricing
