Azure database

Azure Database Services: OLAP, OLTP & NoSQL

[Azure, Cloud Volumes ONTAP, Database, Elementary, 9 minute read, Azure database, A]

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.

In this article, you will learn:

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.

In this post you can learn about these models, and how to create a single database in Azure SQL database.

Read more: Azure SQL Database: 18 Options for SQL Server on the Cloud.

Azure Database Migration Service: Automate Your Cloud DB Migration

Azure Database Migration Service (DMS) is a fully managed service that enables you to migrate data warehouses or databases to Azure. DMS supports the migratiton of multiple database types, including Oracle, MySQL, PostgreSQL, SQL Server. You can also automate the process with PowerShell commandlets.

This post reviews Azure DMS service models, service pricing, migration tools, and shows an example of the DMS process. 

Read More in: Azure Database Migration Service: Automate Your Cloud DB Migration.

Azure Oracle: Your First Oracle Database on Azure

Oracle offers several cloud services and applications you can run on Azure. This includes database hosting, management and deployment support. Both Azure and Oracle provide high availability, backup, and disaster recovery.

This post reviews the fundamentals of available Azure hosting solutions for Oracle, and walks you through the process of creating an Oracle database on Azure.

Read more: Azure Oracle: Your First Oracle Database on Azure

Azure Backup: SQL Server Backups on Azure and How to Use Them

Data that resides on relational databases, such as SQL Server, is crucial to enterprise line-of-business applications. Backing up that data is equally important. Whether deploying on-prem or in the cloud, Azure offers a solution — Azure Backup —SQL Server users can rely on to back up to the cloud. This article will walk you through the process of protecting your SQL databases deployed in Azure VMs using the Azure Backup. SQL Server deployments can also benefit from the data protection capabilities of Cloud Volumes ONTAP.

Read more in Azure Backup: SQL Server Backups on Azure and How to Use Them here.

Azure SQL Managed Instance Explained: Key Features & Challenges

Azure SQL Managed Instance is a fully managed deployment option that provides compatibility with SQL Server Database Engine used for on-premises databases. These instances enable you to deploy a native virtual network (VNet) that simulates on-premises deployments and increases security.

Read more: Azure SQL Managed Instance Explained: Key Features & Challenges

Azure SQL Server: Managed Service Vs. Managed Storage Deployment Options

Azure SQL Server deployments can come in two deployment options: through Azure’s database-as-a-service (DBaaS) offerings, or through building your own based on Azure storage and compute Infrastructure-as-a-Service (IaaS) products. Since Microsoft SQL Server is a major database that many enterprises rely on as a critical part of their businesses, deciding which of these two deployment options to go with is an important decision to make.

This blog introduces you to both the managed service DBaaS and managed storage on IaaS options for Azure SQL Server deployment. Find out the pros and cons of each, and the added benefits that Cloud Volumes ONTAP can give your database.

Read more in Azure SQL Server: Managed Service Vs. Managed Storage Deployment Options.

Azure SQL Database Backup & Recovery: The Abridged Guide

Azure SQL Database is a fully managed cloud-based database service, with built-in high availability, security and tuning. Azure SQL Database offers a wide range of data protection options, including backup and recovery.

This article reviews the Azure SQL Database backup and recovery options, including point in time restore (PITR), Azure SQL Database Managed Instance, and more.

Read more in: Azure SQL Database Backup & Recovery: The Abridged Guide

Oracle on Azure: Managed Service vs Managed Storage

Oracle has been a mainstay RDBMS solution for organizations the world over. With Oracle’s excellent performance, security features, data integrity, and portability, it’s easy to see why enterprises migrating line-of-business applications to Azure want to continue to use this critical database. But there is more than one way to deploy Oracle on Azure.

In this article we look at the two deployment options for Oracle on Azure: through the use of a managed database service via Oracle Cloud Infrastructure (OCI), or the managed storage option of building a database through virtual instances on the Azure Marketplace.

Read more in Oracle on Azure: Managed Service vs Managed Storage.

How to Back Up SQL Server to Azure

When backing up an SQL Server to Azure, you can serve your long-term and short-term retention requirements while benefiting from the cloud provider’s scalable resources, security capabilities, and cost-effective pay-as-you-go model.

This article walks you through how to back up SQL Server to Azure Blob storage. You’ll learn everything you need to know, from configuring the Azure storage to pointing the SQL Server to the new storage target in the cloud. It will also explain how the backup process can be improved even further with Cloud Volumes ONTAP.   

Read more: How to Back Up SQL Server to Azure

SQL Server End of Life Challenges and How Cloud Volumes ONTAP Can Help 

SQL Server 2008 and SQL Server 2008 R2 have reached their official end of life, leaving customers with hefty upgrading challenges. NetApp Cloud Volumes ONTAP can help tackle this issue, simplifying workload migration and enhancing public cloud capabilities. 

This article explores what SQL Service is, how it functions in the cloud, and how Cloud Volumes ONTAP can help rehost and upgrade SQL workloads and augment their performance in the cloud with capabilities such as data protection, security, and high availability.    

Read more: SQL Server End of Life Challenges and How Cloud Volumes ONTAP Can Help

Azure Big Data: 3 Steps to Building Your Solution

Azure provides a wide range of services for big data. This article explains four main types of big data services: databases, analytics, machine learning, and orchestration. Including three critical steps to implement when building a big data solution on Azure.

Read more: Azure Big Data: 3 Steps to Building Your Solution.

Azure NoSQL: Types, Services, and a Quick Tutorial

NoSQL databases are non-relational databases that can flexibly support data. These databases are highly scalable and can be adapted to a wide variety of applications and workloads. This makes NoSQL databases popular alternatives to traditional databases and is driving robust support from cloud vendors like Azure.  

This article explains what Azure NoSQL services are available, highlights the APIs provided for Azure's main NoSQL database (CosmosDB), and provides a brief tutorial for deploying a NoSQL cluster.

Read more: Azure NoSQL: Types, Services, and a Quick Tutorial

Azure Disk Storage for SQL Server

There are a bevy of advantages to deploying SQL Server using IaaS components—from gaining full control over the environment to being able to flexibly configure the database itself. A large part of the performance of your SQL Server will depend on its storage layer.

Block-based Azure disk storage offers SQL Server users the most highly performant storage type in Azure, but with several different options for it available picking the right one for your database’s requirements is crucial. This post will look at the different block storage offerings for use with SQL Server in Azure, including the IOPS and throughput levels and costs involved.

Read more about this in Block Storage for SQL Server in Azure

New call-to-action

-