Explore relational data in Azure
5 minute read
Describe Azure SQL services and capabilities
Azure SQL is a collective term for a family of Microsoft SQL Server based database services in Azure. Specific Azure SQL services include:
SQL Server on Azure Virtual Machines (VMs) - A virtual machine running in Azure with an installation of SQL Server. The use of a VM makes this option an infrastructure-as-a-service (IaaS) solution that virtualizes hardware infrastructure for compute, storage, and networking in Azure; making it a great option for “lift and shift” migration of existing on-premises SQL Server installations to the cloud.
Azure SQL Managed Instance - A platform-as-a-service (PaaS) option that provides near-100% compatibility with on-premises SQL Server instances while abstracting the underlying hardware and operating system. The service includes automated software update management, backups, and other maintenance tasks, reducing the administrative burden of supporting a database server instance.
Azure SQL Database - A fully managed, highly scalable PaaS database service that is designed for the cloud. This service includes the core database-level capabilities of on-premises SQL Server, and is a good option when you need to create a new application in the cloud.
Azure SQL Edge - A SQL engine that is optimized for Internet-of-things (IoT) scenarios that need to work with streaming time-series data.
Describe Azure services for open-source databases
Azure data services are available for other popular relational database systems, including MySQL, MariaDB, and PostgreSQL. The primary reason for these services is to enable organizations that use them in on-premises apps to move to Azure quickly, without making significant changes to their applications.
MySQL, MariaDB, and PostgreSQL are relational database management systems that are tailored for different specializations.
MySQL
- leading open source relational database for Linux, Apache, MySQL, and PHP (LAMP) stack apps
- several editions; Community, Standard, and Enterprise
- Community is FOC and typically used for web apps
- Standard is paid and offers higher performance, and uses a different technology for storing data
- Enterprise is paid and provides a comprehensive set of tools and features, including enhanced security, availability, and scalability
Azure database for MySQL
- PaaS implementation of MySQL in the Azure cloud, based on the MySQL Community Edition.
- includes high availability at no additional cost, and scalability as required. You only pay for what you use. Automatic backups are provided, with point-in-time restore.
- two deployment options: Single Server and Flexible Server
Azure Database for MySQL Single Server
- Single servers are best for existing applications already using single server
Azure Database for MySQL Flexible Server
- provides more granular control and flexibility over database management functions and configuration settings
- recommended deployment option for all new developments or migrations
Benefits of Azure Database for MySQL
- High availability features built-in.
- Predictable performance.
- Easy scaling that responds quickly to demand.
- Secure data, both at rest and in motion.
- Automatic backups and point-in-time restore for the last 35 days.
- Enterprise-level security and compliance with legislation.
- The system uses pay-as-you-go pricing so you only pay for what you use.
- Azure Database for MySQL servers provides monitoring functionality to add alerts, and to view metrics and logs.
MariaDB
- newer database management system, created by the original developers of MySQL
- compatibility with Oracle Database
- built-in support for temporal data. A table can hold several versions of data, enabling an application to query the data as it appeared at some point in the past.
Azure Database for MariaDB
- based on the MariaDB Community Edition.
Benefits of Azure Database for MariaDB
- Built-in high availability with no additional cost.
- Predictable performance, using inclusive pay-as-you-go pricing.
- Scaling as needed within seconds.
- Secured protection of sensitive data at rest and in motion.
- Automatic backups and point-in-time-restore for up to 35 days.
- Enterprise-grade security and compliance.
PostgreSQL
- hybrid relational-object database
- enables you to store custom data types, with their own non-relational properties.
- database management system is extensible; you can add code modules to the database, which can be run by queries
- ability to store and manipulate geometric data, such as lines, circles, and polygons
- has its own query language called pgsql that enable you to write stored procedures that run inside the database.
Azure Database for PostgreSQL Single Server
- similar benefits as Azure Database for MySQL
- three pricing tiers: Basic, General Purpose, and Memory Optimized
- tier supports different numbers of CPUs, memory, and storage sizes; you select one based on the load you expect to support.
Azure Database for PostgreSQL Flexible Server
-more control and server configuration customizations
- better cost optimization controls
Azure Database for PostgreSQL Hyperscale (Citus)
- scales queries across multiple server nodes to support large database loads
- database is split across nodes
- Data is split into chunks based on the value of a partition key or sharding key
- option for the largest database PostgreSQL deployments in the Azure Cloud
- 100GiB +
Benefits of Azure Database for PostgreSQL
- highly available service.
- It contains built-in failure detection and failover mechanisms.
- pgAdmin tool, which you can use to manage and monitor
- records information about queries run against databases on the server, and saves them in a database named azure_sys
- query the query_store.qs_view view to see this information, and use it to monitor the queries that users are running. This information can prove invaluable if you need to fine-tune the queries