Cloud App Development Immersion Hands on approach to build data-driven Intelligent applications

Tara Shankar Jana, Technical Product Manager, Microsoft Corp. Moderated By: Mindy Curnutt

Thank You microsoft.com Empower users with new insights through familiar tools while balancing the need for IT to monitor and manage user created content. Deliver access to all data types across structured and unstructured sources.

idera.com IDERA’s award-winning SQL Server database solutions and multi-platform database, application and cloud monitoring tools ensure your business never slows down.

2

attunity.com Attunity, a leader in data integration and management software, helps move, transform and analyze data efficiently in SQL Server/Azure environments.

JOIN PASS PASS is a not-for-profit organization which offers year-round learning opportunities to data professionals Membership is free, join today at www.sqlpass.org

Access to online training and content

Enjoy discounted event rates

Join Local Chapters and Virtual Chapters

Get advance notice of member exclusives

Save on PASS Summit 2016 Registration! • • • •

The world’s largest gathering of SQL Server & BI professionals Learn from the world’s top data experts, in over 190 technical sessions More than 4000 attendees from all over the world Meet the Microsoft engineering team!

Save $200 right now using discount code 24HOP200! www.passsummit.com

$2,195

until September 18, 2016

BIO Tara Shankar Jana • Senior Technical Product Manager at Microsoft • Working on and with relational database systems for more than 10 years, and cloud technologies for 4 years

• Working with the C+E Cloud App Dev and Data team @Microsoft and building data-driven Intelligent Applications

Developing with data Developer immersion

ELASTIC DATABASE JOBS

R SERVICES

ELASTIC DATA POOLS SQL DATABASE ADVISOR

DYNAMIC DATA MASKING TEMPORAL

POLYBASE

SQL DATA WAREHOUSE AZURE ACTIVE DIRECTORY

QUERY PERFORMANCE INSIGHTS

IN-MEMORY OLTP

DOCUMENT DB

AZURE SEARCH

THREAT DETECTION

COLUMNSTORE

U-SQL JSON ROW LEVEL SECURITY

DYNAMIC DATA MASKING ALWAYS ENCRYPTED ROW LEVEL SECURITY

Develope r

Sessions EXPERIENCE 1

EXPERIENCE 2

SQL Server 2016 – Everything built-in

Modernizing LOB apps with intelligence

EXPERIENCE 3

EXPERIENCE 4

Evolving SQL workloads from SW to SaaS

Modernizing cloud-based web apps

Scenarios

Scenarios

Scenarios

Scenarios

1.1 Recover from user errors more

2.1 Build predictive and prescriptive

3.1 Build multitenant apps with

4.1 Accelerate development with

1.2 Optimize transactional performance

2.2 Explore the power of JSON

3.2 Leverage built-in service intelligence

4.2 Build scalable search experiences for

1.3 Provide real-time insights into app

2.3 Re-architect your LOB workload for

3.3 Develop more secure and compliant

4.3 Process unstructured data using U-

easily using Temporal

using memory-optimized tables and natively compiled SPs

transactions

analytics into your app user R

in SQL Server and build modern apps

cloud

1.4 Develop secure and compliant database apps

1.5

customer isolation and efficiency

for greater performance and data security

apps with minimal custom code changes

schema-free data store in DocumentDB web apps

SQL

4.4 Build SQL-based cloud DW solutions to support BI

Stretch cold data to the cloud without losing query functionality

MyExpenses example

Adventure Works Bikes example

Experience 1

SQL Server 2016 – Everything built in

MyExpenses

An intelligent expense reimbursement solution Users declare work-related expenses and choose cash or corporate bucks Managers approve or deny refunds

Enable advanced features such as:  Predict expenses  Recover previous entries  Flag unusual expenses  Secure sensitive date  And more

Scenario 1.1 SQL Server –Everything built in

Recover from user errors more easily with Temporal Challenge

 

 

SQL SERVER

MyExpenses

CHALLENGE Previous entries are lost



Scenario 1.1 SQL Server –Everything built in

Recover from user errors more easily with Temporal Solution

 



MyExpenses





 TEMPORAL



Challenge

SQL Server –Everything built in

Scenario 1.2

Optimize transactional performance using memory optimized tables and natively compiled SPs

SQL SERVER

MyExpenses

On-Disk CHALLENGE Limited performance due to on-disk usage

App Dev

Solution

SQL Server –Everything built in

Scenario 1.2

Optimize transactional performance using memory optimized tables and natively compiled SPs

On-Disk SQL SERVER

In-Memory

MyExpenses



IN-MEMORY OLTP

Challenge

SQL Server –Everything built in

Scenario 1.3

Offer real-time insights into app transactions

Existing tables SQL SERVER

CHALLENGE App Dev

Slow performance from existing tables

Scenario 1.3 SQL Server –Everything built in

Offer real-time insights into app transactions Solution

Existing tables



COLUMNSTORE

App Dev C 1

C 2

C 3

Needed columns

C 4

C 5

C 6

C 7

C 8

C 9

Needed columns

C1 0

C1 1

C1 2

C13

C14

C15

Needed columns

C1 6

C1 7

C1 8

Challenges

SQL Server –Everything built in

Scenario 1.4

Develop secure and compliant database apps

CHALLENGE Encryption needed to prevent unwanted access

My Expenses

CHALLENGE Personal data not masked

RESULT SET Customer Name Philip Wegner

Product ID 152026

Region North

Order Quantity 154

Sales Amount $150

SSN 152-25-2658

Denny Usher

456826

North

889

$2,025

265-89-2587

Alicia Hodge

154865

West

598

$1,250

265-98-6495

Adam Luft

265984

South

452

$990

643-65-5967

Reed Kieser

124759

South

220

$350

265-59-9864

CHALLENGE

Alison Williams

150265

East

500

$1,600

598-84-5889

Sam Smith

486579

West

458

$800

596-57-5699

Explicit filters needed to control access

Tim Irish

568462

North

150

$650

598-26-8956

Sara Neighler

484532

South

750

$4,520

589-87-8487

Eric Endloff

546853

North

950

$10,659

895-89-2351

NORMAL USER SQL SERVER

Scenario 1.4 SQL Server –Everything built in

Develop secure and compliant database apps Row level security solution

My Expenses

QUERY

SQL SERVER



PRIVILEGED Philip Wegner USER

RESULT SET

ROW LEVEL SECURITY

RESULT SET Fname Philip

Lname Wegner

ID 152026

Expense Subscription

Cost $360

Status Approved

Philip Denny

Wegner Usher

152026 456826

Dinner

$35 $45

Denied Approved

Philip

Wegner

152026

Flight Dinner

$200 $35

Pending Denied

Philip Alicia

Wegner Hodge

152026 154865

Hotel

$125 $80

Pending

Tim

Irish

568462

Flight

$115

Approved

Alison

Williams

150265

Subscription

$600

Approved

Philip

Wegner

152026

Flight

$200

Pending

Philip

Wegner

152026

Hotel

$125

Pending

Tim

Irish

568462

Registration

$50

Approved

Scenario 1.4 SQL Server –Everything built in

Develop secure and compliant database apps Dynamic data masking solution

My Expenses

NORMAL USER

RESULT SET

QUERY



SQL SERVER

DYNAMIC DATA MASKING

RESULT SET Customer Name Philip Wegner

Product ID 152026

SSN Region Order Quantity XXX-25-XX58 North 154

Sales Amount $150

SSN 152-25-2658

Denny Usher

456826

XXX-89-XX87 North 889

$2,025

265-89-2587

Alicia Hodge

154865

XXX-98-XX95 West 598

$1,250

265-98-6495

Adam Luft

265984

XXX-65-XX67 South 452

$990

643-65-5967

Reed Kieser

124759

XXX-59-XX64 South 220

$350

265-59-9864

Alison Williams

150265

XXX-84-XX89 East 500

$1,600

598-84-5889

Sam Smith

486579

XXX-57-XX99 West 458

$800

596-57-5699

Tim Irish

568462

XXX-26-XX56 North 150

$650

598-26-8956

Sara Neighler

484532

XXX-87-XX87 South 750

$4,520

589-87-8487

Eric Endloff

546853

XXX-89-XX51 North 950

$10,659

895-89-2351

Scenario 1.4 SQL Server –Everything built in

Develop secure and compliant database apps Always encrypted solution

COLUMN MASTER KEY

My Expenses

COLUMN ENCRYTION KEY

QUERY SQL SERVER

NORMAL USER

RESULT SET

RESULT SET CLIENT SIDE



Always encrypted

CIPHERTEXT

NAME Philip Wegner Denny Usher Alicia Hodge Adam Luft Reed Kieser Alison Williams

CREDIT CARD NUMBER 1x7fg655se2e 352-2612-2655-59817 0x7ff654ae6d 4467-5426-9857-9875 0y8fj754ea2c 4368-9875-2687-2041 1x7er652se2d 359-9846-3425-68497 0x6efe65ae6n 347-9875-6985-87946 0y8fj356eb2e 4354-987-6985-6794

REGION North North West South South East

Sam Smith Tim Irish Sara Neighler

1x7fg359sd2d 357-3659-6985-69855 0x7ff654es6fd 4471-4679-9485-6495 0y3es268ea2z 4895-4896-3699-6695

West North South

Eric Endloff

1y8fj159e2dw 337-3698-5481-48962

North

Stretch cold data to the cloud without losing query functionality Challenge

COLD DATA

SQL SERVER My Expenses

App Dev

WARM + HOT DATA

CHALLENGE Cold data fills available storage space

Solution

SQL Server –Everything built in

Scenario 1.5

Stretch cold data to the cloud without losing query functionality

Azure T-SQL



My Expenses

App Dev

SQL Server

Stretch DB

Experience 2

Modernizing LOB apps with intelligence

Making an app intelligent

The continuous flow of information feeds predictive models to improve results over time

Your app

Making an app intelligent

Data-aware apps improve business processes and enhance customer experience

Business processes

Customer experiences

Fraud detection

Personalized offers

Demand forecasting

Cross-channel experiences

Supply chain management

Product recommendations

Churn prediction

Place-based offers

Scenario 2.1 Modernizing LOB apps with intelligence

Build predictive and prescriptive analytics into apps using R Challenge

Expenses data CHALLENGE

Risk of system errors

Solution

Modernizing LOB apps with intelligence

Scenario 2.1

Build predictive and prescriptive analytics into apps using R

R Services

Expenses data T-SQL

CHALLENGE

Risk of system errors

App Dev



R SERVICES

Scenario 2.2 Modernizing LOB apps with intelligence

Explore the power of JSON in SQL Server and build modern apps Challenge

All data CHALLENGE Limitations of SQL for all data types

MyExpenses

Tables SQL Server

Scenario 2.2 Modernizing LOB apps with intelligence

Explore the power of JSON in SQL Server and build modern apps Solution

Basic data

Tables SQL Server

Complex data

MyExpenses



JSON

Document DB

Documents

Column family store

Graphs

Key value stores

Challenge

Modernizing LOB apps with intelligence

Scenario 2.3

Re-architect your LOB workload for cloud

SQL Server 2016

Tedious SQL SERVER

JSON

R Services

Temporal Tables

InMemory OLTP

Always Encrypted

Columnstore Index

Row Level Security

Dynamic Data Masking

Node.js

CHALLENGE

Azure AppService

Single Page App with AngularJS

Limitations of onpremises architecture

Solutions

Modernizing LOB apps with intelligence

Scenario 2.3

Re-architect your LOB workload for cloud

Tedious

AZURE SQL DATABASE



Azure Data Services

JSON

Temporal Tables

InMemory OLTP

Always Encrypted

Row Level Security

Dynamic Data Masking

Node.js

Azure AppService

Single Page App with AngularJS

Azure ML

Columnstore Index

Experience 3

Evolving SQL workloads from software to SaaS

AdventureWorks Bikes

An ecommerce SaaS platform for bike companies Eliminate manual processes and IT maintenance Integrate management of in-store and online transactions, accounting, inventory, CRM and more

Enable advanced features such as:  Manage unpredictable usage patterns for multiple databases

 Automatically fine tune DB performance  Flag potential threats to security

Challenge

Evolving SQL workloads from software to SaaS

Scenario 3.1

Build multitenant apps with customer isolation and efficiency

CHALLENGE Need to make changes to many DBs at once

STORE 1

CHALLENGE STORE 2

STORE 3

AdventureWorks Bikes

To do: STORE 4

App Dev

Varied and unpredictable spikes in demand

Solution

Evolving SQL workloads from software to SaaS

Scenario 3.1

Build multitenant apps with customer isolation and efficiency



ELASTIC DATA POOLS

AdventureWorks Bikes

T-SQL QUERY

App Dev



ELASTIC DATABASE JOBS

Scenario 3.2 Evolving SQL workloads from software to SaaS

Leverage built-in service intelligence for greater performance and data security Challenge

CHALLENGE Code loop holes leave apps vulnerable to SQL injection

STORE 1

STORE 2

CHALLENGE

CHALLENGE

Continued need to improve performance

Serious query performance issues STORE 3

AdventureWorks Bikes

STORE 4

App Dev

Scenario 3.2 Evolving SQL workloads from software to SaaS

Leverage built-in service intelligence for greater performance and data security Solution

App Dev





QUERY PERFORMANCE INSIGHT



INDEX ADVISOR

THREAT DETECTION

High impact index detected

Scenario 3.3 Evolving SQL workloads from software to SaaS

Develop more secure and compliant apps with minimal custom code changes Challenge

App Dev STORE

App Dev

AdventureWorks Bikes

App Dev

App Dev

CHALLENGE

CHALLENGE

Many user identities for database admins

Unauthorized access to sensitive data

Scenario 3.3 Evolving SQL workloads from software to SaaS

Develop more secure and compliant apps with minimal custom code changes Solution

App Dev STORE

App Dev

App Dev

App Dev



AZURE ACTIVE DIRECTORY AdventureWorks Bikes

Routine data



Sensitive data

DYNAMIC DATA MASKING

Experience 4

Modernizing cloud-based web apps

Schema and throughput challenges

Modernizing cloud-based web apps

Scenario 4.1

Accelerate your development with schema-free data store in DocumentDB

CHALLENGE Requires complex data schema

Multiple queries

SQL SERVER AdventureWorks Bikes

Table data

Multiple table joins CHALLENGE Poor throughput

Schema and throughput solutions

Modernizing cloud-based web apps

Scenario 4.1

Accelerate your development with schema-free data store in DocumentDB



Partition A

DYNAMIC SCHEMA

One query

JSON AdventureWorks Bikes



Partition B

PARTITION SCALE Partition C

Document

Regional scalability and global replication challenges

Modernizing cloud-based web apps

Scenario 4.1

Accelerate your development with schema-free data store in DocumentDB

CHALLENGE Difficult to configure data by region

STORE 1

STORE 2

STORE 4

STORE 5

SQL SERVER AdventureWorks Bikes

STORE 3

CHALLENGE App Dev

Unable to uniformly scale to global stores

STORE 6

Regional scalability and global replication solutions

Modernizing cloud-based web apps

Scenario 4.1

Accelerate your development with schema-free data store in DocumentDB



GLOBAL DATABASE REPLICATION

Configured by region

STORE 1

STORE 2

STORE 4

STORE 5

SQL SERVER AdventureWorks Bikes

Consistency Strong Session Bounded Bounded staleness staleness Eventual

App Dev



MULTIPLE-REGION DATABASES

STORE 3

STORE 6

Challenge

Modernizing cloud-based web apps

Scenario 4.2

Build scalable search experience for your web apps

SEARCH RESULTS STORE

$$$$$$ AdventureWorks Bikes

$

CHALLENGE Search difficult to design and operationalize

$$$

App Dev $$

$$$$$$

Solution

Modernizing cloud-based web apps

Scenario 4.2

Build scalable search experience for your web apps



SCORING PROFILE

SEARCH RESULTS

AZURE SEARCH

SEARCH SCORE

NEW SEARCH RESULTS

$ $$$$$$

17%

AdventureWorks Bikes

90% $$

$

90%

$$$

56%

$$

77%

$$$$$$

11%

77% $$$

56% $$$$$$

App Dev

TEXT ANALYSIS

17% $$$$$$



LANGUAGE SUPPORT

11%

Challenge

Modernizing cloud-based web apps

Scenario 4.3

Process unstructured data using U-SQL

STORE

AdventureWorks Bikes

ANALYTICS PLATFORM

Deployment Configuration

App Dev

Tuning

CHALLENGE Analytics requires ongoing support and resources

Solution

Modernizing cloud-based web apps

Scenario 4.3

Process unstructured data using U-SQL

STORE

AdventureWorks Bikes

SQL SERVER

DATA LAKE ANAYTICS

App Dev



U-SQL QUERY LANGUAGE

Challenge

Modernizing cloud-based web apps

Scenario 4.4

Build SQL-based cloud DW solutions to support business intelligence needs

DATA WAREHOUSE Bike store Transactions

CHALLENGE Fixed data storage capacity

SQL SERVER AdventureWorks Bikes

Relational data

CHALLENGE App Dev

Limitations in storing non-relational data

AZURE BLOB STORAGE

Solution

Modernizing cloud-based web apps

Scenario 4.4

Build SQL-based cloud DW solutions to support business intelligence needs SQL DATA WAREHOUSE



FLEXIBLE STORAGE

Bike store transactions

SQL SERVER AdventureWorks Bikes

Relational data

App Dev

AZURE BLOB STORAGE

Non-relational data



POLYBASE