Transcript SQL Server 2016, New Database Features

SQL Server 2016, New
Database Features
With a focus for Dynamics NAV partners and users
Kennie Nybo Pontoppidan,
Microsoft
About me
• Senior Program Manager at Microsoft
• Have worked with data since 1998
• 17+ years in the IT industry
• Have no humor
[email protected]
@KennieNP
linkedin.com/in/kennie
www.pontop.dk
2
Agenda
• Learn about SQL Server 2016 new features
• See SQL Server as a way to extend NAV
• SQL Server “partner features”
• SQL Server “customer features”
Pop quiz (before we start)
Who are you?
• Using the database for NAV out
of the box
• Using database features (eg.
Auditing, AlwaysOn, …)
• Installing SQL Server
• Tuning the application
• Tuning the database server
• Database administrators
Image source: http://www.keepcalm-o-matic.co.uk/p/keep-calm-coz-i-love-u-nav/
What do you run (in production)
• SQL Server 2000
• SQL Server 2005
• SQL Server 2008
• SQL Server 2008R2
• SQL Server 2012
• SQL Azure
• SQL Server 2014
• SQL Server 2016
• SQL Server on Linux
NAV architecture 101
What if…
Before we dive in…
A few small improvements…
• Deadlock detection and logging
• Who’s blocking who
• SQLIndex bugfix
Deadlock detection and logging
• Setup
• SQL Server privileges
• NAV Server
• (Administration tool or Powershell cmdlet)
• Monitor
• Eventlog (filter on trace tag 00000DI)
• Examine
• Save deadlock graph as deadlock XML (.xdl) file
• Open in SQL Server Management Studio (SSMS)
Who’s blocking who?
• New virtual table 'Database locks‘
• information about current locks and their owners
• data for transactions against tables in current company
• Additional data regarding username and AL scope is shown if virtual
table is build on the same NAV server where user have aquired the
lock.
• Added page 9511
SQLIndex bugfix
https://dynamicsuser.net/nav/b/vanvugt/posts/what-happened-tosqlindex
Cliff jumping world record
Height- 58.8m or 192.9 ft
Top Speed- 123km/h or 76 mph
Pool Depth- 8m or 26 ft
Hang Time- 3.58 seconds
SQL 2012 new features
SQL Server 2012 new features
Standard Edition
Enterprise Edition
• SSIS project model
• Audit
• SSAS tabular model
• Non-clustered column store
indexes (non-updatable)
• Data Quality Services (DQS)
• Always On Availability Groups
SQL Server 2016 SP1 – Standard edition
• Faster transaction performance from In-memory OLTP, faster query performance
from In-memory ColumnStore, and the ability to combine the two for real-time
Hybrid Transactional and Analytical Processing, also known as Operational
Analytics;
• Data warehousing or data mart performance features such as partitioning,
compression, change data capture, database snapshot, and the ability to query
across structured and unstructured data with a single node of PolyBase; and
• The innovative security feature Always Encrypted for encryption at rest and in
motion, as well as fine-grained auditing which captures more detailed audit
information for your compliance reporting needs. In addition, row-level security
and dynamic data masking are being made available to Express edition for the
first time.
https://blogs.technet.microsoft.com/dataplatforminsider/2016/11/16/sql-server2016-service-pack-1-generally-available/
Audit
track and log events that occur on
the Database Engine
• Who accessed your
sensitive tables in the last
week?
• What has your
administrator changed in
the past month?
• Server level auditing – all
editions
• Database level auditing - only
Enterprise edition
https://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI407
Data Quality Services (DQS)
enables a data steward or IT
professional to maintain the
quality of their data and ensure
that the data is suited for its
business usage
https://msdn.microsoft.com/en-us/library/ff877917.aspx
https://channel9.msdn.com/posts/SQL11UPD05-REC-05
Always On Availability Groups
high-availability (HA) and disasterrecovery (DR) solution
https://msdn.microsoft.com/en-us/library/hh510230.aspx
https://channel9.msdn.com/events/teched/northamerica/2011/dbi302
Carowinds Fury 325 roller coaster
Height- 99m
Top Speed- 153km/h
Max vertical angle – 81 degr
SQL 2014 new features
SQL Server 2014 new features
Standard Edition
Enterprise Edition
• SQL engine memory limit
increase 64GB->128GB
• Buffer pool extension
• Backup encryption
• In-memory OLTP
• Clustered column store indexes
• Non-clustered column store
indexes (updatable)
SQL engine memory limit increase
We use memory for
• OS
• Data (pages)
• Connections
•…
• More memory for data => less
traffic to storage (I/O)
Memory limit is per instance
• SQL engine (128GB)
• SSAS (64 GB)
• SSRS (64 GB)
Max memory for SQL engine
increased 64GB->128GB
Buffer pool extension
seamless integration of solidstate drive (SSD) extension to
the Database Engine buffer
pool to significantly improve
I/O throughput
https://msdn.microsoft.com/en-us/library/dn133176(v=sql.120).aspx
https://channel9.msdn.com/Series/sqlserver-2014-mission-crit-performace/07
Consider using Buffer pool extension when
• your total actively queried data
set is bigger than you can fit in
memory
• you have already maxed out the
memory on the server
• business requirements force you
to use shared storage or
magnetic local storage
• your server have room for locally
attached PCI Express or
SAS/SATA solid state drives
https://www.brentozar.com/archive/2013/06/almost-everything-you-need-to-knowabout-the-next-version-of-sql-server/
Backup encryption
Just do it…
https://msdn.microsoft.com/en-us/library/dn449489.aspx
SQL Server 2014 new hybrid cloud features
Standard Edition
Enterprise Edition
• Backup to Azure
• Data+log files on Azure blob
storage
• AlwaysOn replica in Azure
SQL 2016 new features
SQL Server 2016 editions – what’s new
SQL Server 2016 is the biggest leap forward in the Microsoft data platform history with real-time operational analytics, rich
visualizations on mobile devices, built-in advanced analytics, new advanced security technology, and new hybrid cloud scenarios.
Enterprise
Standard
Express
Developer
SQL Server Enterprise delivers comprehensive
datacenter capabilities for mission-critical
database, business intelligence, and advanced
analytics workloads.
SQL Server Standard provides core data
management and business intelligence
capabilities for non-critical workloads with
minimal IT resources.
SQL Server Express is a free edition of SQL Server
ideal for development and production for
desktop, web and small server applications.
SQL Server Developer is now a free edition
providing the full feature set of SQL Server
Enterprise. For development and test only, and
not for production environments or use with
production data.
Mission critical Security
performance
Data
warehousing
Business
intelligence
Advanced
analytics
Hybrid cloud
• Operating system max cores
and memory
• Enhanced in-memory
ColumnStore
• End-to-end mobile BI on all
major platforms
• In database advanced
analytics
• Stretch Database
• PolyBase in scale-out
configuration
(head and compute nodes)
• Enhanced DirectQuery
• R integration with massive
parallel processing for
performance and scale
• Enhanced in-memory OLTP
performance
• Operational analytics
Standard
Enterprise
• Enhanced AlwaysOn with no
domain join (WS 2016)
• Query Store
• Temporal
• Always Encrypted
• Row-level security
• Dynamic data masking
• Enhanced separation of
duties
• Enhanced SQL Server
auditing
• License rights to (the APS)
MPP appliance”
• Transparent data encryption
• Distributed query
processing
• Policy-based management
• Support for JSON
• Advanced data mining
• Advanced tabular
• Web portal experience
(all reports in one place)
• Modernized reports
• Pin reports to Power BI
• Works with in-memory
technology
• Run in database or
standalone
• Connectivity to R Open
• Enhanced HA and DR with
Azure – ease of use, no
domain join (Windows
Server 2016)
• SSIS integration with Azure
Data Factory and Azure SQL
Data Warehouse
• Enhanced multi-dimensional
models
• Disk-based OLTP
• Row-level security
• 24 cores max and 128 GB
max memory
• Dynamic data masking
• 2-node single database
failover (non-readable
secondary)
• Separation of duties
• Query Store
• Basic auditing
• PolyBase
(compute node only)
• Basic tabular (16GB memory
per instance)
• Support for JSON
• Web portal experience
• 1 GB memory,
max 10 GB memory
• Basic OLTP
• Query Store
• Stretch Database
• Connectivity to R Open
• Backup to Azure
• Pin reports to Power BI
• Policy-based management
• Policy-based management
• Single-threaded for RRE
• Modernized reports
• Enhanced multi-dimensional
models
• Temporal
Express
• In-memory analytics
• Enhanced backup
to Azure
• Support for JSON
• Basic reporting and analytics
• Stretch Database
• Web portal experience
• Backup to Azure
• Modernized reports
• Temporal
This content was developed prior to the product or service’s release and as such, we cannot guarantee that all details included herein will be exactly as what is found in the shipping product. Because Microsoft must respond to changing market conditions, it
should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. The information represents the product or service at the time this document
was shared and should be used for planning purposes only. Information subject to change at any time without prior notice.
SQL Server 2016 features by edition
Developer1
Express
Standard
Enterprise
Unlimited
4 cores
24 cores
Unlimited
Maximum memory utilized per instance
OS Max
1 GB
128 GB
OS Max
Maximum size
524 PB
10 GB
524 PB
524 PB



Maximum number of cores
New
Production use rights
Basic OLTP




Manageability (Management Studio, Policy-Based Management)






Basic high availability (2-node single database failover, non-readable secondary)
New
Enterprise data management (Master Data Services, Data Quality Services)
Advanced OLTP (In-memory OLTP, Operational analytics)
New
Advanced HA (Always On - multi-node, multi-db failover, readable secondaries)
Security
Data
warehousing






Basic security (Row-level security, data masking, basic auditing, separation of duties)
New

Advanced security (Transparent Data Encryption, Always Encrypted)
New






Advanced data integration (Fuzzy grouping and look ups, change data capture)
Data warehousing (In-Memory ColumnStore, Partitioning)
New
PolyBase2
Programmability & developer t ools (T-SQL, CLR, Data Types, FileTable, JSON)
Business
intelligence



New







Basic reporting & analytics




Basic data integration (SSIS, built-in connectors)





Basic Corporate Business Intelligence (Multi-dimensional models, Basic tabular
model)
New

Mobile BI (Datazen)
New


Advanced Corporate Business Intelligence (Advanced tabular model, DirectQuery,
in-memory analytics, advanced data mining)
New


Advanced
analytics
Basic “R” integration (Connectivity to R Open, Limited parallelism for RRE)
New

Advanced “R” integration (Full parallelism for RRE)
New

Hybrid cloud
Stretch Database
New







1
SQL Server 2016 Developer Edition offers the full feature set of SQL Server 2016 Enterprise Edition, but Developer Edition is for development and test only, and not for production environments or use with production data.
Scale out relational and non-relational data queries with the simplicity of T-SQL using PolyBase, which requires single Microsoft SQL Server 2016 Enterprise Edition as head node.
© 2016 Microsoft Corporation. All rights reserved. This document is provided "as-is." Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it. This
not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes. You may modify this document for your internal, reference purposes.
2
document does

SQL Server 2016 new features – std. edition
Mission critical
performance
Security
Data warehousing
24 cores max Row-level
Support for
security
JSON
2-node single
database
Dynamic data
failover
masking
Query Store
Temporal
Business intelligence
Advanced analytics
Hybrid cloud
Basic tabular
Singlethreaded for
RRE
Stretch
Database
Web portal
experience
Modernized
reports
Pin reports to
Power BI
Connectivity
to R Open
SQL Server 2016 new features – std. edition
Mission critical
performance
Security
Data warehousing
24 cores max Row-level
Support for
security
JSON
2-node single
database
Dynamic data
failover
masking
Query Store
Temporal
Business intelligence
Advanced analytics
Hybrid cloud
Basic tabular
Singlethreaded for
RRE
Stretch
Database
Web portal
experience
Modernized
reports
Pin reports to
Power BI
Connectivity
to R Open
SQL engine core limit increase
We use CPU cores for
• OS
• Execution
• Connections
•…
Core limit is per instance
• SQL engine (24)
• SSAS (24)
• SSRS (24)
Max cores increased 16->24
2-node single database failover
Primary database
Licensing info
http://download.microsoft.com/download/9/C/6/9C6EB70A-8D52-48F4-9F0408970411B7A3/SQL_Server_2016_Licensing_Guide_EN_US.pdf
Secondary replica
• Same location or in Azure
Commit modes
• synchronous-commit, or
• asynchronous-commit
“… For each server licensed with
SQL Server 2016 and covered by
active SA, customers can run up to
the same number of passive
failover instances in a separate,
OSE to support failover events”
https://msdn.microsoft.com/en-us/library/mt614935.aspx
Query store
Query Store automatically
captures a history of
queries, plans, and
runtime statistics, and
retains these for your
review
https://msdn.microsoft.com/en-us/library/dn817826.aspx
https://channel9.msdn.com/Shows/Data-Exposed/Query-Store-in-SQL-Server-2016
Temporal
System-versioned state tables
SQL2011 standard
Audit: who changed it
Temporal: what changed
https://msdn.microsoft.com/en-us/library/dn935015.aspx
https://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016
SQL Server 2016 new features – std. edition
Mission critical
performance
Security
Data warehousing
24 cores max Row-level
Support for
security
JSON
2-node single
database
Dynamic data
failover
masking
Query Store
Temporal
Business intelligence
Advanced analytics
Hybrid cloud
Basic tabular
Singlethreaded for
RRE
Stretch
Database
Web portal
experience
Modernized
reports
Pin reports to
Power BI
Connectivity
to R Open
Row Level Security (RLS)
control access to rows in a table
based on the user
• Filter predicates filter rows
available to read operations
• Block predicates block write
operations
https://msdn.microsoft.com/en-us/library/dn765131.aspx
https://channel9.msdn.com/Events/DataDriven/SQLServer2016/Row-Level-Security
Dynamic Data Masking
limit sensitive data exposure by
masking it to non-privileged users
• Not a encryption feature
• Mask/unmask privilege on
database level
https://msdn.microsoft.com/en-us/library/mt130841.aspx
https://channel9.msdn.com/Events/DataDriven/SQLServer2016/DynamicDataMasking
SQL Server 2016 new features – std. edition
Mission critical
performance
Security
Data warehousing
24 cores max Row-level
Support for
security
JSON
2-node single
database
Dynamic data
failover
masking
Query Store
Temporal
Business intelligence
Advanced analytics
Hybrid cloud
Basic tabular
Singlethreaded for
RRE
Stretch
Database
Web portal
experience
Modernized
reports
Pin reports to
Power BI
Connectivity
to R Open
JSON
• Parse JSON text and read or
modify values
• Transform arrays of JSON objects
into table format
• Format the results of T-SQL
queries in JSON format
https://msdn.microsoft.com/en-us/library/dn921897.aspx
https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-2016-and-JSON-Support
SQL Server 2016 new features – std. edition
Mission critical
performance
Security
Data warehousing
24 cores max Row-level
Support for
security
JSON
2-node single
database
Dynamic data
failover
masking
Query Store
Temporal
Business intelligence
Advanced analytics
Hybrid cloud
Basic tabular
Singlethreaded for
RRE
Stretch
Database
Web portal
experience
Modernized
reports
Pin reports to
Power BI
Connectivity
to R Open
Basic tabular
Analysis Services databases
16GB limit
In-memory
https://msdn.microsoft.com/en-us/library/hh212945.aspx
https://channel9.msdn.com/Events/TechEd/Europe/2014/DBI-B414
Web portal experience
Reporting Services web portal
View
• KPIs
• mobile reports
• paginated reports
https://msdn.microsoft.com/en-us/library/mt637133.aspx
https://channel9.msdn.com/Events/DataDriven/SQLServer2016/Access-reports-in-modernbrowsers-and-mobile-devices
Modernized reports
SSRS reports now render on
modern browsers (HTML5)
https://msdn.microsoft.com/en-us/library/ms170438.aspx
https://channel9.msdn.com/Events/Microsoft-Data-Insights-Summit/2016/BRE032
Pin reports to Power BI
Pin an on-prem SSRS report to a
Power BI dashboard
https://blogs.msdn.microsoft.com/sqlrsteamblog/2015/10/28/pin-reporting-services-charts-topower-bi-dashboards-with-sql-server-2016-ctp-3-0/
SQL Server 2016 new features – std. edition
Mission critical
performance
Security
Data warehousing
24 cores max Row-level
Support for
security
JSON
2-node single
database
Dynamic data
failover
masking
Query Store
Temporal
Business intelligence
Advanced analytics
Hybrid cloud
Basic tabular
Singlethreaded for
RRE
Stretch
Database
Web portal
experience
Modernized
reports
Pin reports to
Power BI
Connectivity
to R Open
R is…
a language and environment for
statistical computing and graphics
free to use
open source
provides a wide variety of
statistical and graphical
techniques
https://www.r-project.org/about.html
R statistic examples
Comprehensive R Archive
Network (CRAN)
• Currently 9224 available
packages
https://cran.r-project.org/
Example techniques
• linear and nonlinear modelling
• classical statistical tests
• time-series analysis
• classification
• clustering
Source: http://www.ats.ucla.edu/stat/r/seminars/ggplot2_intro/ggplot2_intro.htm
R graphical examples
Source: https://benjaminlmoore.wordpress.com/tag/ggplot2/
Source: http://statmatt.com/r-ggmap-examples-plot-overlay-spatial-data/
R in SQL Server 2016
Run R code in the database
Embed R code in T-SQL stored
procedures
Return
• Results
• plots
https://msdn.microsoft.com/en-us/library/mt604845.aspx
https://channel9.msdn.com/Events/DataDriven/SQLServer2016/Predictive-Maintenance-with-R-Services
SQL Server 2016 new features – std. edition
Mission critical
performance
Security
Data warehousing
24 cores max Row-level
Support for
security
JSON
2-node single
database
Dynamic data
failover
masking
Query Store
Temporal
Business intelligence
Advanced analytics
Hybrid cloud
Basic tabular
Singlethreaded for
RRE
Stretch
Database
Web portal
experience
Modernized
reports
Pin reports to
Power BI
Connectivity
to R Open
Stretch database
Migrate (part of) table to Azure
• Cost-effective availability for cold
data
• No changes to queries or
applications
• Streamlines on-premises data
maintenance
• Data secure during migration
https://msdn.microsoft.com/en-us/library/dn935011.aspx
https://channel9.msdn.com/Events/DataDriven/SQLServer2016/StretchDatabase
SQL Server 2016 new features – enterpr.
edition
Mission critical
performance
Security
Data warehousing
Business intelligence
Advanced analytics
Operational
analytics
Always
Encrypted
PolyBase
Mobile BI
Parallel R
Enhanced
DirectQuery
R server
In-memory
analytics
Hybrid cloud
SQL Server 2016 new features – enterpr.
edition
Mission critical
performance
Security
Data warehousing
Business intelligence
Advanced analytics
Operational
analytics
Always
Encrypted
PolyBase
Mobile BI
Parallel R
Enhanced
DirectQuery
R server
In-memory
analytics
Hybrid cloud
Non-clustered column store indexes
ability to run both analytics and
OLTP workloads on the same
database tables at the same time
https://msdn.microsoft.com/en-us/library/dn817827.aspx
SQL Server 2016 new features – enterpr.
edition
Mission critical
performance
Security
Data warehousing
Business intelligence
Advanced analytics
Operational
analytics
Always
Encrypted
PolyBase
Mobile BI
Parallel R
Enhanced
DirectQuery
R server
In-memory
analytics
Hybrid cloud
Always Encrypted
Security controlled by client
Data is encrypted
• over the network
• in memory on the database
• in database data files
https://msdn.microsoft.com/en-us/library/mt163865.aspx
https://channel9.msdn.com/Events/DataDriven/SQLServer2016/AlwaysEncrypted
SQL Server 2016 new features – enterpr.
edition
Mission critical
performance
Security
Data warehousing
Business intelligence
Advanced analytics
Operational
analytics
Always
Encrypted
PolyBase
Mobile BI
Parallel R
Enhanced
DirectQuery
R server
In-memory
analytics
Hybrid cloud
PolyBase
Execute T-SQL queries against
• relational data in SQL Server
• semi-structured data in Hadoop
or Azure Blob Storage
Leverage existing T-SQL skills and
BI tools to gain insights from
different data stores
https://msdn.microsoft.com/en-us/library/mt143171.aspx
Access any data
https://channel9.msdn.com/Events/DataDriven/SQLServer2016/PolyBase
SQL Server 2016 new features – enterpr.
edition
Mission critical
performance
Security
Data warehousing
Business intelligence
Advanced analytics
Operational
analytics
Always
Encrypted
PolyBase
Mobile BI
Parallel R
Enhanced
DirectQuery
R server
In-memory
analytics
Hybrid cloud
Mobile BI
Reporting Services mobile reports,
optimized for mobile devices
Design for phones/tablets
View on any device
iOS, Microsoft, Android
https://msdn.microsoft.com/en-us/library/mt652547.aspx
https://channel9.msdn.com/Events/DataDriven/SQLServer2016/Mobile-Report-Publisher
Access any data
SSAS direct query
No data stored in SSAS database
https://msdn.microsoft.com/en-us/library/hh230898.aspx
https://channel9.msdn.com/Events/DataDriven/SQLServer2016/DirectQuery-for-Tabular-models
Access any data
SQL Server 2016 new features – enterpr.
edition
Mission critical
performance
Security
Data warehousing
Business intelligence
Advanced analytics
Operational
analytics
Always
Encrypted
PolyBase
Mobile BI
Parallel R
Enhanced
DirectQuery
R server
In-memory
analytics
Hybrid cloud
R in SQL Server 2016 enterprise edition
Dedicated R server
Run R code in parallel
https://msdn.microsoft.com/en-us/library/mt674874.aspx
SQL 2016 themes
SQL Server 2016 themes
It just works
• Traceflags 1117, 1118
• Database Instant File
Initialization
It just runs faster
• Number of tempdb datafiles
• Multiple log writers
https://blogs.msdn.microsoft.com/sql_server_team/sqlserver-2016-changes-in-default-behavior-for-autogrowand-allocations-for-tempdb-and-user-databases/
https://blogs.msdn.microsoft.com/psssql/2016/03/17/sql2016-it-just-runs-faster-automatic-tempdb-configuration/
https://msdn.microsoft.com/en-us/library/ms175935.aspx
https://blogs.msdn.microsoft.com/psssql/2016/04/19/sql2016-it-just-runs-faster-multiple-log-writer-workers/
Want to learn more?
Watch more than 30 videos on SQL Server 2016 features
https://channel9.msdn.com/events/DataDriven/SQLServer2016
Visit (free) SQL Saturday conferences world-wide
http://www.sqlsaturday.com
3-Dec-16SQLSaturday #569 - Prague 2016
10-Dec-16SQLSaturday #567 - Slovenia 2016
20-Jan-17SQLSaturday #579 - Vienna 2017
25-Feb-17SQLSaturday #589 - Pordenone 2017
11-Mar-17SQLSaturday #583 - Lisbon 2017
18-Mar-17SQLSaturday #602 - Iceland 2017
18-Mar-17SQLSaturday #601 - Belgrade 2017
25-Mar-17SQLSaturday #590 - Portsmouth, UK 2017
27-May-17SQLSaturday #599 - Plovdiv 2017
Thank you!
[email protected]
@KennieNP
linkedin.com/in/kennie
www.pontop.dk
73