SQL (Azure SQL Database)

See Azure SQL Database for managed Microsoft SQL
Other third-party managed SQL databases in Azure include:
1. Azure Database for MySQL
  - MYSQL community with tools preinstalled like mysql.exe and phpMyAdmin.
2. Azure Database for PostgreSQL
- Both has common features:
  - You can run one or more databases with this instance.
  - High availability with no additional cost.
  - Predictable performance, using inclusive pay-as-you-go pricing.
  - Scale on the fly within seconds.
  - Secured to protect sensitive data at-rest and in-motion.
  - Automatic backups and point-in-time-restore for up to 35 days.
  - Enterprise-grade security and compliance.
Other options
- Own VMs running e.g. MySQL
- ClearDB provides managed MySQL that you can create from Azure Marketplace.

Azure SQL Database

Database as a service
Predictable performance
- Through comparison of DTUs (Database Throughput Units)
- DTUs describe capacity of tier and performance level.
- Relative: E.g. Basic (B) 5, Standard (S2) 50, so standard is 10 times power of Basic.
High compatibility
- A tabular data Stream (TDS) endpoint is provided for each logical server.
Management
- In portal: Azure Management Portal -> Manage
- REST API, PowerShell or Xplat CLI
Retention
- Long-term retention (LTR): Automatically retain backups in Azure Blob storage for up to 10 years
- Azure SQL Database automatic backups: 7-35 days

Three tier where every tier have different performance levels:

Tier	DTU	Ideal for
Basic	5	small dbs, single active operation, dev/test, small scale apps
Standard	10 - 100	multiple operations, workgroup or web apps
Premium	100 - 800	high transaction volumes, large number of users, multiple operations, mission critical apps

Basic/Standard model
- Based on remote storage.
- Uses Azure Premium Storage Disks, i.e. accessed over network.
Premium/Business Critical model
- Uses AlwaysOn Availability Groups
- Has local attached SSD storage
  - Provides higher IOPS and throughput

Scaling it/out by simplified sharding.
- Coordinates data movement between shards to split or merge ranges of data among different databases
Satisfies common scenarios such as pulling a busy tenant into its own shardING
Split-Merge service
- Provided through a downloadable package
- Customers can deploy as an Azure cloud service into their own subscription.
- Two main parts:
  - An Elastic Scale library (SDK) for client applications to configure shards and access shards.
    - Direct transactions to the appropriate shard
    - Perform queries across multiple shards
    - Modify service tier for existing shards
  - The Elastic Scale features in Azure SQL Database that implements the any changes requested by your application.

Available only in SQL servers
Azure SQL database implements it in its underlying infrastructure for Premium tier to achieve high availability but abstracts it away using active geo-replication
AlwaysOn availability groups
- An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together.
Always On Failover Cluster Instances
- Leverages Windows Server Failover Clustering (WSFC) functionality
- Provides local high availability through redundancy at the server-instance level failover cluster instance (FCI).
- FCI (failover cluster instance)
  - Single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets

Leverages the Always On technology of SQL Server.
Azure SQL Database feature.
- Supported in Elastic Pools.
- ❗ Not supported in managed instances.
- 💡 Use auto-failover groups for them.
  - It also supports SQL database.
Multiple secondaries are supported as opposed to auto-failover groups.
Allows you to create readable secondary databases of individual databases on a SQL Database server in the same or different data center (region).
Best practice configuration:
- User => Azure Traffic Manager =>
  1. Ingress LB => SQL in Primary Logical Server
  2. Ingress LB => SQL in Secondary Logical Server
- Capabilities:
  - Automatic Asynchronous Replication
  - Readable secondary databases
  - Planned / Unplanned failover
  - Multiple readable secondaries
  - Geo-replication of databases in an elastic pool
    - Each secondary database can separately participate in an elastic pool or not be in any elastic pool at all.
  - Configurable compute size of the secondary database
  - User-controlled failover and failback
  - Keeping credentials and firewall rules in sync

Allows you manage replication and failover of a group of
- databases on a SQL Database server
- or all databases in a Managed Instance to another region
It uses the same underlying technology as active geo-replication.
You can failover
1. Manually
2. Alternatively you can delegate it to the SQL Database service based on a user-defined policy.
When working with single or pooled databases on a SQL Database server and you want multiple secondaries in the same or different regions, use active geo-replication.
You get URL (HA URL) for the DB.
Terminology and capabilities
- Failover group
  - Group of databases that can either be:
    - SQL Database servers
    - Managed Instances
- Primary & secondary hosts
  - Use Active-geo replication for multiple secondary hosts.
- Adding databases in elastic pool to failover group
  - Multiple failover groups: You can configure multiple failover groups for the same pair of servers to control the scale of failovers. Each group fails over independently
    - ❗ Managed Instance does not support multiple failover groups
- Grace period with data loss: By configuring GracePeriodWithDataLossHours, you can control how long the system waits before initiating the failover that is likely to result data loss.

Automatic failover policy: Configured by default
Read-only failover policy:
- Kicks in after a set period (hours) so that data is not last during a long failure.
- Disabled by default
- When disabled:
  - Performance of the primary is not impacted when the secondary is offline.
  - Read-only sessions will not be able to connect until the secondary is recovered
- When enabled
  - Read-only traffic will be automatically redirected to the primary if the secondary is not available.
  - Performance gets lower in the primary
- Use if you
  - cannot tolerate downtime for the read-only sessions.
  - are OK to temporarily use the primary for both read-only and read-write traffic at the expense of the potential performance degradation of the primary
Planned failover: Full synchronization without data loss
- Use-cases:
  - Perform disaster recovery (DR) drills in production when the data loss is not acceptable
  - Relocate the databases to a different region
  - Return the databases to the primary region after the outage has been mitigated (failback).
Unplanned failover: Switches directly without any synchronization.
Manual failover: You can initiate forced or friendly failover (with full data synchronization).