SQL Server 2016Everything Built-In – Technical Overview

Everything built-in

Mission-critical and

cloud performance

SQL Server2008

SQL Server2008 R2

SQL Server2000XML ● KPIs

Compression ● Policy-Based Mgmt ● Programmability

PowerPivot ● SharePoint Integration ● Master Data Services

SQL Server2012AlwaysOn ● ColumnStore Index ● Data Quality Services ● Power View ● Cloud Connectivity

Cloud-ready Self-service BI

SQL Server2014In-Memory Across Workloads ● Performance & Scale ● Hybrid Cloud Optimized ● HDInsight ● Cloud BI

Performance and productivity

Mission critical

Modern DB platform

Management Studio ● Mirroring SQL Server2005

SQL Server2016

Enhanced Always On ● In-Memory OLTP ● Stretch DB ● Temporal Tables ● Enhanced Backup to Cloud ● Polybase ● Real-Time Operational Analytics ● Row-Level Security ● Query Store ● R Services● Always Encrypted ● Mobile BI

The evolution of Microsoft SQL Server

How we develop SQL

Cloud-first but not cloud-only Use SQL Database to improve core SQL Server features and cadenceMany interesting and compelling on-premises cloud scenarios

SQL Server and APS

Azure SQL Virtual Machines

AzureSQL Database

DB

AzureSQL Data Warehouse

DW

In-Memory OLTP enhancements

Columnar index

Inmemory OLAP

Inmemory OLTP

Transact-SQL

ALTER TABLE Sales.SalesOrderDetail ALTER INDEX PK_SalesOrderID REBUILD WITH (BUCKET_COUNT=100000000)

ALTER supportFull schema change support: add/alter/drop column/constraintAdd/drop index supported

Performance

The ALTER TABLE syntax is used for making changes to the table schema, as well as for adding, deleting, and rebuilding indexesIndexes are considered part of the table definitionKey advantage is the ability to change the BUCKET_COUNT with an ALTER INDEX statement

CREATE PROCEDURE [dbo].[usp_1]WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERAS BEGIN ATOMIC WITH( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') SELECT c1, c2 from dbo.T1ENDGO

ALTER PROCEDURE [dbo].[usp_1]WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERAS BEGIN ATOMIC WITH( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') SELECT c1 from dbo.T1ENDGO

You can now perform ALTER operations on natively compiled stored procedures using the ALTER PROCEDURE statementUse sp_recompile to recompile stored procedures on the next execution

Altering natively compiled stored procedures

Performance

Transact-SQLFull support for all Collation and Unicode Support

(var)char columns can use any code page supported by SQL ServerCharacter columns in index keys can use any SQL Server collationExpressions in natively compiled modules as well as constraints on memory-optimized tables can use any SQL Server collation

Scalar User-Defined Functions for In-Memory OLTPCreate, drop, and alter natively compiled, scalar user-defined functionsNative compilation improves performance of the evaluation of UDFs in T-SQL

Performance

Cross-Feature SupportSystem-Versioned Temporal TablesQuery StoreRow-Level Security (RLS)Multiple Active Result Sets (MARS)Transparent Data Encryption (TDE)

Performance

In SQL Server 2016, the storage for memory-optimized tables will be encrypted as part of enabling TDE on the databaseSimply follow the same steps as you would for a disk-based database

Support for Transparent Data Encryption (TDE)

Windows Operating SystemLevel Data Protection

SQL ServerInstance Level

User DatabaseLevel Database Encryption

Key

Service Master Key

Transparent Database Encryption architecture

DPAPI encrypts the Service Master Key

MasterDatabase Level Database Encryption

Key

Service Master Key Encrypts the Database master Key for the master Database

Database Master Key of the master Database creates a certificate in the master database

The certificate encrypts the database Encryption Key in the user database

The entire user database is secured by the Datbase Encryption Key (DEK) of the user database by using transparent database encryption

Created at a time of SQL Server setup

Statement:CREAT MASTER KEY…

Statement:CREATE CERTIFICATE…

Statement:CREATE DATABASE ENCRYPTION KEY…

Statement:ALTER DATABSE… SET ENCRYPTION

Performance

New Transaction Performance Analysis Overview report New report replaces

the need to use the Management Data Warehouse to analyze which tables and stored procedures are candidates for in-memory optimization

Performance

Temporal TablesQuery back in time

Data changes over timeTracking and analyzing changes is often important

Temporal in DBAutomatically tracks history of data changes

Enables easy querying of historical data states

Advantages over workaroundsSimplifies app development and maintenance

Efficiently handles complex logic in DB engine

Why temporal

Time travel Data audit

Slowly changing dimensions

Repair record-level corruptions

Performance

No change in programming model

New Insights

INSERT / BULK INSERT

UPDATE

DELETE

MERGE

DML SELECT * FROM temporal

Querying

How to start with temporal

CREATE temporal TABLE PERIOD FOR SYSTEM_TIME…

ALTER regular_table TABLE ADD PERIOD…

DDL

FOR SYSTEM_TIMEAS OF FROM..TOBETWEEN..ANDCONTAINED IN

Temporal Querying

ANSI 2011 compliant

Performance

Temporal table (actual data)

Insert / Bulk Insert

* Old versions

Update */ Delete *

How does system-time work?

History table

Performance

Temporal table (actual data)

Temporal queries * (Time travel, etc.)

How does system-time work?

History table

Regular queries (current data)

* Include historical version

Performance

Always Encrypted

Data disclosure preventionClient-side encryption of sensitive data using keys that are never given to the database system

Queries on encrypted dataSupport for equality comparison, including join, group by, and distinct operators

Application transparencyMinimal application changes via server and client library enhancements

Allows customers to securely store sensitive data outside of their trust boundary.Data remains protected from high-privileged, yet unauthorized, users.

The need for Always Encrypted

Security

trust boundary

"SELECT Name FROM Customers WHERE SSN = @SSN","111-22-3333"

How it worksHelp protect data at rest and in motion, on-premises and in the cloud

NameWayne Jefferson

ADO .NET Name0x19ca706fbd9a

Result SetResult Set

Client

Name SSN Country0x19ca706fbd9a

0x7ff654ae6d USA

SQL Server or SQL Database"SELECT Name FROM Customers WHERE SSN = @SSN",0x7ff654ae6d

ciphertext

Encrypted sensitive data and corresponding keys are never seen in plaintext in SQL Server

dbo.Customers

ciphertext

Security

Randomized encryptionEncrypt('123-45-6789') = 0x17cfd50aRepeat: Encrypt('123-45-6789') = 0x9b1fcf32Allows for transparent retrieval of encrypted data but NO operationsMore secure

Deterministic encryptionEncrypt('123-45-6789') = 0x85a55d3fRepeat: Encrypt('123-45-6789') = 0x85a55d3fAllows for transparent retrieval of encrypted data AND equality comparison

E.g. in WHERE clauses and joins, distinct, group by

Two types of encryption availableRandomized encryption uses a method that encrypts data in a less predictable manner

Deterministic encryption uses a method that always generates the same encrypted value for any given plaintext value

Types of encryption for Always Encrypted

Security

Dynamic Data Masking

Configuration made easy in new Azure portal

Policy-driven at table and column level, for defined set of users

Data masking applied in real time to query results based on policy

Multiple masking functions available, such as full or partial, for various sensitive data categories (credit card numbers, SSN)

SQL DatabaseSQL Server 2016

Table.CreditCardNo4465-6571-7868-57964468-7746-3848-19784484-5434-6858-6550

Real-time data masking, partial masking

Prevent abuse of sensitive data by hiding it from users

Security

Dynamic Data Masking

Regulatory compliance A strong demand for applications to meet privacy standards recommended by regulating authorities

Sensitive data protectionProtects against unauthorized access to sensitive data in application, and against exposure to developers or DBAs who need access to production database

Agility and transparencyData is masked on the fly, with underlying data in database remaining intact (transparent to application and applied according to user privilege)

Limit access to sensitive data by defining policies to obfuscate specific database fields, without affecting database integrity

Security

Benefits of Dynamic Data Masking

Dynamic data masking walkthrough

ALTER TABLE [Employee] ALTER COLUMN [SocialSecurityNumber]ADD MASKED WITH (FUNCTION = ‘SSN()’)

ALTER TABLE [Employee] ALTER COLUMN [Email]ADD MASKED WITH (FUNCTION = ‘EMAIL()’)

ALTER TABLE [Employee] ALTER COLUMN [Salary] ADD MASKED WITH (FUNCTION = ‘RANDOM(1,20000)’) GRANT UNMASK to admin1

1) Security officer defines dynamic data masking policy in T-SQL over sensitive data in Employee table2) Application user selects from Employee table3) Dynamic data masking policy obfuscates the sensitive data in the query results

SELECT [Name], [SocialSecurityNumber], [Email], [Salary]FROM [Employee]

admin1 loginother login

Security

Security Officer

Row-Level SecuritySQL Server 2016SQL Database

Nurse Database

One

Policy manager creates a filter predicate and security policyTwo

App user (e.g., nurse) selects from Patients tableThree

Security Policy transparently rewrites query to apply filter predicate

CREATE FUNCTION dbo.fn_securitypredicate(@wing int) RETURNS TABLE WITH SCHEMABINDING AS return SELECT 1 as [fn_securitypredicate_result] FROM StaffDuties d INNER JOIN Employees e ON (d.EmpId = e.EmpId) WHERE e.UserSID = SUSER_SID() AND @wing = d.Wing;

CREATE SECURITY POLICY dbo.SecPol ADD FILTER PREDICATE dbo.fn_securitypredicate(Wing) ON Patients WITH (STATE = ON)

FilterPredicate:

INNER JOIN…

SecurityPolicy

Application

Patients

SELECT * FROM Patients

SELECT * FROM Patients SEMIJOIN APPLY dbo.fn_securitypredicate(patients.Wing);

SELECT Patients.* FROM Patients, StaffDuties d INNER JOIN Employees e ON (d.EmpId = e.EmpId) WHERE e.UserSID = SUSER_SID() AND Patients.wing = d.Wing;

Security

RLS in three steps

Policy Manager

-- The following syntax creates a security policy with a filter predicate for the Customer table, and leaves the security policy disabledCREATE SECURITY POLICY [FederatedSecurityPolicy]

ADD FILTER PREDICATE [rls].[fn_securitypredicate]([CustomerId])

ON [dbo].[Customer];

-- Create a new schema and predicate function, which will use the application user ID stored in CONTEXT_INFO to filter rows.CREATE FUNCTION rls.fn_securitypredicate (@AppUserId int)     

RETURNS TABLE     WITH SCHEMABINDING

AS     RETURN (SELECT 1 AS fn_securitypredicate_result     WHERE         

DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('dbo') -- application context         

AND CONTEXT_INFO() = CONVERT(VARBINARY(128), @AppUserId); GO

Creates security policy forrow-level security

The following examples demonstrate use of CREATE SECURITY POLICY syntax

For an example of a complete security policy scenario, see Row-Level Security

Create security policy

Security

CapabilityRow-Level Security provides fine-grained access control over rows in a table based on conditions you set up

BenefitsStore data for many users in same databases and tables while limiting access by other users who share same tables

Security

Summary: Row-Level Security

Security enhancementsBuilt-in tools for enabling compliance: SQL Server audit toolsCreate server audits, with server audit specifications (audited events can be written to event logs or to audit files)

User-defined audit: Allows middle-tier application to write custom events into audit log, which enables more flexibility to store audit information

Audit filtering: Provides greater flexibility to filter wanted events in audit log

Audit resilience: Audit logging is now tolerant to loss of connectivity to target directory and will recover automatically once network connection is re-established

Security

Azure Key Vault supportAzure Key Vault:

Central key management that leverages hardware security modules (HSMs), separation of key management from data management

Support for AKV available through SQL Server Connector for AKV

Extensible Key Management (EKM) provider for SQL ServerLeverage Azure Key Vault for managing encryption keysBoth on-premises and SQL Server-in-a-VM users can assume control of encryption keys for Transparent Data Encryption (TDE), Column Level Encryption (CLE), and Backup Encryption while leveraging additional security benefits of Azure Key Vault

Security

Transparent Data Encryption (TDE)Encrypt data when it is stored on disk, and decrypt it when read into memory

Developers can encrypt database files, log files, and backup files without changing existing applications

Intel AES-NI hardware encryption acceleration

Support for storage of memory-optimized OLTP tables (new)

Exclusive to SQL Server Enterprise edition

Security

Transparent Data Encryption (TDE)Encrypt backup by specifying encryption algorithm and encryptorSupports on-premises and Azure storage locationsConfigurable for Managed Backup to Windows Azure

Backup encryption now supported with compression, using AES-NI hardware acceleration

Security

Summary: SecurityAlways encrypted: Sensitive data always encrypted (and queryable)Dynamic Data Masking: Real-time obfuscation of dataRow-Level Security: Fine-grained access control of table rows Audit success/failure of database operationsTDE support for storage of In-Memory OLTP tablesEnhanced auditing for OLTP with ability to track history of record changesSecurity

AlwaysOn

Failover on SQL Server instance level

Shared storage (SAN/SMB) Failover can take minutes based

on load Multi-node clustering Passive secondary nodes

Failover on database level Direct attached storage Failover takes seconds Multiple secondaries Active secondaries

Availability

Failover Cluster Instances

for servers

Availability Groupsfor groups of databases

WSFC node

WSFC node

Failover Cluster Instances

Server failoverShared storageMulti-node clusteringPassive secondary nodesFailover in minutes

SQL Server 2016

Shared storage

SQL Server 2016SQL Server Failover Cluster Instance

Availability

Availability Group

Listener

Availability Groups

Multi-database failoverDirect attached storageMultiple secondariesActive secondariesFailover in seconds

SQL Server 2016

SQL Server 2016SQL Server 2016

Availability

Availability Groups + Failover Clustering

AlwaysOn: Failover Cluster Instances and Availability Groups work together to ensure data is accessible despite failures

Availability

Windows Server Failover Clustering (WSFC) ClusterNetwork Subnet Network Subnet

Node NodeNodeNodeNode

SQL ServerInstance

SQL ServerInstance

SQL ServerInstance

AlwaysOn SQL ServerFailover Cluster Instance

Primary Replica Secondary Replica Secondary Replica Secondary Replica

AlwaysOn Availability Group

InstanceNetwork Name

WSFCConfiguration

WSFCConfiguration

WSFCConfiguration

WSFCConfiguration

WSFCConfiguration

InstanceNetwork Name

InstanceNetwork Name

InstanceNetwork Name

Availability Group Listener Virtual Network Name

Storage Storage Storage Share Storage

PolyBase for SQL Server 2016

Interest in big data spurs customer demand

Adoption of big data technologies like Hadoop

Increase in number and variety of data sources that generate large quantities of data

Realization that data is “too valuable” to delete

Dramatic decline in hardware cost, especially storage

$

PolyBase and queries

RDBMS Hadoop

Provides a scalable, T-SQL-compatible query processing framework for combining data from

both universes

PolyBase

Access any data

PolyBase ViewExecute T-SQL queries against relational data in SQL Server and semi-structured data in Hadoop or Azure Blob Storage

Leverage existing T-SQL skills and BI tools to gain insights from different data stores

SQL Server

Hadoop Azure Blob Storage

Query Results

Access any data

PolyBase View in SQL Server 2016

Load data

Use Hadoop as an ETL tool to cleanse data before loading to data warehouse with PolyBase

Interactively query

Analyze relational data with semi-structured data using split-based query processing

Age out data

Age out data to HDFS and use it as “cold” but queryable storage

Access any data

PolyBase use cases

Components introduced in SQL Server 2016PolyBase Engine Service

PolyBase Data Movement Service (with HDFS Bridge)

External table constructs

MR pushdown computation support

Access any data

Head Node

SQL 2016

PolyBase Engine

PolyBase DMS

Step 1: Set up a Hadoop cluster…

Hortonworks or Cloudera distributionsHadoop 2.0 or above

Linux or WindowsOn-premises or in Azure

Access any data

Hadoop ClusterNamenode

Datanode

Datanode

Datanode

Datanode

File System

AB 01 01 01 01

File System

File System

File System

Step 1: …or set up an Azure Storage Blob

Azure Storage Blob (ASB) exposes an HDFS layerPolyBase reads and writes from ASB using Hadoop

RecordReader/RecordWriteNo compute pushdown support for ASB

Access any data

Azure

Azure Storage Volume

Azure Storage Volume

Azure Storage Volume

Access any data

Query relational and non-relational data, on-premises

and in Azure

T-SQL query

CapabilityT-SQL for querying relational and non-relational data across SQL Server and Hadoop

BenefitsNew business insights across your data lakeLeverage existing skill sets and BI toolsFaster time to insights and simplified ETL process

Apps

SQL Server Hadoop

Summary: PolyBaseQuery relational and non-relational data with T-SQL

Enterprise-grade Analysis Services

Easily create powerful modelsUse SSAS as semantic model onlyImprove manageability and securityStrengthen multidimensional support

Scale and manage

Easy access to

insights

Increasedproductivity

Faster

time

to va

lue

Analysis Services themes for SQL Server 2016Improved productivity and performance

Analysis Services OverviewAnalysis Services (SSAS) is an online analytical data engine used in decision support and business analytics

It provides analytical data for business reports and client applications such as Reporting Services reports, Power BI, Excel, and other third-party data visualization tools

SSAS supports two modelling modes—multidimensional and tabularThe multidimensional mode also includes a data mining engine

Analysis Services OverviewOne Semantic Model - Two Ways to Develop

Dimensions and measure groupsHighly scalable and matureFeature rich and complex

Multidimensional models

Tables and relationshipsFast by design with in-memoryEasy to get started and simple

Tabular models

Analysis Services Architectural Overview

BI SEMANTIC MODEL

Data access

Use SQL Server Data Tools for Visual Studio to create BI semantic models

DATA SOURCES

CLIENT TOOLS

Cloud services

Excel3rd party applications

SSRS paginated reports

Relational databases

SSRS mobile reports

LOB applications Analytics Platform System

PowerBI.comPower BI Desktop

MOLAP In-memory DirectQuery

Data model TabularMultidimensional

DAXBusiness logic MDX

Queries MDX/DAX (all model types)

ROLAP

Reporting Services OverviewReporting Services (SSRS) delivers enterprise, web-enabled reporting functionality

Implemented as a Report Server, in one of two modes—Native and SharePoint—it can scale to support thousands of users

SSRS 2016 is extended with a new capability to deliver mobile reports, and is enhanced with a new modern web portalTo contrast classic RDL reports with the new mobile reports, SSRS reports are now referred to as either paginated or mobile

Reporting Services architecture

Web APIs

Report server database

Web portalWeb browsers

Power BI Mobile

Report Builder

Mobile Report Publisher

Visual Studio

SQL Server SQL ServerAnalysis Services

Oracle Teradata

ODBC, OLE DB More/custom

Processing andrendering

Scheduling anddelivery

Email File share

SharePoint Power BI

Custom

REPORT VIEWING (PCs, mobile)

REPORT DESIGN (PCs)

REPORT SERVER DATA SOURCES

REPORT DELIVERY DESTINATIONS

Custom apps/LOB

SQL Server 2016Core investment areas in Reporting Services & Analysis Services

Enhanced analysis

Modern reports

Rich tools

Mobile reports

Hybrid BI SharePoint 2016 support

New SSRS FeaturesSupports the current versions of Microsoft.NET Framework 4This includes 4.0 and 4.5.1If no version of .NET Framework 4.x is installed, SQL Server setup installs .NET 4.0 during the feature installation

Render paginated reports as PowerPoint filesText boxes and images render as PowerPoint text boxes and imagesData visualizations render as imagesThe PowerPoint slide layout can be adjusted to suit, by moving and resizing individual objects

New SSRS FeaturesAbility to deliver subscriptions by using a single file share account, reusable across multiple subscriptions

Pin report items to Power BI dashboardsRequires registering the Report Server with Power BICan pin charts, gauge panels, maps and images, and configure how often the data is refreshedDashboard tile refresh is achieved with subscriptionsSupported by all SQL Server editions

Support of SharePoint mode for SharePoint 2016

New SSRS FeaturesPaginated report authoring:Custom parameter pane, allowing design-time control over report parameter layoutTwo new chart types: Tree Map and Sunburst charts, which effectively visualize hierarchical data

Report BuilderNow has a new modern look and feel, with streamlined UI elementsSupports High DPI (Dots Per Inch) scaling and devices

New SSRS FeaturesUpdated data source support:Oracle Database 12c (uses ODP.NET)Oracle Essbase 11SAP BW 7.5Teradata 15

Personalized connection strings:Enables data sources to pass current user name as custom data

Data Source=localhost;Initial Catalog=Sales Analysis;CustomData={{ UserID }}

New SSRS FeaturesKey Performance Indicators (KPIs):Deliver visual cues that communicate the amount of progress made toward a goalTypically valuable for teams, managers, and businesses by enabling them to quickly evaluate the progress made toward measurable goalsDeveloped in, and displayed by, the new SSRS web portalSupported by Enterprise edition, and Native mode only

New SSRS FeaturesKey Performance Indicators (KPIs):Enable defining value, goal, status and trend metrics

The value, goal and status metrics are single values—typically numeric, though text is also possibleThe trend metric is designed to use a time series (e.g. monthly values)

Values can be sourced from shared datasets, or by manually entered valuesDatasets can be parameterizedDatasets must use cachingCache plans should be used to schedule the refresh

Can be configured to launch related content—either a mobile report, or a custom URLThe status colors—by default—are green (1), amber (0), or red (-1), and these can be overridden by custom branding

New SSRS FeaturesThe web portal is a new HTML5 application that targets modern web standards and modern browsersReport Manager has been removed and the web portal is now the default used to browse/manage/consume the Report Server catalogStores, manages and secures reports and related assets:

Paginated reportsMobile reportsKey Performance Indicators (KPIs)Power BI Desktop filesExcel workbooks

New SSRS FeaturesSupports custom branding, allowing the customization of logo, web portal colors and a mobile report theme

New SSRS FeaturesPDF replaces ActiveX for remote printing (plug-in free printing)New enhanced subscription management:

Easily enable/disableProvide a descriptionUser can change the owner

Create interactive mobile reportsResponsive experiences for phones and tablets

Drag-and-drop to create beautiful, interactive data visualizationsPrototype in minutes with simulated dataAccess enterprise data sources by connecting to Reporting ServicesCustomize layouts for different form factors

Consume mobile reportsNative apps for all major mobile platforms

One mobile app for Power BI and SSRS mobile reportsNative mobile experienceoptimized for touchKeep data up-to-date with real-time query or scheduled data refreshInteract with mobile reports even when disconnected

Windows HTML5iOS Android

R integration

Revolution R Enterprise and SQL

Big data analytics platform Based on open source R

High-performance, scalable, full-featuredStatistical and machine-learning algorithms are performant, scalable, and distributable

Write once, deploy anywhereScripts and models can be executed on a variety of platforms, including non-Microsoft (Hadoop, Teradata in-DB)

Integration with the R EcosystemAnalytic algorithms accessed via R function with similar syntax for R users (with arbitrary R functions/packages)Advanced analytics

Enhanced R interpreter

Data sourceintegration

Parallel external-memoryalgorithm library

Data

Compute context integration

Resources

RequestsR scripts +

CRANalgorithms

SQL Server 2016 R integration scenario

ExplorationUse Revolution R Enterprise (RRE) from R integrated development environment (IDE) to analyze large data sets and build predictive and embedded models with compute on SQL Server machine (SQL Server compute context)

OperationalizationDeveloper can operationalize R script/model over SQL Server data by using T-SQL constructsDBA can manage resources, plus secure and govern R runtime execution in SQL Server

Advanced analytics

R script usage from SQL ServerOriginal R script:IrisPredict <- function(data, model){library(e1071)predicted_species <- predict(model, data)return(predicted_species)

}  library(RODBC)conn <- odbcConnect("MySqlAzure", uid = myUser, pwd = myPassword);Iris_data <-sqlFetch(conn, "Iris_Data");Iris_model <-sqlQuery(conn, "select model from my_iris_model");IrisPredict (Iris_data, model);

Calling R script from SQL Server:/* Input table schema */create table Iris_Data (name varchar(100), length int, width int);/* Model table schema */create table my_iris_model (model varbinary(max)); declare @iris_model varbinary(max) = (select model from my_iris_model);exec sp_execute_external_script @language = 'R', @script = 'IrisPredict <- function(data, model){library(e1071)predicted_species <- predict(model, data)return(predicted_species)

}IrisPredict(input_data_1, model);', @parallel = default, @input_data_1 = N'select * from Iris_Data', @params = N'@model varbinary(max)', @model = @iris_modelwith result sets ((name varchar(100), length int, width int, species varchar(30)));

Values highlighted in yellow are SQL queries embedded in the original R scriptValues highlighted in aqua are R variables that bind to SQL variables by name

Advanced analytics

CapabilityExtensible in-database analytics, integrated with R, exposed through T-SQLCentralized enterprise library for analytic models

BenefitsNo data movement, resulting in faster time to insightsReal-time analytics on transactional dataIntegration with existing workflowsUnified governance across analytics and storage

SQL Server

Analytical enginesIntegrate with RBecome fully extensible

Data management layerRelational data Use T-SQL interfaceStream data in-memory

Analytics libraryShare and collaborateManage and deploy

R +

Data Scientists

Business Analysts

Publish algorithms, interact directly with data

Analyze through T-SQL, tools, and vetted

algorithms

DBAsManage storage and analytics together

Summary: R integration and advanced analytics

Advanced analytics

Stretch Database

Ever-growing data, ever-shrinking IT

What to do?Expand server and storageMove data elsewhereDelete

Massive tables (hundreds of millions/billions of rows, TBs size)Users want/need to retain data indefinitelyCold data infrequently accessed but must be onlineDatacenter consolidationMaintenance challengesBusiness SLAs at risk

Hybrid solutions

CapabilityStretch large operational tables from on-premises to Azure with the ability to query

BenefitsCost-effective online cold dataEntire table is online and remains queryable from on-premises appsNo application changesSupport for Always Encrypted andRow-Level SecurityStretching history tables of Temporal Tables a great scenario

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

SQLSERVER

2016

Azure

Hybrid solutions

Stretch Database architectureHow it worksCreates a secure linked server definition in the on-premises SQL ServerTargets remote endpoint with linked server definitionProvisions remote resources and begins to migrate eligible data, if migration is enabledQueries against tables run against both local database and remote endpoint

Remote endpoint

Remote data

On-premises instance

Azure

Internet boundary

Local database

Local data

Eligible data

Linked servers

Hybrid solutions

Queries continue working

Business applications continue working without disruptionDBA scripts and tools work as before (all controls still held in local SQL Server)Developers continue building or enhancing applications with existing tools and methodsOrders_History

Orders

Orders_History

Hybrid solutions

Advanced security features supported

Data in motion always via secure channels (TLS 1.1/1.2)Always Encrypted supported ifenabled by user (encryption key remains on-premises)Row-Level Security and Auditing supported

Hybrid solutions

Orders_History

Orders

Orders_History

Backup and restore benefits

Hybrid solutions

DBAs only back up/restore local SQL Server hot dataStretchDB ensures remote data is transactionally consistent with localUpon completion of local restore, SQL Server reconciles with remote using metadata operation, not data copyTime of restore for remote not dependent on size of data

Orders_History

Orders

Orders_History

Auto-reconcile

Order history    Name SSN DateJane Doe cm61ba906f

d2/28/200

5Jim Gray ox7ff654ae6

d3/18/200

5John Smith i2y36cg776r

g4/10/200

5Bill Brown nx290pldo9

0l4/27/200

5Sue Daniels ypo85ba616

rj5/12/200

5Sarah Jones bns51ra806f

d5/22/200

5Jake Marks mci12hh906

fj6/07/200

5Eric Mears utb76b916gi 6/18/201

4Rachel Hogan px61hi9306f

j 7/1/2014

Sam Johnson ol43bi506gd 7/12/2014

David Simon tx83hal916fi 7/29/2014

Michelle Burns nb95re926gi 8/10/2014

Reed Dean vc61ira536fe

8/23/2014

Order history    Name SSN DateJane Doe cm61ba906f

d2/28/200

5Jim Gray ox7ff654ae6

d3/18/200

5John Smith i2y36cg776r

g4/10/200

5Bill Brown nx290pldo9

0l4/27/200

5

Customer data

Product data

Order History

Stretch to cloud

CapabilityStretch cold database tables from on-premises SQL Server databases to Azure with remote query processing

BenefitsCost-effective historical dataEntire table is online and remains queryable from on-premises appsTransparent to applicationsSupport for Always Encrypted and Row-Level SecuritySQL Server App

Query

Microsoft Azure

Jim Gray ox7ff654ae6d

3/18/2005

Summary: Stretch SQL Server into Azure

Hybrid solutions