Image Source @ [Pixabay.com]
This article explains how Azure data architecture works, use cases for Azure SQL Database, MySQL, and PostgreSQL, and examines three methods you can use to migrate and deploy databases on Microsoft Azure.
Table of Contents
Microsoft Azure is a top cloud vendor, offering a wide range of services, including their own version of SQL Database, MySQL, and PostgreSQL. Azure SQL Database is a managed service offered in the cloud or on-premises.
Azure Database for MySQL is an enterprise-ready fully managed service typically used for eCommerce and gaming. Azure Database for PostgreSQL is a fully managed service typically used for analytics, financial management, and digital marketing.
Azure Data Architecture
As organizations adopt cloud services, the way applications are designed is changing, including how data is stored and processed. General-purpose databases are no longer a given and are being replaced with data stores that are optimized for specific workloads.
Cloud services also enable data stores to be built into pipelines through which data flows freely to multiple resources. Generally, this results in two types of data solutions—relational database management systems (RDBMS) and big data solutions.
RDBMS are closer to the general-purpose databases that have been used traditionally. These solutions support online transaction processing (OLTP) and online analytical processing (OLAP) workloads. The former is used to capture, store, and process data in real-time. The latter is used to perform complex queries on aggregated OLTP data.
Data in RDBMS solutions is relational with a predefined schema and constraints that maintain the referential integrity of the data. This data is often aggregated from multiple sources and stored in a data warehouse. To use data, you typically have to perform extract, transform, load (ETL) processes.
Big data solutions
Big data solutions are designed to accommodate collections of data that are too large or complex for RDBMS solutions. These solutions include components for the ingestion, processing, and analysis of data and can handle data in real-time or in batches.
Often, big data solutions are used for time series data, JSON documents, or key-value data. These solutions typically include data lakes, where data in multiple formats can be stored, and NoSQL databases.
3 Azure Managed Database Options
Cloud services enable greater flexibility in the way data is stored and accessed as a whole. However, many individual applications and workloads still greatly benefit from and depend on traditional databases.
The difference is that multiple databases can be easily deployed and managed in a single infrastructure. Below are three of the most common Azure database services that you can deploy.
Azure SQL Database
Azure SQL Database is a managed database service that you can use in the cloud or on-premises through an Azure Arc connection. It includes features for migration, data discovery, scalability, security, and machine learning. You can deploy SQL Database as a single database or as a cluster of databases with pooled resources.
Use cases of Azure SQL Database include:
- Software as a service (SaaS)—with up to 100TB per database and the ability to deploy hyperscale, multi-tenant databases.
- Mobile and web applications—available with the high availability and performance needed for mission-critical applications.
- Development and testing—enables you to quickly replicate and deploy environments for development and testing.
Business continuity—service provides 99.995% availability protected by a service level agreement (SLA).
Azure Database for MySQL
Azure Database for MySQL is a fully managed service that provides an enterprise-ready, community version of MySQL. MySQL Community Edition enables you to migrate using a lift and shift with the frameworks and languages you prefer. With Azure Database for MySQL, you gain access to a database with dynamic scalability, high availability, and customizability.
Use cases of Azure Database for MySQL include:
- Retail and eCommerce—enables you to build secure, scalable solutions with security and low latency.
- Gaming—dynamic scaling enables you to support users through bursts of traffic with minimal lag.
Azure Database for PostgreSQL
Azure Database for PostgreSQL is a fully managed service based on the Community Edition of PostgreSQL. It includes horizontal scalability, built-in security, and features for intelligent performance recommendations. Database for PostgreSQL integrates with a wide variety of tools and services, including PostGIS, TimescaleDB, and Visual Studio Code. You can use it with a variety of frameworks and languages, including Ruby on Rails, Node.js, and Python with Django.
Use cases for Azure Database for PostgreSQL include:
- Analytics—enables you to integrate with Azure Machine Learning Studio and Power BI for big data analytics.
- Financial management—built-in security meets the compliance requirements for financial applications and data.
- Digital marketing—you can integrate it with CMS on Web App or Redis Cache, enabling you to store customer data and perform personalization.
Deploy Databases to Microsoft Azure
One of the best reasons to deploy and migrate to a database in Azure is the support that is provided for migration. To ease this process, Azure offers several options to help you transition your data:
Data Migration Service (DMS)
DMS is a service specially designed to help you perform migrations. It includes the Data Migration Assistant (DMA) which can help you evaluate your existing databases, identify barriers to migration, and provide recommendations for optimizing your transfer and configuration.
You can use DMS to perform both off and online migrations. Offline migrations are best for smaller databases or those that can withstand downtime. It enables you to transfer data directly or via a database backup, including schema.
Online migrations are designed for mission-critical databases that require minimal or zero downtime migrations. This process involves migrating data piecemeal when it is not being used. After migration, any changes to that data are logged for asynchronous replication to the Azure database. This ensures that your databases are identical and enables you to transition workloads with no loss of service.
Another option for migration is to add an Azure database as a subscriber to a transactional replication process. This enables you to use snapshots of your database to transfer information while still syncing changes. Like with DMS, this enables you to switch over whenever you’re ready.
One thing to keep in mind with this method is that it only enables you to replicate tables with a primary key. For tables that don’t, you’ll need to use a different technique.
Data-tier application package (DACPAC)
A DACPAC is a file that contains definitions for your database schema, including view, tables, functions, stored procedures, and database objects. It serves as a backup of your schema. This method is typically used to create tests or proof of concept databases where you don’t need to transfer any or all of your data.
You can use a DACPAC to transfer schema from your existing database to an Azure database using Microsoft’s SQL Server Data Tools (SSDT), SQL Server Management Studio (SSMS), or a SQL package executable. Once your schema is transferred, you can transfer your data via backup, DMS, or any other method you choose.
Azure data architecture works in a way that enables you to leverage both RDBMS and big data solutions. There is also a wide range of shared responsibility models, with some services offered as SaaS, others as PaaS, as well as SLAs.
Azure also offers different migration options, to enable you to migrate from and to different data types and environments. DMS is Azure’s main migration tool, but you can also migrate via replication or use a DACPAC to transfer schema.
Thank you for reading my blog.
If you have any questions or feedback, please leave a comment.