More about Azure database
- Oracle on Azure: Managed Service vs Managed Storage
- Azure SQL Database Backup & Recovery: The Abridged Guide
- Azure SQL Server: Managed Service Vs. Managed Storage
- Azure Backup: SQL Server Backups on Azure
- Azure Oracle: Your First Oracle Database on Azure
- Azure Database Migration Service: Automate Cloud DB Migration
- How to Back Up SQL Server to Azure
- Azure SQL Database: 18 Options for SQL Server on the Cloud
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
- 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 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|
|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|
|Runs on platform||N/A||Windows, Linux, Docker||N/A||N/A|
|Supported languages||T-SQL, .NET, R||T-SQL, .NET, R, Python||SQL||
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.
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 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 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 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 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 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: 7 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 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.
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.
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.
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.
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.