state of azure sql database

#sqlsatParma#sqlsat566November 26th, 2016

State of Azure SQL DatabaseMarco Parenzan@marco_parenzan

Sponsors

Organizers

getlatestversion.it

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

• Microsoft Azure MVP 2016

• Microsoft Azure Trainer

• Curious about Retrogaming and IoT

Agenda

• Scenario 1 - IoT– Fundamentals– JSON Support– In-Memory OLTP in Azure SQL Database– Temporal Tables– Row Level Security

• Scenario 2 – On Line Transactions– Dynamic Data Masking– 10 years backup retention

• Scenario 3 - CQRS– GEO replication

SCENARIO 1 - IOT

DEVICE DATA INGESTION

Event Hub(Ingest)

Stream Analytics

(BL)SQL Azure

(Store)

#device 1

#device n

DEVICE DATA INGESTION

IoT Hub(Ingest +

Device Mgmt)

Azure Function(bridge)

SQL Azure(BL+Store)

#device 1

#device n

FUNDAMENTALSL

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

Build multitenant appswith isolation and efficiency Multitenant efficiency Maximize efficiency with elastic

database pools (see Emanuele Zanchettin session)

Manage and monitor growth without the administrative overhead of managing each database

5 10 20 50 100 250 1750DTUs

B S0 S1S2

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

SQL Database service tiers (single DB model)

Built For

Available SLA

Max Storage

Business Continuity

Security

Performance Objectives

Database Transaction Units (DTUs)

Available Tiers ($/Month) and GA Price

Point-in-time Restore (“oops” Recovery)

BASIC PREMIUMSTANDARD

Light transactional workloads Medium transactional workloads Heavy Transactional Workloads

99.99%*

2 GB 250 GB 500 GB

Any point within 7 days Any point within 14 days Any point within 35 days

Geo-restore, Active geo-replication, up to four readable secondary backups

Always Encrypted, Transparent Data Encryption, Azure Active Directory authentication, Auditing, row-level security, dynamic data masking

Transactions per hour Transactions per minute Transactions per second

S1 S2 S3 P2 P4 P6 P11

10 20 50 100

$15 $30 $75 $150

125 250 500 1,000 1,750

$465 $930 $1,860 $3,720 $7,001

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

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.

IN MEMORY DATABASE

In-Memory technology for real-time performance

In-Memory Analytics

In-Memory OLTP Breakthrough PerformanceReal-time Operational Analytics

Real-time business insight based on operational data

Expedite query and transaction processing speed

30x faster transactions

100xperformance gains

AZURE SQL DATABASE

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

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

Point-in-time restore Programmatic “oops recovery”

of data deletion or alterationAuto backups, transactional logs every 5 minBackups 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

10 years!!!!Automated export of logical backups for long-term backup protection

Geo- replicated

Restore from backup

SQL Database Backupssabcp01bl21

Azure Storagesabcp01bl21

Active geo-replication Mission-critical businessSelf-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 RTO<1h, RPO<5m, you choose when to

failover continuity on your terms, via programmatic

Active geo-replicationMission critical business continuity

Up to 4 secondaries

Service levels Basic, Standard and PremiumSelf Service

Readable Secondaries

Up to 4

Regions available Any Azure region

Replication Automatic, Asynchronous

Manageability tools REST API, PowerShell or Azure Portal

Recovery Time Objective (RTO)

<1 hour

Recovery Point Objective

<5 mins

Failover On Demand

CONCLUSIONS

Conclusions

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

THANKS! Q&A

#sqlsatParma#sqlsat566

state of azure sql database

Software

introducing azure sql database

azure sql database basics -...

sql azure database: present and future

tablas temporales en sql server 2016 y azure sql database...

introduction to sql database on azure

sql server and azure sql database gdpr guidance... ·...

windows azure sql database overview

introduction to windows azure and windows azure sql database

modern data security for the enterprises – sql server &...

alta disponibilidade com azure sql database - sql saturday...

notes: microsoft sql server 2017 and azure sql database ·...

sql azure federations - wou homepage - western … · web...

azure sql database basics - files.meetup.com sql...

advanced sql azure database

sql server managed instances - precision it · •azure sql...

introducing windows azure - kennisportal · sql database...

building cloud apps using azure sql database

what's new with azure sql database

sql azure database under the hood

sql server konferenz 2017 - register...