SQL Server 2016
Everything built-in
Concepts Always On Availability Groups
Data Insights
Columnstore PolyBase
Stretch Database
In-Memory Analytics
R Services PolyBase
Temporal Tables
JSON Support
Query Data Store
TempDB
Always Encrypted
Modern BI
Row Level Security
Platform
Continuous Innovation
Hybrid cloud
Cloud-First Approach SQL Server 2016
New innovations
Continuous
enhancements
SQL DB SQL DB SQL DB SQL DB
Advanced Analytics
Across customer base
End-to-end mobile BI
Speed Proven Enterprise-grade DW The best
SQL Server
Agility Feedback Mission critical OLTP release in history
SQL Server 2016 New Features
SQL Server Database Engine
• New Features
• In-Memory
• Query Store
• Stretch Database
• Temporal Queries
Always On Availability Groups
SQL Security Enhancements
SQL Server Business Intelligence
• Modern SQL Server Reporting Services
• DataZen Integration
Native Big Data with PolyBase
SQL Server 2016 Database Engine
Enhancements
In-Memory OLTP
Max Size 2 TB
SQL Max Size 256 GB SQL
Server No LOB Support Server LOB Support
2014 No TDE 2016 TDE
ALTER TABLE
No ALTER TABLE
NULL support in Indexes
No NULL support in Indexes
Parallel Queries
Single threaded
Foreign Keys Support
No Foreign Keys MARS support
No MARS support Check Constraints
No Check Constraints Columnstore Index Support
Limited T-SQL Operations Support for OUTER JOIN, OR, NOT, DISTINCT, EXISTS, IN
Query Store
Flight Data
Recorder for the
Database Engine
Allows the ability
Captures
to force query
Execution Plans
execution plans
Captures Plan
Regression
Query Store
Query Plans change over time—due to data change,
optimizer changes
Query Execution Plans may change between versions of
SQL Server—optimizer changes can lead to plan regression
Query Data Store captures all of this data, at low
performance impact
Allows administrators to easily identify problematic queries
and execution plans
Query Store
Query Store
Customizable dashboard to identify
High Resource Queries Regressed Queries Tracked Queries
Allows Administrators and Developers to rapidly identify and tune problematic
queries
Forced plans can help with parameter sniffing issues
Stretch Database
Integrates directly with
SQL Server
No code changes are
needed for archiving data
Secured data connection
to Azure
Seamless to end users
Stretch Database
Query Optimizer
is aware of data
locality
Performance
Integration
level of SQL
with SQL
Database can
Backup
be adjusted as
Processes
needed
Native JSON Support
SELECT name, surname
FROM emp [
FOR JSON AUTO { "name": "John" },
{ "name": "Jane", "surname": "Doe" }
]
SELECT * FROM
OPENJSON('{"name":"John","surname":"Doe","age":45}')
Temporal Queries
Database Engine—Other Key Features
TempDB Enhancements
Trace Flag 4199 Behavior Enabled in Engine
Live Query Statistics
Enhancements to Backup to Azure and Managed Backup
SQL Server 2016
What’s New in Availability
Groups
SQL 2016 Availability Group Enhancements
Database Level Health Check
Distributed Transaction Coordinator (DTC) Support
Better Automatic Failover Support
Log Transport Performance
Load Balanced Readable Secondaries
Group Managed Service Account (gMSA) Support
Standard Edition Support
Distributed Transaction Coordinator (DTC) Support
Currently Distributed Transactions touching an
AG database are not supported
• Many enterprise apps require cross database transactions
Support for DTC in 2016
Log Transport Performance and Automatic Failover Targets
2012, 2014 supported only one automatic failover target
2016 supports any synchronous secondary (up to three total)
Log Transport Performance
• Hardware enhancements (notably SSDs) have driven redo throughput
higher
• Entire pipeline rewritten, lower CPU utilization, overall better performance
• Performance aim is to match that of standalone server
Load Balancing in Readable Secondaries
In 2014, reads went to first
available secondary
Availability Group Listener
Now you can configure
routing to use round-robin
AG1 AG2 AG3 AG4
amongst secondaries
Group Managed Service Account Support
Workgroup and Cross Domain Availability Groups
With Windows 2016 and SQL
Server 2016 Active Directory
domains are no longer a
requirement for availability groups
Full feature compatibility with
database mirroring
Use Case: Edge sites, or cross
company configurations
SQL Server 2016
What’s New in Security
SQL 2016 New Security Features
Always Encrypted
Dynamic Data Masking
Row-Level Secuirty
New Permissions
Transparent Data Encryption CPU Offload
AES Endpoint Encryption
Always Encrypted
Client Encrypted
Libraries data can
encrypt be queried
inbound and
data indexed
Dynamic Data Masking
Restricts data view by user login
Can use custom or default masks
• Email
• SSN
• Phone Number
Row Level Security
RLS Predicates
are functionally
Controls access equivalent to a
to rows based WHERE clause
on the user in a query
executing the
query
SQL 2016 Security Enhancements
Transparent
Data Support for Intel AES-NI Encryption
Encryption Acceleration
AES Endpoint
Encryption
Default changes from RC4 to AES
New
Permissions
Support new features
SQL Server 2016
What’s New in Business
Intelligence
SQL Server 2016—BI Enhancements SSRS
HTML5
Sunburst and Treemap Charts
Modern Report Builder
Power BI Compatibility
Mobile Rpeorting
Dashboarding tool acquired by
Microsoft in 2015 form DataZen
Full Excel, SQL Server and
SharePoint source support
On-premises mobile solution
SQL Server 2016—BI Enhancements SSAS
Great deal of enhancements to DAX
New Functions
Parallel Processing for Tabular models
Better performance for DirectQuery
GUI support for Extended Events
SQL Server 2016—BI Enhancements SSIS
Incremental
Package
Always On Support OData v4 Support
Deployment
Support
Enhanced Logging New Connectors
SQL Server 2016—BI Enhancements Database
Engine
R Services for SQL Server
Columnstore Enhancements
In-memory columnstore
JSON Support
In-database Advanced Analytics
Build intelligent applications with SQL Server R Services
R Built into SQL Server
Analytics without data
movement allows for
real time analytics
Mission critical OLTP
Take advantage of
parallel processing and
R built-in to SQL Server in-memory technologies
Remove the complexity of big data
T-SQL over Hadoop
PolyBase Polybase allows for easy access to HD
Insight or on-premises Hadoop
solutions
Allows External tables to be defined,
Quote:
and queried via T-SQL
************************
T-SQL query **********************
*********************
**********************
***********************
Data can be brought into SQL Server
SQL Server Hadoop $658.39 if needed
Name DOB State
Denny Usher 11/13/58 WA
Gina Burch 04/29/76 WA
PolyBase
PolyBase can work with Azure
Blob Storage or Hadoop—to
provide low cost options for
storing data
Full BI support with SQL Server
BI stack or third-party BI tools
Only Microsoft delivers
On-premises & cloud
Consistent experience is everything
