21 de Outubro \\Microsoft Lisbon Experience SQL Server 2016 New innovations César Mendes cmendes@microsoft.com Partner T echnical Consultant Speed… [605608]

Data Platform Airlift
21 de Outubro \\Microsoft Lisbon Experience
SQL Server 2016
New innovations
César Mendes
[anonimizat]
Partner T echnical Consultant

Speed
Source: IDC & Microsoft, April 2014Data differentiates today's leading companies

Operational
Database
Management
Systems
Data
Warehouse
Database
Management
Systems
Business
Intelligence
and Analytics
Platforms
x86 Server
Virtualization
Cloud
Infrastructure
as a Service
Enterprise
Application
Platform as a
Service
Public Cloud
Storage
Leader in 2014 for Gartner Magic QuadrantsMicrosoft platform leads the way on -premises and cloud

Do more. Achieve more.

Performance Security Availability Scalability
Operational analytics
Insights on operational data;
Works with in -memory OLTP and
disk-based OLTP
In-memory OLTP
enhancements
Greater T -SQL surface area,
terabytes of memory supported,
and greater number of parallel
CPUs
Query data store
Monitor and optimize query plans
Native JSON
Expanded support for JSON data
Temporal database
support
Query data as points in time Always encrypted
Sensitive data remains encrypted
at all times with ability to query
Row-level security
Apply fine -grained access control
to table rows
Dynamic data masking
Real-time obfuscation of data to
prevent unauthorized access
Other enhancements
Audit success/failure of database
operations
TDE support for storage of in –
memory OLTP tables
Enhanced auditing for OLTP with
ability to track history of record
changesEnhanced AlwaysOn
Three synchronous replicas for
auto failover across domains
Round robin load balancing of
replicas
Automatic failover based on
database health
DTC for transactional integrity
across database instances with
AlwaysOn
Support for SSIS with AlwaysOnEnhanced database
caching
Cache data with automatic,
multiple TempDB files per instance
in multi -core environmentsMission -critical performance

In-memory OLTP
enhancements

ALTER TABLE Sales.SalesOrderDetail
ALTER INDEX PK_SalesOrderID
REBUILD
WITH (BUCKET_COUNT=100000000)
T-SQL surface area: New
{LEFT|RIGHT} OUTER JOIN
Disjunction (OR, NOT)
UNION [ALL]
SELECT DISTINCT
Subqueries (EXISTS, IN, scalar)ALTER support
Full schema change support: add/alter/drop
column/constraint
Add/drop index supported
Surface area improvements
Almost full T -SQL coverage including scaler user -defined
functions
Improved scaling
Increased size allowed for durable tables; more sockets
Other improvements
MARS support
Lightweight migration reports

CREATE PROCEDURE [ dbo].[usp_1]
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE =
N'us_english '
)
SELECT c1, c2 from dbo.T1
END
GO
ALTER PROCEDURE [ dbo].[usp_1]
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE =
N'us_english '
)
SELECT c1 from dbo.T1
END
GOYou can now perform ALTER
operations on natively compiled
stored procedures using the
ALTER PROCEDURE statement
Use sp_recompile to
recompile stored procedures on
the next executionAltering natively compiled stored procedures

Data Source=MSSQL; Initial Catalog= AdventureWorks ;
Integrated Security=SSPI;
MultipleActiveResultSets =TrueSetup MARS connection for
memory optimized tables using
the
MultipleActiveResultsSets
=True in your connection stringUsing multiple active result sets (MARS)

Query Store
Y our flight data recorder
for your database

Have Y ou Ever …?
…had your system down/slowed down and everyone waiting for you to
magically fix the problem ASAP?
…upgraded an application to the latest SQL Server version
and had an issue with a plan change slowing your application down?
…had a problem with your Azure SQL Database and been unable to
determine what was going wrong?

With Query Store…
I CAN get full history of query execution
I CAN quickly pinpoint the most expensive queries
I CAN get all queries that regressed
I CAN easily force better plan from history with a single line of T -SQL
I CAN safely do server restart or upgrade

Durability latency controlled by DB option
DATA_FLUSH_INTERNAL_SECONDSCompile
ExecutePlan Store
Runtime
Stats
Query
Store
SchemaQuery data store
Collects query texts (+ all relevant properties)
Stores all plan choices and performance
metrics
Works across restarts / upgrades / recompiles
Dramatically lowers the bar for perf.
Troubleshooting
New Views
Intuitive and easy plan forcing

Query Store
Y our flight data recorder
for your database
Demo

Monitoring Performance By Using the Query Store
The query store feature
provides DBAs with
insight on query plan
choice and performance

Java Script Object
Notation (JSON)

[
{
"Number":"SO43659",
"Date":"2011 -05-31T00:00:00"
"AccountNumber":"AW29825",
"Price":59.99,
"Quantity":1
},
{
"Number":"SO43661",
"Date":"2011 -06-01T00:00:00“
"AccountNumber":"AW73565“,
"Price":24.99,
"Quantity":3
}
]Number Date Customer Price Quantity
SO43659 2011 -05-31T00:00:00 AW29825 59.99 1
SO43661 2011 -06-01T00:00:00 AW73565 24.99 3SELECT * FROM myTable
FOR JSON AUTO
SELECT * FROM
OPENJSON (@json)Data exchange with JSON

No new data type
If you need to store it raw, store it as NVARCHAR
What is new:
Easy export: FOR JSON
Easy import: OPENJSON
Easy handling: ISJSON, JSON_VALUEHow to handle JSON?

OPENJSON
OPENJSON (@json,N'$.Orders.OrdersArray' )
WITH (
Number varchar(200)N'$.Order.Number',
Date datetime N'$.Order.Date',
Customer varchar(200)N'$.AccountNumber',
Quantity int N'$.Item.Quantity'
){"Orders": { "OrdersArray ":
[
{
"Order": {
"Number":"SO43659" ,
"Date":"2011 -05-31T00:00:00“
},
"AccountNumber":"AW29825“ ,
"Item": {
"Price":2024.9940,
"Quantity":1
}
},
{
"Order":{
“Number":"SO43661" ,
"Date":" 2011-06-01T00:00:00 “
},
"AccountNumber":"AW73565“ ,
"Item": {
"Price":2024.9940,
"Quantity":3
}
}
]} }Number Date Customer Quantity
SO43659 2011 -05-31T00:00:00 AW29825 1
SO43661 2011 -06-01T00:00:00 AW73565 3

T emporal
Query back in time

Real data sources are dynamic
Historical data may be critical to business success
Traditional databases fail to provide required insights
Workarounds are…
Complex , expensive, limited, inflexible, inefficient
SQL Server 2016 makes life easy
No change in programming model
New InsightsWhy T emporal
Time Travel
Data Audit
Slowly Changing
Dimensions
Repair record -level
corruptions

Temporal table (actual data)
Insert / Bulk Insert* Old versions
Update */ Delete *
How system -time works?
History Table

Temporal table (actual data)
Temporal Queries *
(Time travel,etc.)
How system -time works?
History Table
Regular queries
(current data)* Include Historical
Version

Dynamic Data Masking
Always Encrypted
Row Level Security
@14h00 / Data Platform Track
SQL Server 2016 Security -3 wishes
were satisfied
Luís Canastreiro

Enhanced
AlwaysOn

Greater scalability:
Load balancing readable secondaries
Increased number of auto -failover targets
Log transport performance
Improved manageability:
DTC support
Database -level health monitoring
Group managed service accountAG_Listener
New York
(Primary)
Asynchronous data
Movement
Synchronous data
MovementUnified HA SolutionEnhanced AlwaysOn Availability Groups
AGHong Kong
(Secondary)AG
New Jersey
(Secondary)AG

Scalability
improvements

Supports caching data with automatic, multiple TempDB
files per instance in multi -core environments
Reduces metadata and allocation contention for TempDB
workloads, improving performance and scalability
By default, setup adds as many tempdb files as the CPU
count or 8, whichever is lowerEnhanced database caching
setup.exe /Q /ACTION="INSTALL" /IACCEPTSQLSERVERLICENSETERMS /FEATURES=" SqlEngine "
/INSTANCENAME="SQL15" .. /SQLTEMPDBDIR="D: \tempdb" /SQLTEMPDBFILECOUNT="4"

Live Query Statistics
Live query metrics

Live query statistics
Collect actual metrics about query while running
View CPU/memory usage, execution time, query
progress, etc.
Enables rapid identification of potential
bottlenecks for troubleshooting query
performance issues.
Allows drill down to live operator level statistics:
Number of generated rows
Elapsed time
Operator progress
Live warnings, etc.

Live Query statistics
Demo

Access any data Scale and manage Powerful Insights Advanced analytics
PolyBase
Insights from data across SQL
Server and Hadoop with simplicity
of T-SQL
Enhanced SSIS
Designer support for previous SSIS
versions
Support for Power QueryEnterprise -grade
Analysis Services
Enhanced performance and
scalability for analysis services
Single SSDT in Visual Studio
2015 (CTP3)
Build richer analytics solutions as
part of your development projects
in Visual Studio
Enhanced MDS
Excel add -in15x faster; m ore
granular security roles; a rchival
options for transaction logs; and
reuse entities across modelsMobile BI
Business insights for your on –
premises data through rich
visualization on mobile devices
with native apps for Windows, iOS
and Android
Enhanced Reporting
Services
New modern reports with rich
visualizationsR integration (CTP3)
Bringing predictive analytic
capabilities to your relational
database
Analytics libraries (CTP3)
Expand your “R” script library with
Microsoft Azure MarketplaceDeeper insights across data

PolyBase for
SQL Server 2016

PolyBase
Query relational and non -relational data with T -SQL
T-SQL query
SQL Server HadoopQuote:
************************
**********************
*********************
**********************
***********************
$658.39
Jim GrayName
11/13/58DOB
WAState
Ann Smith 04/29/76 ME

–Run sp_configure ‘hadoop connectivity’
–and set an appropriate value
sp_configure
@configname = 'hadoop connectivity',
@configvalue = 7;
GO
RECONFIGURE
GO
–List the configuration settings for
–one configuration name
sp_configure @configname ='hadoop connectivity ';
GOOption values
0: Disable Hadoop connectivity
1: Hortonworks HDP 1.3 on Windows Server
Azure blob storage (WASB[S ])
2: Hortonworks HDP 1.3 on Linux
3: Cloudera CDH 4.3 on Linux
4: Hortonworks HDP 2.0 on Windows Server
Azure blob storage (WASB[S])
5: Hortonworks HDP 2.0 on Linux
6: Cloudera 5.1 on Linux
7: Hortonworks 2.1 and 2.2 on Linux
Hortonworks 2.2 on Windows Server
Azure blob storage (WASB[S]) Choose Hadoop data source with sp_configure

Start the PolyBase services
After running for sp_configure ,
you must stop and restart the
SQL Server engine service
Run services.msc
Find the services shown below and
stop each one
Restart the services

–Using credentials on database requires enabling
–traceflag
DBCC TRACEON(4631, -1)
–Create a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';
CREATE CREDENTIAL WASBSecret ON DATABASE WITH
IDENTITY = 'pdw_user ', Secret = ' mykey==';
–Create an external data source (Azure Blob Storage)
–with the credential
CREATE EXTERNAL DATA SOURCE Azure_Storage WITH
(TYPE = HADOOP,
LOCATION
='wasb[s]://mycontainer@test.blob.core.windows.net/pat
h’,
CREDENTIAL = WASBSecret
)Type methods for providing
credentials
Core -site.xml in installation path of
SQL Server –
<SqlBinRoot >\Polybase \Hadoop \Conf
Credential object in SQL Server for
higher security
NOTE: The syntax for a database -scoped
credential (CREATE CREDENTIAL … ON
DATABASE) is temporary and will change
in the next release. This new feature is
documented only in the examples in the
CTP2 content, and will be fully
documented in the next release.Configure PolyBase for Azure blob storage

–Create an external data source (Hadoop)
CREATE EXTERNAL DATA SOURCE hdp2 with (
TYPE = HADOOP,
LOCATION =' hdfs://10.xxx.xx.xxx:xxxx',
RESOURCE_MANAGER_LOCATION='10.xxx.xx.xxx:xxxx')CTP2 supports the following
Hadoop distributions
Hortonworks HDP 1.3, 2.0, 2.1, 2.2 for
both Windows and Linux
Cloudera CDH 4.3, 5.1 on LinuxCreate a reference to a Hadoop cluster

SELECTDISTINCT C.FirstName , C.LastName ,
C.MaritalStatus
FROMInsurance_Customer_SQL
INNERJOIN(
SELECT*FROMSensorData_ExternalHDP WHERE
Speed>35
UNIONALL
SELECT*FROMSensorData_ExternalHDP2 WHERE
Speed>35
)ASSensorD
ONC.CustomerKey =SensorD.CustomerKeyExternal tables
referring to data
in 2 HDP Hadoop
clustersSQL Server tableQuery Capabilities
Joining relational and external data

Enterprise grade
Analysis Services

Scale your tabular
models with support
for high end servers
More memory
More coresEnhanced Analysis Services
Deliver high performance and scalability for your BI solutions
High -end server hardware
Capability
Parallel partition processing
NUMA optimization for tabular models
On-demand loading and paging
Tabular and MOLAP modeling enhancements
Detect MOLAP index corruption using DBCC
Benefits

Enhanced
Reporting
Services

Modern reports with SQL Server Reporting Services
Report consumption from
modern browsers
Improved parameters
Modern themes
Internet Explorer Firefox Safari Chrome Edge

Modern reports with SQL Server Reporting Services
New chart types
•Tree Map Chart
•Sunburst Chart

SQL Server Reporting Services –What’s new?
Support to .NET Framework 4
New Report Builder User Interface
HTML 5 Rendering Engine
PowerPoint Rendering andExport

R integration with
database engine

Example Solutions
Fraud detection
Sales forecasting
Warehouse efficiency
Predictive maintenanceExtensibility
Microsoft Azure
Machine Learning MarketplaceNew R scripts
010010
100100
010101010010
100100
010101010010
100100
010101010010
100100
010101Built-in advanced analytics (CTP3)
In-database analytics
Built-in to SQL ServerAnalytic Library
T-SQL Interface
Relational Data010010
100100
010101
010010
100100
010101Data Scientist
Interact directly
with data
Data Developer/DBA
Manage data and
analytics together
?RR Integration

Hybrid solutions Simplicity Consistency
Stretch Database
Stretch operational tables in a secure manner
into Azure for cost effective historic data
availability works with Always Encrypted and
Row Level Security
Power BI with on -premises data
New interactive query with Analysis Services.
Customer data stays behind your firewall
Hybrid Scenarios with SSIS
Azure Data Factory integration with SSIS,
package lineage and impact analysis and
connect SSIS to cloud data source
Enhanced Backup to Azure
Faster restore times and 50% reduction in
storage, support larger DBs with Block blobs
and custom backup schedule with local stagingEasy migration of on -premises SQL
Server
Simple point and click migration to Azure
Simplified Add Azure
Replica Wizard
Automatic listener configuration for AlwaysOn
in Azure VMsCommon development, management
and identity tools
Including Active Directory, Visual Studio, Hyper –
V and System Center
Consistent Experience from SQL
Server on -premises to Microsoft
Azure IaaS and PaaSDeeper insights across data

Stretch Database

Order history
Name SSN Date
Jane Doe cm61ba906fd 2/28/2005
Jim Gray ox7ff654ae6d 3/18/2005
John Smith i2y36cg776rg 4/10/2005
Bill Brown nx290pldo90l 4/27/2005
SueDaniels ypo85ba616rj 5/12/2005
Sarah Jones bns51ra806fd 5/22/2005
Jake Marks mci12hh906fj 6/07/2005Order history
Name SSN Date
Jane Doe cm61ba906fd 2/28/2005
Jim Gray ox7ff654ae6d 3/18/2005
John Smith i2y36cg776rg 4/10/2005
Bill Brown nx290pldo90l 4/27/2005Customer data
Product data
Order HistoryStretch to cloudStretch SQL Server into Azure
Stretch warm and cold tables to Azure with remote query processing
AppQueryMicrosoft Azure
Always E ncryptedJim Gray ox7ff654ae6d 3/18/2005

Simplified
AlwaysOn with
replicas on Azure

Today this requires manually
configuring the Listener
SQL Server 2016Simplified Add Azure Replica Wizard
Automatic Listener Configuration

The Microsoft
data platform
Internal &
externalDashboards Reports Ask Mobile
Information
management OrchestrationExtract, transform,
load Prediction
Relational Non-relational AnalyticalApps
Streaming

© 2015 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trad emarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a comm itment on
the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this present ation.MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
César Mendes
cmendes@microsoft.com

Free Azure
Trial
Try SQL Server
2016 CTP2
http://aka.ms/trysql2016
http://aka.ms/tryazure
Use Power BI for Free
http://powerbi.microsoft.com

© 2015 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trad emarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a comm itment on
the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this present ation.MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.