Introduktion till Azure SQL Database Robert Folkesson Active Solution

Introduktion till Azure SQL Database Robert Folkesson Active Solution

WHY?

SQL Database Service A relational database-as-a-service, fully managed by Microsoft. For cloud-designed apps when near-zero administration and enterprise-grade capabilities are key. Perfect for cloud architects and developers looking for programmatic DBA-like Business functionality. Familiar & Elastic scale & performance Predictable performance levels Programmatic scale-out Dashboard views of DB metrics continuity & data protection selfmanaged Self-service restore Familiar tools Disaster recovery Programmatic Compliance-enabled Self-managed

WHY NOT? Azure VM SQL Server Active Directory Data warehouse, full SQL Server 32 cores, 448 GB RAM, 6500 GB SSD, 32 000 IOPS Not supported in Azure SQL Databases: Full text index SQL Server Agent FILESTREAM, etc - https://msdn.microsoft.com/en-us/library/azure/ee336281.aspx

How it works Client Layer SQL Server Applications and Tools PHP/Node Architecture Client Layer - Used by application to communicate directly with SQL Database. Services Layer – Gateway between Client layer and Platform layer. Platform Layer – Includes physical servicers and services that support the Services layer. Infrastructure Layer – IT administration of the physical HW and OS. ODBC .NET Apps ADO.NET Tabular Data Stream (TDS) TDS SSL Services Layer Provisioning Provisioning Provisioning Billing and Metering Billing and Metering Billing and Metering Connection Routing Connection Routing Connection Routing Platform Layer SQL Server SQL Server SQL Server SQL Database Fabric SQL Database Fabric SQL Database Fabric Management Services Management Services Management Services Infrastructure Layer

Portalen – SQL Management Studio Demo

Point-in-time restore Programmatic “oops recovery” of data deletion or alteration Auto backups, transactional logs every 5 min Backups in Azure Storage and georeplicated Creates a side-by-side copy, nondisruptive SQL Database Backups sabcp01bl21 Georeplicated REST API, PowerShell or Azure Portal Backups retention policy: Basic, up to 7 days Standard, up to 14 days Premium, up to 35 days Automated export of logical backups Restore from backup Azure Storage sabcp01bl21

Geo-restore Emergency data recovery when you need it most SQL Available in Basic, Standard and Premium Built on geo-redundant Azure Storage Database Backups sabcp01bl21 Georeplicated Azure Storage sabcp01bl21 Recover to any Azure region Restore to any Azure region

Standard geo-replication Opt-in business continuity for greater geo-redundancies Opt-in for Standard & Premium databases Creates a stand-by secondary Replicate to pre-paired Azure region Automatic data replication, asynchronous Opt-in via REST API, PowerShell or Azure Portal Microsoft-managed failover Georeplicated

Active geo-replication Mission-critical business continuity on your terms, via programmatic APIs Self-service activation in Premium Create up to 4 readable secondaries Replicate to any Azure region Automatic data replication, asynchronous REST API, PowerShell or Azure Portal You choose when to failover Up to 4 secondaries

Auditing Gain insight into database events & streamline compliancerelated tasks Configurable to track & log database activity Audit log Dashboard views in the portal for at-aglance insights Pre-defined Power View reports for deep visual analysis on Audit log data Audit logs reside in your Azure Storage account Available in Basic, Standard, and Premium *currently in preview SQL Database Auditin g Applicati on data Azure Storage

Point in time restore Demo

Migration

Predictable Performance Web / Business Basic / Standard / Machine Premium Machine Compute DB 2 DB 2 DB 7 DB 7 DB 5 5 DB DB 8 DB 8 Memory DB 6 DB 6 DB 9 DB 9 DB 2 DB 1 Bounding boxes eliminates noisy neighbors DB 4 DB 7 DB 5 DB 8 Memory DB 3 DB 6 DB 9 Reads DB DB 4 4 DB 3 Noisy neighbor! Reads Writes DB 1 DB 3 Writes DB 1 Compute

Roles and Responsibilities Azure SQL Database Customers Assure resource are available when they are requested Provide insights into and tools to measure resource consumption Provide guidance on how to use the new Service Tiers Chose the right Service Tier (features) Chose the right Performance Level Monitor and react to performance needs

Database Throughput Unit – DTU Monitoring % of current Performance Level Bounding Box Compute 60 % 5 0 % 5 0 % 75% DB workload Reads Writes Utilization Read Write CPU Memory Memory Represents the relative power (resources) assigned to the database Blended measure of CPU, memory, and read and write rates Compare the power across performance levels

Azure SQL Database Benchmark – ASDB An example representing meaningful OLTP-workload Uses six tables of varying sizes some of which are always larger than available memory and scale with the throughput Uses nine transaction types A transaction is a combination of multiple SELECT, DELETE, INSERT, UPDATE statements

New Performance Levels Summary Basic Standard Premium Performance Levels (DTU) 5 S0: 10 S1: 20 S2: 50 P1: 100 P2: 200 P3: 800 ASDB results 16,600 tx/hour S1: ,520 tx/minute S1: ,940 tx/minute S2: 2,570 tx/minute P1: 105 tx/second P2: 228 tx/second P3: 735 tx/second Maximum DB size 2GB 250GB 500GB Price* per hour (month) 0.0069 ( 5) S0: 0.0208 ( 15) S1: 0.0417 ( 30) S2: 0.1042 ( 75) P1: 0.6458 ( 465) P2: 1.292 ( 930) P3: 5.167 ( 3,720) *Starting November 1st

Resource Governance S2 Governor P1 Governor Governor CPU CPU CPU Writes Writes Writes SELECT * FROM a JOIN b ON RESULT Reads Reads Reads Resource requests are being not rejected but being queued Overloaded can result in long running transactions and command timeouts

Resource Monitoring master.sys.resource stats Based on 5 minute averages userdb.sys.dm db resource stats Based on 15 second averages Percentages relative to performance level Accessible though Azure Portal Allows to configure alerting!

Resource Monitoring Demo

Elastic Scale Customer scenarios; Elastic Scale Public Preview

Customer Scenario Application exceeds the limits of a single database: capacity, performance, geo-locality, isolation Application Types Single tenant: Each tenant’s data is stored in a different database Multi-tenant: Multiple tenants share the same database Hybrid model: Some tenants share databases, others get their own database Temporal Data: Telemetry ingestions/Internet of Things (IOT) scenarios Geo-location/Geo-political: Keeping data in geographical regions for performance and/or policy reasons

Terminology (2) Sharding Key (3) Shard Map Manager (8) Shardlet Customer Customer ID Table Name 1 Alice 2 Bob . Data Center Table Data Center ID DC Name 1 Boston 2 Miami (7) Reference Table (4) Shard (6) Sharded Table (1) Database (4) Shard (5) Shard Set

Elastic Scale Demo

Robert Folkesson twitter: @rfolkes [email protected] e www.robertfolkesson.se