what's new with azure sql database
Post on 11-Apr-2017
60 Views
Preview:
TRANSCRIPT
#sqlsat589February 25th, 2017
What’s new with Azure SQL Database?Marco Parenzan@marco_parenzan
#sqlsat589February 25th, 2017
Sponsors
#sqlsat589February 25th, 2017
Organizers
#sqlsat589February 25th, 2017
Marco Parenzan | @marco_parenzan• Community Lead 1nn0va – Pordenone
• Microsoft Azure MVP 2016
• Microsoft Azure Trainer
• Curious about Retrogaming and IoT
#sqlsat589February 25th, 2017
PAAS EVOLUTIONThe building block
#sqlsat589February 25th, 2017
PaaS You decide the size of the resources
you always overprovision, you can scale elastic
You don’t handle the resources’ infrastructure
CPU+Memory+I/O(=tier) Unit of Measure
#sqlsat589February 25th, 2017
Cortana Management SuiteMedia ServicesStorage Traffic
Manager
Visual StudioServices
OMS Management SuitMachine LearningCDNDocument DB
Search
SchedulerActive Directory Key Vault App Insights Cognitive Services Embedded Power BI
Hockey AppStream AnalyticsNotification HubIoT Hub Service Bus
Logic App
Where you decide the CPU on these?
Function
#sqlsat589February 25th, 2017
Serverless ArchitecturesServerless architectures refer to applications that significantly depend on third-party services (knows as Backend as a Service or "BaaS") or on custom code that's run in ephemeral containers (Function as a Service or "FaaS"), the best known vendor host of which currently is AWS Lambda. By using these ideas, and by moving much behavior to the front end, such architectures remove the need for the traditional 'always on' server system sitting behind an application. Depending on the circumstances, such systems can significantly reduce operational cost and complexity at a cost of vendor dependencies and (at the moment) immaturity of supporting services.
From Martin Fowler [https://martinfowler.com/articles/serverless.html]
#sqlsat589February 25th, 2017
Serverless vs. PaaS PaaS
You decide the size of the resources you always overprovision, you can scale elastic
You don’t handle the resources’ infrastructure
CPU+Memory+I/O(=tier) Unit of Measure Serverless
You consume «blended» Unit of Measure
#sqlsat589February 25th, 2017
Database Transaction Unit (DTU) DTUs provide a way to describe the
relative capacity of a performance level based on a blended measure of CPU, memory, and read and write rates offered by each performance level.
Documented here: https://azure.microsoft.com/en-us/documentation/articles/sql-database-benchmark-overview
#sqlsat589February 25th, 2017
Azure SQL Database Fully managed SQL database service that lets you
focus on your business Database provisioning on-demand Predictable performance for enterprise workloads Elastic database pools for unpredictable SaaS
workloads 99.99% availability SLA Geo-replication and restore services for data
protection Secure and compliant to protect sensitive data Compatible with SQL Server 2016 databases
#sqlsat589February 25th, 2017
Predictable performance Isolated databases are allocated isolated resources Basic, Standard, and Premium tiers provide increasing
performance levels Scale up/down in response to actual or predicted change in
workload Databases remain online while scaling Hourly billing at highest rate that hour
S0 S1 S2 S3 P1 P2 P4 P6/P3 P11Maximum Database Size 2GB 1TBDTUs 5 10 20 50 100 125 250 500 1,000 1,750Point-in-time restore Any point within 7 days
Disaster RecoveryGeo-Restore to any
Azure regionMax In-Memory OLTP Storage N/A N/A N/A N/A N/A 1GB 2GB 3GB 8GB 10GBMax concurrent requests 30 60 90 120 200 200 400 800 1,600 2,400Max concurrent logins 30 60 90 120 200 200 400 800 1,600 2,400Max sessions 300 600 900 1,200 2,400 2,400 4,800 9,600 19,200 32,000
Standard Geo-Replication, offline secondary
Active Geo-Replication, up to 4 online (readable) secondary backups
Basic Standard Premium
250GB 500GB
Any point within 14 days Any point within 35 days
S0 S1 S2 S3 P1 P2 P4 P5 P11 P15Maximum Database Size 2GBDTUs 5 10 20 50 100 125 250 500 1,000 1,750 4,000Point-in-time restore Any point within 7 days
Disaster Recovery
Max In-Memory OLTP Storage N/A N/A N/A N/A N/A 1GB 2GB 4GB 8GB 14GB 32GBMax concurrent requests 30 60 90 120 200 200 400 800 1,600 2,400 6,400Max concurrent logins 30 60 90 120 200 200 400 800 1,600 2,400 6,400Max sessions 300 600 900 1,200 2,400 30,000 30,000 30,000 30,000 30,000 30,000
Premium
Active Geo-Replication, up to 4 online (readable) secondary backups
1TB
Any point last within 35 days
Basic Standard
250GB 500GB
#sqlsat589February 25th, 2017
Azure SQL Database Virtual Logical Server Logic container
«Database», «Elastic Pool» and «Data Warehouse»
Context «Endpoint» for connection (es.
dbdemo.database.windows.net) Users that can access to these databases «Policy» (es. «Audit», «Threat detection»)
You loose ALL the typical functionalities at server level
#sqlsat589February 25th, 2017
JSON SUPPORT
#sqlsat589February 25th, 2017
Built-in functions for JSON ISJSON - valid JSON ? JSON_VALUE extracts scalar value JSON_QUERY extracts an object or array
#sqlsat589February 25th, 2017
OPENJSON
#sqlsat589February 25th, 2017
FOR JSON In PATH mode dot syntax - 'Item.Price' –
formats nested output.
#sqlsat589February 25th, 2017
TEMPORAL TABLES
#sqlsat589February 25th, 2017
Temporal Tables Automatically keeps track of changed
data Available in SQL Azure Transparent to existing applications (if
needed)
#sqlsat589February 25th, 2017
Temporal Queries AS OF <date_time> FROM <start_date_time> TO <end_date_time> BETWEEN <start_date_time> AND
<end_date_time> CONTAINED IN (<start_date_time> ,
<end_date_time>) ALL
#sqlsat589February 25th, 2017
Temporal Tables Some limitations compared to “classic” tables
No TRUNCATE TABLE support INSTEAD OF triggers not supported
Temporal tables *can* be ALTERed A few limitations:
Cannot add a computed columns Cannot add an Identity column
Versioning can be turned on/off as we wish There is *no* automatic cleanup of versioning
Stretch Database offer “a sort of” automatic archival (but still no cleaning!)
Tips: https://msdn.microsoft.com/library/mt637341.aspx
#sqlsat589February 25th, 2017
ROW LEVEL SECURITY
#sqlsat589February 25th, 2017
Row-level security Protect data privacy by ensuring the
right access across rows Give users access only the rows
applicable to their role Simplify the design and coding of
security in your apps Administer with SQL Server
Management Studio or SQL Server Data Tools
#sqlsat589February 25th, 2017
DYNAMIC DATA MASKING
#sqlsat589February 25th, 2017
Dynamic data masking Limit the exposure of sensitive data by hiding
it from users Auto-discovery of potentially sensitive data to
mask Configurable masking policy
from the Azure portal or via DDL in the server
On-the-fly obfuscation of data in query results Flexibility to define a set of privileged users for
un-masked data access
#sqlsat589February 25th, 2017
GEO REPLICATION
#sqlsat589February 25th, 2017
High-availability platform
Reads are completed at the primaryWrites are replicated to secondaries
Single logical database
Write
Write Ack
Ack
Readvalue writeAck
Critical capabilities: Create new replica Synchronize data Stay consistent Detect failures Failover 99.99% availability
#sqlsat589February 25th, 2017
«Active Geo-Replication» Fino a 4 copie secondarie
Accessibile in sola lettura Supportati scenari di aggiornamento e
trasferimento «Failover» manuale
«Estimated Recovery Time»: <30 secondi «Recovery Point Objective»: <5
secondi Disponibile per tutti i «Service Tier»!
#sqlsat589February 25th, 2017
SCALABILITY
#sqlsat589February 25th, 2017
Scalability patters [1]
Monolithic database
#sqlsat589February 25th, 2017
«Scale up» e «Scale down» Change the service level
«Service Tier/Performance Level» Copy by replica operation
Interruption during switch Check compatibility with feature used
(ex. Database size)
#sqlsat589February 25th, 2017
Predictable performance Isolated databases are allocated isolated resources Basic, Standard, and Premium tiers provide increasing
performance levels Scale up/down in response to actual or predicted change in
workload Databases remain online while scaling Hourly billing at highest rate that hour
S0 S1 S2 S3 P1 P2 P4 P6/P3 P11Maximum Database Size 2GB 1TBDTUs 5 10 20 50 100 125 250 500 1,000 1,750Point-in-time restore Any point within 7 days
Disaster RecoveryGeo-Restore to any
Azure regionMax In-Memory OLTP Storage N/A N/A N/A N/A N/A 1GB 2GB 3GB 8GB 10GBMax concurrent requests 30 60 90 120 200 200 400 800 1,600 2,400Max concurrent logins 30 60 90 120 200 200 400 800 1,600 2,400Max sessions 300 600 900 1,200 2,400 2,400 4,800 9,600 19,200 32,000
Standard Geo-Replication, offline secondary
Active Geo-Replication, up to 4 online (readable) secondary backups
Basic Standard Premium
250GB 500GB
Any point within 14 days Any point within 35 days
S0 S1 S2 S3 P1 P2 P4 P5 P11 P15Maximum Database Size 2GBDTUs 5 10 20 50 100 125 250 500 1,000 1,750 4,000Point-in-time restore Any point within 7 days
Disaster Recovery
Max In-Memory OLTP Storage N/A N/A N/A N/A N/A 1GB 2GB 4GB 8GB 14GB 32GBMax concurrent requests 30 60 90 120 200 200 400 800 1,600 2,400 6,400Max concurrent logins 30 60 90 120 200 200 400 800 1,600 2,400 6,400Max sessions 300 600 900 1,200 2,400 30,000 30,000 30,000 30,000 30,000 30,000
Premium
Active Geo-Replication, up to 4 online (readable) secondary backups
1TB
Any point last within 35 days
Basic Standard
250GB 500GB
#sqlsat589February 25th, 2017
«Query Performance Insight»
#sqlsat589February 25th, 2017
SaaS issues Customers with different requirements
(performances) Customers in different regions Overprovisioning
#sqlsat589February 25th, 2017
Scalability patters [2]
Monolithic database
Tenant partitioning (1 db per customer)
#sqlsat589February 25th, 2017
Scenario IoT, device syncronization Multiple customers Monthly subscription
S0 S1 S2 S3 P1 P2 P4 P5 P11 P15Maximum Database Size 2GBDTUs 5 10 20 50 100 125 250 500 1,000 1,750 4,000Point-in-time restore Any point within 7 days
Disaster Recovery
Max In-Memory OLTP Storage N/A N/A N/A N/A N/A 1GB 2GB 4GB 8GB 14GB 32GBMax concurrent requests 30 60 90 120 200 200 400 800 1,600 2,400 6,400Max concurrent logins 30 60 90 120 200 200 400 800 1,600 2,400 6,400Max sessions 300 600 900 1,200 2,400 30,000 30,000 30,000 30,000 30,000 30,000
Premium
Active Geo-Replication, up to 4 online (readable) secondary backups
1TB
Any point last within 35 days
Basic Standard
250GB 500GB
#sqlsat589February 25th, 2017
Failure scenario Bad performance on a query (timeout) Need time to check
No time to check immediatly Immediate reaction: scale up (BasicS0)
Time: 5minutes Time to check: 2 weeks
Costs of DB: 12€/2=6€ Fixed query
Scale down (S0Basic)
#sqlsat589February 25th, 2017
Scalability patters [4]
Vertical partitioning (architectural)
Monolithic database
Tenant partitioning (1 db per customer)
Sharding (No SQL)
#sqlsat589February 25th, 2017
«Sharding» Molteplici database condivisi da più
«tenant»?
Tecnica «Scale out» distribuzione dati Strutturati in maniera identica In più database indipendenti In base a «Sharding Key» Mappature per intervallo di valori o lista
#sqlsat589February 25th, 2017
«Elastic Database client library» «Shard Map Management»
Mappatura «Shard Keys» e database «Shard Keys» liste o intervalli di valori
«Data Dependent Routing» Supporto apertura connessione in base a «Shard Key»
«Multi-Shard Queries» Supporto Query che coinvolge più «Shard» Fusione unico «Result Set» con Semantica UNION ALL
Image source: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-scale-shard-map-management
#sqlsat589February 25th, 2017
«Elastic Database Pools» DTU Pool (eDTUs) and Storage Pool
(GBs) shared Minimal guaranteed Maximum set «Auto-Scale» You can add/remove during lifetime
Image source: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-what-is-a-dtu
#sqlsat589February 25th, 2017
BACKUP
#sqlsat589February 25th, 2017
Point-in-time restoreProgrammatic “oops recovery” of data deletion or alterationAuto backups
«Full» weekly «Differenziale» some hours «Log» every 5-10 minutes
Backups in Azure Storage and geo-replicated Creates a side-by-side copy, non-disruptiveREST API, PowerShell or Azure PortalBackups retention policy: Basic, up to 7 days Standard, up to 14 days Premium, up to 35 days (preview
10 years)
Automated export of logical backups for long-term backup protection
Geo- replicated
Restore from backup
SQL Database Backupssabcp01bl21
Azure Storagesabcp01bl21
#sqlsat589February 25th, 2017
CONCLUSIONS
#sqlsat589February 25th, 2017
Conclusions
Almost complete alignment with IaaS/On Premise SQL Server 20016Think PaaSThink about alternatives to Management System
#sqlsat589February 25th, 2017
Funzionalità rispetto versione «on-premise» Not everything on Azure SQL Database
Es. CDC, CLR, FILESTREAM, PBM, Service Broker Different implementation
Es. AwaysOn AG/Active Geo Replication, SSIS/Azure Data Factory
Some in preview Es. Row-Level Security, Data Masking, Temporal
Tables
#sqlsat589February 25th, 2017
Compliance
SOC 1 Type 2 and SOC 2 Type 2
ISO/IEC 27001 FedRAMP/FISMA
HIPAAbusiness associateagreement (BAA)
PCI DSS Level 1
EU Model Clauses
#sqlsat589February 25th, 2017
THANKS! Q&A
#sqlsat589
top related