Overview of Azure Options for SQL Server

Problem

SQL Server professionals must be familiar with traditional on-premises and cloud deployment options, such as Azure-based deployments. They should also be aware of the differences between deployment, configuration, monitoring, high availability, and disaster recovery solutions for SQL Server.

This tip will discuss an overview of Azure for the SQL Server Professional and things you should know about Azure.

Solution

Cloud solutions are broadly classified into three categories:

  1. Software-as-a-Service (SaaS)
  2. Platform-as-a-Service (PaaS)
  3. Infrastructure-as-a-Service (IaaS)

The following diagram shows the shared responsibility model in Azure and highlights the difference between SaaS, PaaS, and IaaS.

  • SaaS: The cloud provider handles most of the responsibilities, and the customer is responsible for the data.
  • PaaS: The cloud provider is responsible for physical infrastructure, operating systems, development tools, and databases.
  • IaaS: Responsibility lies with the customer, except for handling the physical hosts, network, and data center.
Shared responsibilities model in Azure

image reference: Microsoft docs

Microsoft provides the offerings below for SQL Server in the Azure cloud based on the cloud deployment options:

  • Azure SQL Database
  • Azure SQL Managed Instance
  • SQL Server on Azure Virtual Machine

Let’s learn about these SQL database offerings and understand their differences.

Azure SQL Database

These are some of the main attributes.

  • It is a PaaS deployment option in Azure with an OS and SQL Server instance abstraction.
  • Users can create a SQL database and start using it for their development work.

Reference articles:

A few key features of Azure SQL Database:

  • Single database
  • Elastic pool
  • Hyperscale storage (100TB+)
  • Serverless compute
  • Fully managed Service
  • Private link support
  • High availability with Az isolation

Serverless Compute

The serverless computing tier offers the database with intermittent and unpredictable usage and price and performance tradeoffs. It allows features such as auto-scaling, Automatic Pausing and Resume, and billing per second. It is ideal for Development and testing environments and Applications with frequently changing workloads that do not require fixed compute availability.

Refer to the article Going Serverless with Azure SQL Database for more details on Serverless computing in addition to these articles:

Databases

Single Database:

  • Hyperscale storage (up to 100 TB)
  • Fully Managed service

Elastic Pool:

  • Resource sharing between multiple databases to optimize price.
  • Simplified Performance management for multiple databases.
  • Fully Managed Service.

Purchase Model

Azure SQL Database offers purchases based on database transaction units (DTU-based) or virtual cores (vCore-based).

  • DTU: It provides a bundled measure of compute, storage, and I/O resources.
  • vCore-based: You can independently select compute and storage resources. This model also allows using Azure Hybrid benefit for SQL Server or pay in advance (reserved capacity) for cost savings.

Service Tiers

The vCore model supports three service tiers:

General PurposeBusiness CriticalHyperscale
Scalable compute and storageHighest resilience to failuresSuitable for large environments (100 TB+ storage)
Suitable for most of the workloadSuitable for applications having low latency requirementsAvailable only in a single database.
Budget orientedSupports in-memory OLTP for higher performanceIt lies between the general purpose and critical business service tiers.

Reference articles:

Security

The following table summarizes the Azure SQL Database security options for network security, identity and access, data protection, and security management.

Network Security

  • Vnet, Firewall Rules, NSG, Private Link

Identity and Access

  • Authentication options: Azure AD, SQL Auth, Windows Auth
  • Azure RBAC
  • Roles and permissions
  • Row-level security

Data Protection

  • Encryption-in-use (Always Encrypted)
  • Encryption-at-rest (TDE)
  • Encryption-in-flight (TLS)
  • User-managed keys
  • Dynamic data masking

Security Management

  • Advanced threat detection
  • SQL audit
  • Audit integration with log analytics and event hubs
  • Vulnerability assessment
  • Data discovery and classification
  • Azure Security Center

Reference articles:

High Availability

  • Backup and Restore. Azure SQL Database is automatically backed up as the following schedule:
  • Full database backup: Once a week
  • Log backup: Every 5-10 minutes
  • Differential backup: Every 12-24 hours
  • Backup storage: In read-access geo-redundant storage (RA-GRS)
  • Option to choose zone-redundant storage (ZRS) or locally redundant storage (LRS)

Geo-replication and Auto-failover Groups. The diagram below shows the features of Geo-replication and failover groups.

the features of Geo-replication and failover groups

Reference articles:

Azure SQL Managed Instance

Azure SQL Managed Instance is also an Azure PaaS offering and works best for lift-and-shift migrations to the cloud. You get a SQL Server instance, but Azure removes the overhead of managing the underlying VM from the customer. The key features of the Azure SQL Managed instances are:

  • Fully managed service
  • Single interface for on-premises and cloud workloads
  • SQL Server surface area support
  • Native virtual network support
  • Fully managed service
  • Operational database enabled with Azure AD and RBAC controls
  • End-to-end database migration with minimal code changes
  • High availability with built-in failover groups
  • Predictable costs with flexible compute, storage, and licensing options
  • ideal for customers looking to use instance-scoped features

Deployment Options

Single Instance:

  • SQL Server surface area (vast majority)
  • Native virtual network support
  • Fully managed service

Instance Pool:

  • Pre-provision compute resources for migration
  • Enable cost-efficient migration
  • Ability to host smaller instances
  • Fully Managed service

Purchasing Model

Azure SQL Managed Instance does not support the DTU model; only Virtual Core-based deployment is supported.

The link Azure SQL Managed Instance vs. Azure SQL Database gives a comparison between the Azure PaaS deployments.

Reference articles:

Case Study:

SQL Server on Azure VM

SQL Server Azure VM is an Infrastructure-as-a-Service (IaaS) that gives you full access to SQL Server features and instances similar to an on-premise SQL Server.

Key Features

  • SQL Server IaaS with proven success
  • Editions: Enterprise, Standard, Developer, Express
  • Windows, Linux OS available
  • High-volume, ETL and OLTP workloads supported
  • SSAS, SSIS, and SSRS
  • Automated backups and restores with Azure Blob Storage
  • Built-in high-availability options
  • Enhanced storage performance for premium disks
  • Azure Hybrid Benefit reduces costs for moving on-premises workloads to Azure

Reference articles:

Next Steps

  • Explore Microsoft docs on Azure SQL Server deployment options, understand your requirements, and choose the appropriate deployment from Azure SQL Database, Azure SQL Managed Instance, or SQL Server on Azure VM.
  • Use the appropriate tool, such as MySQL workbench Azure Database Studio, to connect to and query the MySQL database in Azure.
  • You can review existing tips for Azure.

Related Posts

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *