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

S3P2

P11

P1

125

P4

500

P6

1000

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

P1S0

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

5

$4.99

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

1 TB


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

Up to

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

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

APIs


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