Microsoft Data Platform Evolution New innovations

Paweł Potasiński Product Manager Data Insights

pawelpo@microsoft.com

Disclaimer

• There is no public release of SQL Server vNext available at the moment.

• Some of the presented features can disappear or be a subject of substantial changes.

Mobile Reports

Natural

language

query Dashboards Applications

Streaming Relational

Internal &

external

Non-relational NoSQL

Orchestration

Machine

learning Modeling

Information

management

Complex event

processing

Data

The Microsoft Data Platform

SQL Server 2016

Enhanced in-memory

ColumnStore

In-memory OLTP

Analysis Services

in-memory

PowerPivot in-memory

for Excel

In-memory in the cloud

Analysis Services

in-memory

In-memory ColumnStore

PowerPivot in-memory

for Excel

PowerPivot in-memory

for Excel

SQL Server

2008 R2

SQL Server

2012

SQL Server

2014

Enhanced in-memory

ColumnStore

In-memory OLTP

Analysis Services

in-memory

PowerPivot in-memory

for Excel

In-memory in the cloud

IAAS & PAAS

In-memory OLTP for

greater number of apps

Operational Analytics on

in-memory OLTP

The in-memory journey

SQL Server

2016

In-memory OLTP Enhancements •

•

•

•

•

•

•

•

•

•

•

•

•

•

•

•

Enhanced Columnstore Indexes • Clustered

• Additional NCI allowed

• PK/FK allowed

• AlwaysON AG – supported with readable secondary

• Snapshot and RC Snapshot isolation levels supported

• Nonclustered • Updateable

• Can be filtered

• New query execution strategies

• Online REORGANIZE removes deleted rows

• New DMVs, XE and PerfMon counters

• Parallel Insert

• Single thread batch mode execution

• Batch anti-semi-join

• Multiple count DISTINCT

• Window aggregates

• Sort

AlwaysOn Enhancements

Query Data Store

1.Enable Query Store.

2.Use a set of DMV’s.

3.Find the bottleneck.

4.Find the fastest plan.

5.Mark fastest plan to be

used for the query.

http://slavasql.blogspot.com

ALTER DATABASE MyTestDB SET QUERY_STORE (interval_length_minutes = 1); ALTER DATABASE MyTestDB SET QUERY_STORE = ON; EXEC sp_query_store_force_plan 1, 1;

dbo.Patients

Jane Doe

Name

243-24-9812

SSN

USA

Country

Jim Gray 198-33-0987 USA

John Smith 123-82-1095 USA

dbo.Patients

Jane Doe

Name

1x7fg655se2e

SSN

USA

Jim Gray 0x7ff654ae6d USA

John Smith 0y8fj754ea2c USA

Country

Result Set

Jim Gray

Name

Jane Doe

Name

1x7fg655se2e

SSN

USA

Country

Jim Gray 0x7ff654ae6d USA

John Smith 0y8fj754ea2c USA

dbo.Patients

SQL Server

ciphertext

Query

Always Encrypted Help protect data at rest and in motion, on-premises & cloud

Trusted Apps

SELECT Name FROM

Patients WHERE SSN=@SSN

@SSN='198-33-0987'

Result Set

Jim Gray

Name

SELECT Name FROM

Patients WHERE SSN=@SSN

@SSN=0x7ff654ae6d

Column Encryption

Key

Enhanced

ADO.NET

Library

Column Master Key

Client side

Dynamic Data Masking

From http://blog.sqlexpert.pl/2015/04/09/dynamic-data-masking-w-sql-azure/

Mission critical performance

Performance

Operational analytics

In-memory OLTP for more applications

Query data store

Native JSON

Temporal database support

Security

Always Encrypted

Row Level Security

Dynamic Data Masking

Availability

Enhanced AlwaysOn

• 3 synchronous replicas for auto failover across domains

• Round robin load balancing of replicas

• DTC for transactional integrity across database instances with AlwaysOn

Enhanced online operations

Scalability

Enhanced database caching

No 16 core and 128GB of RAM restrictions in Standard Edition

Support for Windows Server 2016

• 12 TB memory support

Advanced Analytics Beyond business intelligence

Source: Gartner

Traditional BI Advanced Analytics

VA

LU

E

DIFFICULTY

Predictive

Analytics

Prescriptive

Analytics

Diagnostic

Analytics

Descriptive

Analytics

How can we

make it happen?

What will

happen?

Why did it

happen?

What

happened?

PolyBase Query relational and non-relational data with T-SQL

T-SQL query

SQL Server Hadoop

Quote:

************************

**********************

*********************

**********************

***********************

$658.39

Jim Gray

Name

11/13/58

DOB

WA

State

Ann Smith 04/29/76 ME

Data Scientist

Interact directly with data

Built-in to SQL Server

Data Developer/DBA Manage data and

analytics together

Built-in advanced analytics In-database analytics at massive scale

Example Solutions

• Sales forecasting

• Warehouse efficiency

• Predictive maintenance

Relational Data

Analytic Library

T-SQL Interface

Extensibility

? R

R Integration

010010

10010

0 01010

1

Microsoft Azure

Marketplace

New R scripts

010010

10010

0 01010

1

010010

10010

0 01010

1

010010

10010

0 01010

1

010010

10010

0 01010

1

010010

10010

0 01010

1

• Credit risk protection

Enhanced Analysis & Reporting Services Scalable on-premises BI solutions & new modern reports

Internet Explorer Firefox Safari Chrome

Edge

Rich visualizations on mobile devices

Business insights through rich visualizations

on any device. Native apps for Windows,

iOS and Android

Deeper insights across data

Access any data

PolyBase

Power Query for analytics and reporting

Enhanced SSIS

• Designer support for previous SSIS versions

• Support for Power Query

Scale and manage

Enterprise-grade Analysis Services

New single SSDT in Visual Studio 2015

Enhanced MDS

• Excel add-in 15x faster

• More granular security roles

• Archival options for transaction logs

• Reuse entities across models

Powerful insights

Built-in advanced analytics

Business insights through rich visualizations on mobile devices

Enhanced Reporting Services

Order history

Name Date Item

0x21ba906fdb52 1ba906fd 2ba906f

0x19ca706fbd9a 5re316rl 1da813t

1x59cm676rfd8b 1re306fd 3ha706f

2y36cg776rgd5b 3bg606fl 1ba906i

1t64ce87r6pd7d 5ba616rj 2ra933f

0y16cj676r6fd3e 1ra806fd 3ra806t

3x47cr876r6fd9g 2hh906fj 1sa906f

1x11cj576rf6d3d 6be916gi 3sa523t

2t74ce6676rfd9c 1hi9306fj 2ga906f

0y47cm776rfd1b 3bi506gd 1wa806f

4x32cj6676rfd9y 3ha916fi 2ba913i

0x77cf6676rfd3x 5re926gi 1ba902f

2t22cm676rfd3a 1ra536fe 1ea667i

0x19ca706fbd9a 5re316rl 1da813t

Order history

Name Date Item

0x21ba906fdb52 1ba906fd 2ba906f

0x19ca706fbd9a 5re316rl 1da813t

1x59cm676rfd8b 1re306fd 3ha706f

2y36cg776rgd5b 3bg606fl 1ba906i

0x19ca706fbd9a 5re316rl 1da813t

Stretch SQL Server into Azure Securely stretch cold tables to Azure with remote query processing

App

Query

Microsoft Azure

Customer data

Product data

Order History

Stretch to cloud

Query

Always Encrypted

ALTER DATABASE MyERPDatabase SET STRETCH ON; ALTER TABLE dbo.[Order History] ENABLE STRETCH;

Hyperscale cloud

Hybrid solutions

Stretch tables into Azure

Power BI with on-premises data

Hybrid scenarios with SSIS

Enhanced backup to Azure

Simplicity

Easy migration of on-premises SQL Server

Simplified Add Azure Replica Wizard

Consistency

Common development, management and identity tools

Consistent experience from on-premises to Azure

Today

Data

Complex data

orchestration

Relational

IAAS & PAAS

Non-Relational

& unstructured

Tomorrow

Data

Complex data

orchestration

Relational

IAAS & PAAS

Non-Relational

& unstructured

Complex data

orchestration

Non-Relational

& unstructured

Look for the SQL Server 2016 CTP2 this summer http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/

Pre-register for the SQL Server 2016 CTP & Azure SQL DW previews

http://www.microsoft.com/mcpnews

SQL Server 2016 CTP2 Features • In-Memory Enhancements

• Operational Analytics

• Always Encrypted

• AlwaysOn Enhancements

• Row Level Security

• Dynamic Data

• Native JSON

• Temporal Database

• Query Data Store

•

•

•

•

•

Enjoy SQLDay 2015 Thank you!

Paweł Potasiński Product Manager Data Insights

pawelpo@microsoft.com