Transcript SQL Server 2016

SQL Server Evolution Overview
Myles Matheson and Scott Klein
M211
The Evolution of Microsoft Data Platform
XML ● KPIs
SQL Server
2000
Management Studio ● Mirroring
SQL Server
2005
Compression ● Policy-Based Mgmt ● Programmability
SQL Server
2008
PowerPivot ● SharePoint Integration ● Master Data Services
SQL Server
2008 R2
AlwaysOn ● ColumnStore Index ● Data Quality Services ● Power View ● Cloud Connectivity
SQL Server
2012
In-Memory Across Workloads ● Performance & Scale ● Hybrid Cloud Optimized ● HDInsight ● Cloud BI
SQL Server
2014
Operational Analytics ● StretchDB ● Polybase ● R Integration ● Temporal ● Query Data Store ● RLS ● Always Encrypted
SQL Server
2016
Do more. Achieve more.
Mission critical platform
Performance
Security
Availability
Scalability
Operational analytics
Always Encrypted
Enhanced AlwaysOn
Row level security
• 3 synchronous replicas for
auto failover across domains
Support for Windows
Server 2016
•
Minimize performance impact
running real-time analytics on
transaction data
•
Avoid data sprawl
In-memory OLTP for
more applications
Query Store
Dynamic Data
Masking
• Round robin load balancing
of replicas
• DTC for transactional integrity
across database instances
with AlwaysOn
Enhanced online
operations
12TB 16 Sockets
Deeper insights across data
Access any data
Scale and manage
Powerful insights
PolyBase
Enterprise-grade
Analysis Services
Built-in advanced analytics
Power Query for analytics
and reporting
Enhanced SSIS
• Designer support for previous
SSIS versions
• Support for Power Query
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
Business insights through
rich visualizations on
mobile devices
Enhanced Reporting
Services
Hyperscale cloud
Hybrid solutions
Simplicity
Consistency
Stretch tables into Azure
Easy migration of on-premises
SQL Server
Common development,
management and
identity tools
Power BI with on-premises data
Hybrid scenarios with SSIS
Enhanced backup to Azure
Simplified Add Azure
Replica Wizard
Consistent experience
from on-premises to Azure
Mission-critical performance
Performance
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
Security
Always encrypted
Sensitive data remains encrypted
at all times with ability to query
Row-level security
Apply fine-grained access control
to table rows
Round robin load balancing of
replicas
Automatic failover based on
database health
Other enhancements
Support for SSIS with AlwaysOn
Real-time obfuscation of data to
prevent unauthorized access
Native JSON
TDE support for storage of inmemory OLTP tables
Query data as points in time
Three synchronous replicas for
auto failover across domains
DTC for transactional integrity
across database instances with
AlwaysOn
Audit success/failure of database
operations
Temporal database
support
Enhanced AlwaysOn
Dynamic data masking
Monitor and optimize query plans
Expanded support for JSON data
Availability
Enhanced auditing for OLTP with
ability to track history of record
changes
Scalability
Enhanced database
caching
Cache data with automatic,
multiple TempDB files per instance
in multi-core environments
Traditional operational/analytics architecture
BI analysts
 Key issues
 Complex implementation
 Requires two servers (capital
IIS Server
expenditures and operational
expenditures)
 Data latency in analytics
 More businesses demand;
requires real-time analytics
Minimizing data latency for analytics
BI analysts
IIS Server
 Benefits
 No data latency
 No ETL
 No separate data warehouse
 Challenges
 Analytics queries are resource
intensive and can cause
blocking
 Minimizing impact on
operational workloads
 Sub-optimal execution of
analytics on relational schema
 JSON became ubiquitous
 Compact and simple data exchange format
 The choice on the web
 Recommended scenario
 I CAN accept JSON, easily parse and store it as relational
 I CAN export relational easily as JSON
 I CAN correlate relational and non-relational
 Data exchange with JSON

[
SELECT * FROM myTable
FOR JSON AUTO
Number
Date
Customer
Price
Quantity
SO43659
2011-05-31T00:00:00
AW29825
59.99
1
SO43661
2011-06-01T00:00:00
AW73565
24.99
3
SELECT * FROM
OPENJSON(@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
 JSON and relational
 CREATE TABLE SalesOrderRecord (
Id int PRIMARY KEY IDENTITY,
OrderNumber NVARCHAR(25) NOT NULL,
OrderDate DATETIME NOT NULL,


JSalesOrderDetails NVARCHAR(4000)
CONSTRAINT SalesOrderDetails_IS_JSON
CHECK ( ISJSON(JSalesOrderDetails)>0 ),
AS
CAST(JSON_VALUE(JSalesOrderDetails, '$.Order.Qty') AS int)
)
 GO

JSON is plain text
ISJSON guarantees
consistency
CREATE INDEX idxJson
ON SalesOrderRecord(Quantity)
INCLUDE (Price);
Optimize further with
computed column and
INDEX
Deeper insights across data
Access any data
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 Query
Scale and manage
Enterprise-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-in 15x faster; more
granular security roles; archival
options for transaction logs; and
reuse entities across models
Powerful Insights
Mobile BI
Business insights for your onpremises data through rich
visualization on mobile devices
with native apps for Windows, iOS
and Android
Enhanced Reporting
Services
New modern reports with rich
visualizations
Advanced analytics
R integration (CTP3)
Bringing predictive analytic
capabilities to your relational
database
Analytics libraries (CTP3)
Expand your “R” script library with
Microsoft Azure Marketplace
PolyBase
Query relational and non-relational data with T-SQL
Quote:
************************
T-SQL query
**********************
*********************
**********************
***********************
SQL Server
Name
DOB
State
Jim Gray
11/13/58 WA
Ann Smith 04/29/76 ME
Hadoop
$658.39
Built-in advanced analytics
In-database analytics at massive scale
Example Solutions
Extensibility
• Sales forecasting
R Integration
• Warehouse efficiency
• Predictive maintenance
R
?
New R scripts
• Credit risk protection
010010
100100
010101
Analytic Library
010010
100100
010101
Data Scientist
Interact directly with data
010010
100100
010101
T-SQL Interface
Relational Data
Built-in to SQL Server
Data Developer/DBA
Manage data and
analytics together
010010
100100
010101
010010
100100
010101
Microsoft Azure
Marketplace
Rich visualizations on mobile devices
Business insights through rich visualizations
on any device. Native apps for Windows,
iOS and Android
Modern reports with SQL Server Reporting Services
Report consumption from
modern browsers
Improved parameters
Modern themes
New chart types
Continuous investments
Live Power BI
reports &
dashboards
Live
Query
Cloud
On-premises
Scheduled
Refresh
Mobile BI apps for SQL Server
Data visualization and publishing
Deeper insights across data
Hybrid solutions
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 staging
Simplicity
Easy 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 VMs
Consistency
Common development, management
and identity tools
Including Active Directory, Visual Studio, HyperV and System Center
Consistent Experience from SQL
Server on-premises to Microsoft
Azure IaaS and PaaS
Stretch SQL Server into Azure
Securely stretch cold tables to Azure with remote query processing
Capability
Azure
On-premises
Hot/active data
Benefits
Cold/closed data
Trickle data movement and
remote query processing
Orders
In-memory
OLTP table
Order history
Stretched table
Stretch large operational tables
from on-premises to Azure with
the ability to query
BI integration
for on-premises
and cloud
Stretch SQL Server into Azure
Stretch warm and cold tables to Azure with remote query processing
Microsoft Azure
Jim Gray
Order history
Name
ox7ff654ae6d
3/18/2005
Stretch to cloud
John Smith
Date
Customer data
cm61ba906fd 2/28/2005
Product data
ox7ff654ae6d 3/18/2005
Order History
i2y36cg776rg 4/10/2005
Bill Brown
nx290pldo90l
Jane Doe
Jim Gray
SSN

4/27/2005
Query
App
Connect live to on-premises Analysis Services data
Capability
Publish on-premises Analysis Services data
models for consumption in Power BI
Live dashboards
and exploration
Benefits
AS Connector
Analysis Services
on-premises
Tabular model
SQL Server vNext
Interactive query
Applications
Reports
Dashboards
Orchestration
Information
management
Complex event
processing
Natural
language
query
Mobile
Modeling
Machine
learning
The Microsoft
data platform

Relational
Non-relational
NoSQL
Streaming
Internal &
external
Session code
M211
M218
M320
M225
M324
M327
M229
M236
M410
M242
M244
M352
M355
M359
M252
M370
M378
M383
Session name
The SQL Server Evolution: Overview
Upgrading Microsoft SQL Server
In-Memory OLTP: The Road Ahead
Using Azure Machine Learning to predict Trade Me auction prices
Why you should be thinking Hybrid with SQL Server 2016
Big Data for the SQL Ninja
Introduction to Cortana Analytics
Microsoft SQL Server BI Drill Down
SQL Server Execution Plans and the Query Optimizer
Microsoft Azure SQL Data Warehouse Overview
BI in Action How Microsoft Finance uses Power BI
BI in the cloud, is it possible? Sure is with Azure!
End-to-End SQL Server Master Data Services
Advanced SSDT and DACFx - Practical techniques for real world database development and
deployment.
Telling Stories with Data BI Tools as a Key Component of the Business Narrative
Azure Machine Learning: From Design to Integration
Elastic for SQL shards, pools, stretch
Mission Critical features in SQL Server 2016
Start Time
9/2/2015 9:00
9/2/2015 10:40
9/2/2015 11:55
9/2/2015 13:55
9/2/2015 13:55
9/2/2015 15:10
9/2/2015 16:30
9/3/2015 9:00
9/3/2015 10:40
9/3/2015 11:55
9/3/2015 13:55
9/3/2015 15:10
9/3/2015 15:10
9/3/2015 16:30
9/4/2015 9:00
9/4/2015 10:40
9/4/2015 11:55
9/4/2015 13:55
1
Azure Consistent Service
Delivery Overview
NZ1 Wed 10:00am
2
Server Virtualisation Overview
3
Networking Overview
4
Storage Overview
NZ2 Wed 1:30pm
SKYCITY Theatre Thu 11:00am
SKYCITY Theatre Thu 3:30pm
5
Security and Assurance Overview
6
What’s New in System Centre
for Management
NZ4 Fri 9:00am
NZ1 Fri 11:00am
Find me later at…
 Hub Happy Hour Wed 5:30-6:30pm
 Hub Happy Hour Thu 5:30-6:30pm
 Closing drinks Fri 3:00-4:30pm
Free Online Learning
http://aka.ms/mva
Subscribe to our fortnightly newsletter
http://aka.ms/technetnz
http://aka.ms/msdnnz
Sessions on Demand
http://aka.ms/ch9nz
© 2015 Microsoft Corporation. All rights reserved.
Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.