Data Platform Airlift21 de Outubro \\ Microsoft Lisbon Experience

Azure SQL Database & SQL DWBruno Basto

DPSA

bbasto@microsoft.com

SQL Server de 1.0 a 2016

Magic Quadrant for Operational DatabaseManagement Systems

http://www.gartner.c

om/technology/repri

nts.do?id=1-

2PMFPEN&ct=15101

3&st=sb

Cloud-first but not Cloud Only

Using Azure SQL DB to improve core SQL Server features & cadence

Many interesting and compelling on-premises Cloud scenarios

How we develop SQL

How we develop SQL

• Predictable performance

• Scalability with no downtime

• Business continuity

and data protection

• all with near-zero administration

SQL DB

V12

Better database manageability• Table partitioning

• Compression (page, row)

• Large index rebuild

• Online index build/rebuild with LOB

column

• 2GB transaction size limit is removed

• CHECKPOINT support

• Extended Alter Database support

• Non-admin DBCC commands, e.g.

CHECKDB

Programmability and Insight• Analytic Window Functions

• CLR Support (SAFE, bytestream only)

• XML index and schema

• Heap Support (and SELECT INTO)

• Application role

• Change Tracking

• Sequences

• UTF-16 support

• Column-store indexes

• Extended DMV set

DataMasking

TDE

Azure AD

SQL DB

V12

Max per-database burst level

Elastic Database Tools

Single tenant per database Each tenant’s data is stored in a different database Better isolation of tenants as compared to multi-tenant model

Multiple tenants per database Multiple tenants share the same database Less isolation of tenants as compared to single tenant model Typically more cost-effective than the single tenant model

Hybrid model Some tenants share databases, others get their own database E.g., premium or paying customers get their own databases, while free tier

customers share databases

Temporal model Sharding based on date/time Most recent shard is constantly loaded with newly arriving data New shards added when current most recent shard nears capacity

See guidance from the Azure CAT team on sharding: MSDN: https://msdn.microsoft.com/en-us/library/azure/dn764977.aspx

DB1

Customer 1

Customer 2

Customer 3

DB2

Customer 4

Customer 5

Customer 6

DB1

Customer

1

DB2

Customer

2

Shard map management• Define groups of shards for your application

• Manage mapping of routing keys to shards

Data dependent routing• Route incoming requests to the correct

shard, e.g., given a customer ID

• Ensure correct routing as tenants move

• Cache routing information for efficiency

Multi-shard query• Interactive processing across several shards

• Same statement executed on all shards with UNION all semantics

Fleet Management• Orchestrate administrative tasks across

many databases

• Same script executed on all databases

• Ability to save merged results

Split/Merge• Grow or shrink capacity by adding or

removing databases

• Re-balance data among shards

• Isolate hotspots

Shard Elasticity• Dynamically adjust scale factor of database

• Trigger adjustment through policies

Client .NET APIs Management Services

Grow/shrink capacity

Cross-Shard Capabilities

Elastic

Scale app

shard1 shardi

Elastic

Scale

Manage-

ability

Admin/

DevOps

Admin/

DevOps

Application

Developer

… …shardj shardn

…Shard-local

operations

Client

libraryCross-

shard

extensions

Cross-shard

operations

Client

library

Shard-local

operations

Cross-shard

operations

Note that your application needs to be shardable to benefit from Elastic Scale. If the application

does not partition well, Elastic Scale (or any other sharding approach) is likely not going to be a good fit.

Vertical: Scale-up vs. scale-down Change service-tiers for a given database as capacity needs fluctuate

Horizontal: Scale-out vs. scale-in Add or remove databases as more or less capacity is needed

Standard

Standard

Premium

Standard Standard Standard Standard Standard Standard

Premium

Standard

Scale out/in

Sca

le u

p/d

ow

n

Business continuity & data protection

Programmatic “oops recovery” of data deletion or alteration; up to 35 days in Premium

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

Backups

sabcp01bl21

Azure Storage

sabcp01bl21

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

Backups

sabcp01bl21

Azure Storage

sabcp01bl21

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, RTO<24h, RPO<1 hr

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

RTO<1h, RPO<5m, you choose when to failover

Mission-critical business continuity on your terms, via programmatic APIs

Up to 4secondaries

27

Hybrid Apps

Benefits

Aviva implemented a hybrid-cloud solution based on Windows Azure that brings together mobile devices, on-prem apps, and social media

One of the things that we

loved about Windows Azure

is that we could develop code

with Visual Studio 2010,

upload it, and have it running

10 minutes later.

Jason SteeleSolutions Designer, Aviva

29

Speeds development and implementation

by storing data in SQL Database

Enables greater flexibility and innovation

Offers a highly scalable,

fully-managed infrastructure

Up and

running in10Minutes

31

Shift existing apps

Benefits

Samsung Electronics migrated its diverse Smart TV systemsto Windows Azure in two months

Because the infrastructure is

entirely taken care of, we only

need to deal with software and

services. For us, Windows Azure

has a competitive edge,

especially its compatibility with

existing systems and its

reliability.Chunun Kang

Head of Engineers, Visual Display Business Division

80%Reduction in hardware

& maintenance costs

compared to on-prem

Supports firmware updates and

registrations for 20 million smart TVs

Achieved greater competitive edge

through increased focus on core business

32

37

Website & mobile apps

Benefits

3M Informatics hosted its new Visual Attention Service on Windows Azure so managers can focus on business, not infrastructure

Because management time and

cost is so low with Windows

Azure, we focus on growing the

business, not on managing red

tape. As a result, we estimate

that we’re bringing in fifty

percent more revenue.

William K. SmythGlobal Business Manager, 3M VAS

Minimizes management time and cost

Enables cost-effective scalability

Instills customer confidence

39

$ 50%Revenue Growth

BI and analytics

Data enrichment and federated query

Data management and processing

Saas

Azure

PublicCloud

Office 365Office 365

AzureAzure

Elastic ScaleSpin up for heavy workloads, cycle down for daily activity

Buy time to insight based on what you need, when you need it

Choose the combo of compute and storage that meets your needs

PauseData remains in place – no reloading / restoring of data

When paused, cloud-scale storage is min cost

Automate via PowerShell/REST API

$$$$

SQL Server CompatibilityMature enterprise-ready SQL for sophisticated DW scenarios

Existing SQL Server scripts and tools just work

Continuous enhancements on language surface

Modular programming(write once, execute multiple times)

Faster code execution

Encapsulated programming logic

Easier maintenance of large tables

Improves performance

Enhanced scalability and availability

Allows proper use and comparisons of characters in different languages

Mature Column-Store technology for best-in-class DW query performance

Engine

Nodes

1 1 1 1 1 1 1 1 1 1 1 1

Worker

Nodes

1 2 3 4 5 6 10 12 15 20 30 60

Total # of

distributions

60 60 60 60 60 60 60 60 60 60 60 60

# of

distributions

per node

60 30 20 15 12 10 6 5 4 3 2 1

Concurrency

Slots

4 8 12 16 20 24 32 32 32 32 32 32

http://aka.ms/sqldw

Azure SQL Data

Warehouse

Azure Storage Blob(s)

D52D51 D53 D54 D55 D56 D58D57 D59 D60

D12D11 D13 D14 D15 D16 D18D17 D19 D20

D22D21 D23 D24 D25 D26 D28D27 D29 D30

D32D31 D33 D34 D35 D36 D38D37 D39 D40

D42D41 D43 D44 D45 D46 D48D47 D49 D50

D2D1 D3 D4 D5 D6 D8D7 D9 D10

Query unstructured data via PolyBase/T-SQL

• Enables query capabilities across common Hadoop

distributions (HDP & Cloudera) and Hadoop file

formats in Azure storage.

• Allows leveraging existing SQL skills and BI tools

• Supports multiple non-relational file formats

• Improved time-to-insights & simplified ETL

PolyBase for querying & managing non-relational

Hadoop and relational data

SQL DW Instance

Scale out compute

Hadoop VMs /

Azure StoragePolyBase

Free Azure

Trial

Try SQL Server

2016 CTP2http://aka.ms/trysql2016

http://aka.ms/tryazureUse Power BI for Free

http://powerbi.microsoft.com