introduktion till azure sql database robert folkesson active solution
TRANSCRIPT
Introduktion till Azure SQL Database
Robert FolkessonActive Solution
WHY?
SQL Database Service
Elastic scale & performance
Business continuity &
data protection
Familiar & self-managed
Predictable performance levels
Programmatic scale-out
Dashboard views of DB metrics
Self-service restore
Disaster recovery
Compliance-enabled
Familiar tools
Programmatic
Self-managed
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 functionality.
WHY NOT?
• Not supported in Azure SQL Databases:• Full text index• SQL Server Agent• FILESTREAM, etc - https://msdn.microsoft.com/en-us/library/azure/ee336281.aspx
• Azure VM + SQL Server• Active Directory • Data warehouse, full SQL Server• 32 cores, 448 GB RAM, 6500 GB SSD, 32 000 IOPS
How it worksArchitectureClient 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.
Infrastructure Layer
PHP/Node .NET AppsSQL Server
Applicationsand Tools
ODBC ADO.NET
Tabular Data Stream (TDS)
Client Layer
Services Layer
Provisioning
Billing and Metering
Connection Routing
Provisioning
Billing and Metering
Connection Routing
Provisioning
Billing and Metering
Connection Routing
…
…
…
TDS+SSL
Platform Layer
SQL Server
SQL Database Fabric
Management Services
SQL Server
SQL Database Fabric
Management Services
SQL Server
SQL Database Fabric
Management Services
…
…
…
Demo
Portalen – SQL Management Studio
Point-in-time restore
Auto backups, transactional logs every 5 min
Backups in Azure Storage and geo-replicated
Creates a side-by-side copy, non-disruptive
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 for long-term backup protection
Programmatic “oops recovery” of data deletion or alteration
Geo- replicated
Restore from backup
SQL Database Backupssabcp01bl21
Azure Storagesabcp01bl21
Available in Basic, Standard and Premium
Built on geo-redundant Azure Storage
Recover to any Azure region
Emergency data recovery when you need it most
Geo-restore
Geo- replicated
SQL Database Backupssabcp01bl21
Azure Storagesabcp01bl21
Restore to any Azure region
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
Opt-in business continuity for greater geo-redundancies
Standard geo-replication
Geo- replicated
Active geo-replication
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
Mission-critical business continuity on your terms, via programmatic APIs
Up to 4 secondaries
Auditing
Configurable to track & log database activity
Dashboard views in the portal for at-a-glance 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
Gain insight into database events & streamline compliance-related tasks
SQL Database
Auditing
Auditlog
Application data
Azure Storage
*currently in preview
Demo
Point in time restore
Migration
Predictable PerformanceWeb / Business Basic / Standard /
PremiumMachineCompute
Wri
tes R
eads
Memory
DB 1DB 2
DB 3
DB 4
DB 7
DB 5 DB 6
DB 8DB 9
MachineCompute
Wri
tes R
eads
Memory
DB 1DB 2
DB 3
DB 4
DB 7
DB 5 DB 6
DB 8DB 9
DB 1DB 2
DB 3
DB 4
DB 7
DB 5DB 6
DB 8
DB 9
Noisy neighbor!
Bounding boxes
eliminates noisy
neighbors
Roles and ResponsibilitiesAzure SQL Database
• 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
Customers
• Chose the right Service Tier (features)
• Chose the right Performance Level
• Monitor and react to performance needs
Database Throughput Unit – DTU
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 Simplifies talking about performance, think IOPS vs. %
Monitoring% of current Performance Level
Utilization
75%
Read
50%
Write50%
CPU
60%
Memory
Compute
Wri
tes R
eads
Memory
DB workload
Bounding Box
Azure SQL Database Benchmark – ASDB An example representing meaningful
OLTP-workloadUses six tables of varying sizes some of which are always larger than available memory and scale with the throughputUses nine transaction typesA transaction is a combination of multiple SELECT, DELETE, INSERT, UPDATE statements
New Performance Levels Summary
Basic Standard Premium
Performance Levels (DTU)
5 S0: 10S1: 20S2: 50
P1: 100 P2: 200 P3: 800
ASDB results 16,600 tx/hour S1: ,520 tx/minuteS1: ,940 tx/minuteS2: 2,570 tx/minute
P1: 105 tx/secondP2: 228 tx/secondP3: 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
RESULT
CPU
Writes
Reads
GovernorCPU
Writes
Reads
P1 Governor
Resource Governance
SELECT * FROM a JOIN b ON …
S2
Resource requests are being not rejected but being queued
Overloaded can result in long running transactions and command timeouts
CPU
Writes
Reads
Governor
master.sys.resource_statsBased on 5 minute averages
userdb.sys.dm_db_resource_statsBased on 15 second averages
Percentages relative to performance levelAccessible though Azure Portal
Allows to configure alerting!
Resource Monitoring
Demo
Resource Monitoring
Elastic ScaleCustomer 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
(1) Database
(2) Sharding Key
. . .
(3) Shard Map Manager
(4) Shard
(4) Shard
(5) Shard Set
(6) Sharded Table
(7) Reference Table
(8) Shardlet
Customer ID Name
1 Alice
2 Bob
Customer Table
Data Center ID DC Name
1 Boston
2 Miami
Data Center Table
Demo
Elastic Scale
