what's new with azure sql database

#sqlsat589February 25th, 2017

What’s new with Azure SQL Database?Marco Parenzan@marco_parenzan


Sponsors


Organizers


Marco Parenzan | @marco_parenzan• Community Lead 1nn0va – Pordenone

• Microsoft Azure MVP 2016

• Microsoft Azure Trainer

• Curious about Retrogaming and IoT


PAAS EVOLUTIONThe building block


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


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


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]


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


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

https://azure.microsoft.com/en-us/documentation/articles/sql-database-benchmark-overview




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


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


1TB

Any point last within 35 days

Basic Standard

250GB 500GB


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


JSON SUPPORT


Built-in functions for JSON ISJSON - valid JSON ? JSON_VALUE extracts scalar value JSON_QUERY extracts an object or array


OPENJSON


FOR JSON In PATH mode dot syntax - 'Item.Price' –

formats nested output.


TEMPORAL TABLES


Temporal Tables Automatically keeps track of changed

data Available in SQL Azure Transparent to existing applications (if

needed)


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


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

https://msdn.microsoft.com/library/mt637341.aspx


ROW LEVEL SECURITY


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


DYNAMIC DATA MASKING


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


GEO REPLICATION


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


«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»!


SCALABILITY


Scalability patters [1]

Monolithic database


«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)


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


Basic Standard Premium

250GB 500GB

Any point within 14 days Any point within 35 days


Disaster Recovery


Premium


1TB


Basic Standard

250GB 500GB


«Query Performance Insight»


SaaS issues Customers with different requirements

(performances) Customers in different regions Overprovisioning



Monolithic database

Tenant partitioning (1 db per customer)


Scenario IoT, device syncronization Multiple customers Monthly subscription


Disaster Recovery


Premium


1TB


Basic Standard

250GB 500GB


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)



Vertical partitioning (architectural)

Monolithic database

Tenant partitioning (1 db per customer)

Sharding (No SQL)


«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


«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

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-scale-shard-map-management


«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

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-what-is-a-dtu


BACKUP


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


CONCLUSIONS


Conclusions

Almost complete alignment with IaaS/On Premise SQL Server 20016Think PaaSThink about alternatives to Management System


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


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


THANKS! Q&A

#sqlsat589

what's new with azure sql database

Software