Skip to content

Latest commit

 

History

History
181 lines (158 loc) · 24.3 KB

Azure-SQL-Database.md

File metadata and controls

181 lines (158 loc) · 24.3 KB

Azure SQL Database

Single and Managed Instance Shared Concepts

Pricing

Feature Comparison

Business Continuity

  • High Availability Architecture Differences Between General Purpose and Business Critical

    • Local HA is built-into ASDB: Single + MI.
    • General Purpose Tier
      • Replication of compute. Conceptionally similar to a SQL Server failover clustered instance.
    • Business Critical Tier
      • Replication of compute and (local SSD) storage.
      • Local synchronous replicas. Conceptionally similar to a SQL Server always on availability group.
      • This edition has fast local SSD storage.
      • Read-Only replica is available at no extra cost for reporting queries.
      • Zone redundant configuration is available in certain regions.

  • Remote BCDR is provided by the Failover Group feature

    • When created for SQL Managed Instances, a failover group contains all user databases in the instance and therefore only one failover group can be configured on an instance.
    • Auto-failover groups support replication of all databases in the group to only one secondary server in a different region
    • You can failover a failover group manually. You can also define an automatic failover policy (which defines a grace period) so that any outage which impacts one or several of the databases in the group results in automatic failover. The SQL Database service triggers failover after the failure is detected and the grace period has expired. If an outage is detected, SQL waits for the period you specified by GracePeriodWithDataLossHours. The default value is 1 hour. If you cannot afford data loss, make sure to set GracePeriodWithDataLossHours to a sufficiently large number, such as 24 hours.
    • Primary and secondary servers for the databases in the failover group must be in the same subscription
    • Consider dependencies external to the databases such as VNET's, server level logins, and server level firewall rules. Some of these are outlined here.
    • Failover Group SLAs
    • This article discusses common application patterns used to create globally distributed applications optimized for local access to the data, including the benefits and trade-offs of each option: Designing globally available services using Azure SQL Database.

  • Automated Backups

    • "SQL Database uses SQL Server technology to create full backups every week, differential backups every 12 hours, and transaction log backups every 5-10 minutes. The backups are stored in RA-GRS storage blobs that are replicated to a paired data center for protection against a data center outage. When you restore a database, the service figures out which full, differential, and transaction log backups need to be restored."
    • Azure Paired Regions
    • Backup Storage Costs
      • 100% of database size is provided at no extra charge

  • Backup Integrity

  • Long Term Backup Retention

    • Up to 10 years for single db. Currently in public preview for managed instance

  • Point In Time Restore. Restore a database to a point-in-time within the retention period. This operation will create a new database in the same server as the original database.

  • Restore a deleted database.Restore a deleted database to the time it was deleted or any time within the retention period. The deleted database can only be restored in the same server where the original database was created.

  • Export a Single DB Database. When you need to export a database for archiving or for moving to another platform, you can export the database schema and data to a BACPAC file. A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from the database.

  • Copy Only Backups are available in SQL Managed Instance

  • Geo-Restore allows you to restore a database to another geographical region.

Maintenance

Migration

Single Instance

Secure

Business Continuity

Monitoring

  • Azure Metrics. You can pin metrics to an Azure Dashboard.
  • Stream Diagnostic Logs to Log Analytics for centralized longer term monitoring
    • SQL Log Analytics/Intelligent Insights
      • SQL Analytics Solution. If you configure Intelligent Insights log data to be streamed to Azure SQL Analytics workspace, Azure can use built-in intelligence to detect and provide detailed analysis of disruptive events that cause poor performance.
  • Query Performance Insights provides intelligent query analysis for single and pooled databases.
  • Query Store can be accessed through SSMS + Azure Portal provides intelligent query analysis. This is used by the Query Performance Insights feature.
  • Extended Events + DMVs for advanced troubleshooting and monitoring.

Scale: Tuning

  • Can scale tier manually or programmatically
  • Within an elastic pool, individual databases are given the flexibility to auto-scale within set parameters
  • Query Tuning Guidance
  • Consider using columnstore indexes for your larger analytics tables, non-clustered columnstore indexes are available for HTAP tables.
  • Automatic tuning
  • Database Advisor Performance Recommendations
  • Update Statistics/Defragment/Re-index
  • Database Compatibility Level 15
    • Memory Grant Feedback - SQL 2019 new feature applied to Azure SQL DB
    • SQL Server 2019 includes built-in query processing capabilities called Intelligent Query Processing. By updating your database compatibility level to 150 (the default level for SQL Server 2019), the query processor in the SQL Server engine can enhance performance through capabilities like batch-mode on row store, scalar UDF inlining,or table variable deferred compilation.
    • It can automatically correct memory-related query execution issues through memory grant feedback.

Scale: Data Replication

  • You can configure an Azure SQL Database as the push subscriber in a one-way transactional or snapshot replication replication topology. The SQL Server acting as publisher and/or distributor can be an instance of SQL Server running on-premises, an Azure SQL Managed Instance, or an instance of SQL Server running on an Azure virtual machine in the cloud.
  • SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple databases, both on-premises and in the cloud.

Scale: Data Distribution + Partitioning

  • Scale out databases in Azure SQL Database using the Elastic Database tools. Sharding is a technique to distribute large amounts of identically structured data across a number of independent databases. It is especially popular with cloud developers creating Software as a Service (SAAS) multi-tenant offerings for end customers or businesses. Sharding works best when every transaction in an application can be restricted to a single value of a sharding key. That ensures that all transactions are local to a specific database.

  • Partitioned Indexed Views can also be created on a partitioned table, and can themselves be partitioned.

  • A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

  • Partitioned Tables and Indexes

    • Identify which indexes to partition. - Indexes are put in place to optimize queries. So, first you’ll want to identify the top queries and the top indexes being used in your environment.
    • Partition indexes which will (1) benefit from partition elimination to help improve query performance or (2) benefit from reduced impact from maintenance tasks (such as reindexing, statistics rebuild, and bulk loads) by moving to partition level operations instead.
    • Performance Guidelines “If you frequently run queries that involve an equi-join between two or more partitioned tables, their partitioning columns should be the same as the columns on which the tables are joined”
    • Be aware of limitations when using non-aligned partitioned indexes.
    Partition Switch Operations Allowed? Per-Partition Operations Allowed? (Truncate/ Compression/ Reindex/ Reorg)
    Partition Aligned Y Y
    Non-Partition Aligned (clustered index/heap is partitioned + non-clustered indexes are not partitioned) Y, but must disable NC indexes Y, but must disable NC indexes
    Non-Partition Aligned (clustered index/heap is not partitioned + non-clustered indexes are partitioned) N N

Managed Instance

see Azure SQL Database Managed Instance

Hyperscale

  • Time to resize compute is independent of the size of data
  • Decouple storage, compute, transaction logs
  • Use for VLDBs and when you need flexibility to scale compute/storage while maintaining many of the performance benefits of business critical (fast IOPS)
  • Capabilities include Constant time operations (backups, restores, scale), support for VLDBs, ability to provision one or more read-only/hot-standby nodes, and improved performance due to higher log throughput and faster transaction commit times
  • Distributed Architecture
  • Migration to Hyperscale tier is currently one-way
  • Connect to READ replica

Serverless