Skip to content

Latest commit

 

History

History
154 lines (135 loc) · 7.54 KB

3.2. SQL (Azure SQL Database).md

File metadata and controls

154 lines (135 loc) · 7.54 KB
Raw

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

Database Tiers

  • 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

Elastic Scale

  • 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.

Availability

  • Azure Site Recovery: Only for VMs

AlwaysOn

  • 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

Active geo-replication

  • 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

Auto-failover group

  • 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.

Failover strategies

  • 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).