·
Query Store
o
Captures queries, query plans, runtime statistics, etc.
o
Can enforce polices to direct SQL Server Query processor to
execute in specific manner
o
Contains query work load history
o
Helps system-wide or database level performance analysis and
troubleshooting
o
New system views are added to support Query store
·
Live Query Statistics
o
Live Query plans with system resources consumption such as CPU,
memory for all running queries in the plan
o
You can drill down further into live running queries from its
query plan
o
Helps you watch change of statistics during query execution
·
Native JSON Support
o
Language - Independent data-interchange format
o
Modern Web and Mobile Applications are using it
o
Alternate XML
o
Parsing done via Standard JavaScript function
o
Easier and faster than XML parsing
o
Several new T-SQL constructs introduced to facilitate JSON
·
Temporal Database support
o
Keep track data in the database (Current and historical)
o
Provides ability to query the table snapshot for any point in
time
o
Data can be restored to any known state without downtime
o
Help regulate compliance and audit
o
Help implementing slowly changing dimension
·
Always Encrypted
o
Encrypted data at rest and in motion
o
Encryption master key resides at Application
o
No application change required (Simple SSIS Package convergence)
·
Row Level Security
o
Security can be defined on a complete row
·
Column Store
o
Column store index on your in-memory table for higher throughput
·
Polybase
o
Querying relational and non-relational data
o
Example: SQL + Hadoop (With the help of external table)
·
Advance Analytics
o
Data Scientists can get model or templates from MS Azure where
others are sharing and hook it up with their real time data for data examining
·
BI on Mobile Devices
o
All BI on Mobile: (New) – DataZen App
§ Windows
§ iOS
§ Android
§ HTML5
·
Data Stretch to MS Azure
o
Stretch the data to the cloud (Turn the feature on and it works
with Always Encrypted)
§ Remote
data archive (DBA)
§ Stretch
tables into Azure
§ Power BI
Scenarios with SSIS
o
Enhanced Backup/restore for stretched databases
Enhancements?
·
Performance
o
In-memory OLTP
§ Altering
in memory table is now allowed (2014, you needed to recreate the table)
§ More size
of durable tables
§ Migration
of tables and stored procedures to in-memory report is added in SSMS standard
reports and doesn’t require management data warehouse any more
o
Query Processing
§ Better
Query plan choices and more accurate cardinality estimates
§ Faster
and more frequent Stats updates are possible
§ Parallel
insertion is possible with select into operation
o
Data and backup compression
§ Compression
level has increased for database as well as backup
o
Data collector
§ Improved
DMVs, SQL Traces, Server activity, disk usage, and query usage
§ Improved
database tuning advisor (Indexes, views and partitions can be created using
database tuning advisor)
§ Performance
reports are added in standard reports
·
Security
o
Transparent Data Encryption (TDE)
§ TDE now
supports storage of memory optimized tables
§ Tables
and column level masking is improved
o
Backup Encryption
§ Backup
encryption is now supported with compression
o
SQL Server Audit Tools
§ Underlying
operation can be audited now besides just permission audit
·
Availability
o
Availability of Mission Critical systems
§ Load
balancing between Primary and Secondary replica
§ Auto
Failover from 2 to 3
§ DTC tied
to database instead of instance
§ AG health
enhanced to database level instead of only instance level
o
Online database Operations
§ No
blocking for alter and truncate operations (100% uptime for tables during these
operations)
o
Data backup enhancement
§ Enhanced
GUI for backup/restore operation
·
Scalability
o
Windows and SQL Server enhancements
§ Supported
on Windows server Core (less patches, less maintenance and less downtime)
§ Support
for windows server ReFS(Resilient File System)
§ Faster
live migration for SQL VM
§ Cluster-Aware
Updating improved
§ Dynamic
Quorum
o
Buffer Pool Extension
§ Buffer
pool can now use SSDs (Solid-state Drives)
o
Partitioning
§ Scale
upto 1500 table partitions (helps to deal with Large data sets which
applications such as SAP uses daily or hourly)
o
Distributed Replay
§ Multithreaded
replay is introduced as opposed to single threaded replay for production get
ready planning
o
Tempdb Optimization
§ Multiple
TempDB files per instance for multi-core environments
No comments:
Post a Comment