SQL_2016_for_the_DBAx
Download
Report
Transcript SQL_2016_for_the_DBAx
UNIQUE AND NOT DULL
www.MyGareth.com
Agenda
New Features in SQL Server 2016
About me
General Enhancements
In-memory OLTP Enhancements
Always Encrypted
Query Store and Live Query Statistics
Row-Level Security
Stretch Database
AlwaysOn Enhancements
UNIQUE AND NOT DULL
www.MyGareth.com
Agenda (cont)
New Features in SQL Server 2016
PolyBase
JSON Support
TempDB enhancements
References
UNIQUE AND NOT DULL
www.MyGareth.com
Gareth Swanepoel
From South Africa originally
Been in IT for 25+ years
Was first a sysadmin, then a DBA
Sr. Data Platform & BI Consultant with
Pragmatic Works
Author - SQL 2012 Pro Admin
Speaker – SQL User Groups, SQL Saturdays,
SQL PASS Summit, webinars
eMail : [email protected]
Twitter : @GarethSwan
Website : www.MyGareth.com
UNIQUE AND NOT DULL
www.MyGareth.com
UNIQUE AND NOT DULL
www.MyGareth.com
General Enhancements
Advanced Analytics (RRE integration)
Data Exploration and Predictive Modeling with R over SQL Server data
Operationalizing your R code using T-SQL
Transactional replication from SQL Server to Azure SQL DB
Per Session Wait Statistics
sys.dm_exec_session_wait_stats (instance level – sys.dm_os_wait_stats)
Instant File Initialisation configuration during initial setup
Enhanced backup to Azure
UNIQUE AND NOT DULL
www.MyGareth.com
In-memory OLTP Enhancements
Feature/Limit
SQL Server 2014
SQL Server 2016
Maximum size of durable table
LOB (varbinary(max), [n]varchar(max))
Transparent Data Encryption (TDE)
Offline Checkpoint Threads
ALTER PROCEDURE / sp_recompile
Nested native procedure calls
Natively-compiled scalar UDFs
ALTER TABLE Not supported
256 GB
Not supported
Not supported
1
Not supported
Not supported
Not supported
Not supported
(DROP / re-CREATE)
Not supported
Not supported
Not supported
Not supported
Not supported
Not supported
Not supported
Not supported
Not supported
Not supported
2 TB
Supported*
Supported
1 per container
Supported (fully online)
Supported
Supported
Partially supported
(offline – details below)
Partially supported
(AFTER, natively compiled)
Supported
Supported
Supported
Supported
Supported
Supported
Supported
Supported
Supported
Not supported
Supported
DML triggers
Indexes on NULLable columns
Non-BIN2 collations in index key columns
Non-Latin codepages for [var]char columns
Non-BIN2 comparison / sorting in native modules
Foreign Keys
Check/Unique Constraints
Parallelism
OUTER JOIN, OR, NOT, UNION [ALL], DISTINCT, EXISTS, IN
Multiple Active Result Sets (MARS)
(Means better Entity Framework support.)
SSMS Table Designer
UNIQUE AND NOT DULL
www.MyGareth.com
Always Encrypted
Protects data at rest and in motion
Encryption key resides with the application
Encryption and Decryption are transparent & happen on application side
Always Encrypted Driver - .NET Framework 4.6
UNIQUE AND NOT DULL
www.MyGareth.com
Always Encrypted
Encryption is at the column level, rather than at the database level
Feature-based restrictions:
Temporal tables
Triggers are partially supported
Full-text search
Replication
Change Data Capture
In-memory OLTP
Stretch database
UNIQUE AND NOT DULL
www.MyGareth.com
Query Store
Flight data recorder (black box) for your database
Query store retains multiple execution plans per query
Collects query text along with all relevant properties
Two stores:
Plan store – persisting execution plan information AND metrics
Runtime statistics store – persisting the statistics information
Catalog views: sys.query_store_plan, sys.query_store_query,
sys.query_store_query_text
UNIQUE AND NOT DULL
www.MyGareth.com
With query store , you can ….
Easily find & fix plan regression
Pinpoint the most expensive queries
Minimise SQL Server upgrade risks
Analyse workload patterns
UNIQUE AND NOT DULL
www.MyGareth.com
Query Store Views
Regressed Queries
Overall Resource Consumption
Top Resource Consuming Queries
Tracked Queries
UNIQUE AND NOT DULL
www.MyGareth.com
Regressed Queries
UNIQUE AND NOT DULL
www.MyGareth.com
Top Resource Consumers
UNIQUE AND NOT DULL
www.MyGareth.com
Live Query Statistics
Include Live Query Statistics
UNIQUE AND NOT DULL
www.MyGareth.com
Live Query Plan
UNIQUE AND NOT DULL
www.MyGareth.com
Activity Monitor
UNIQUE AND NOT DULL
www.MyGareth.com
Row-Level Security
Row level access control
Based on user’s group membership or execution context
Restriction logic located at the DB tier rather than at the app tier
Optimizer automatically applies predicate function to queries
Security Predicates:
Filter
Block
Steps:
Create predicate function
Create security policy to bind function to table(s)
UNIQUE AND NOT DULL
www.MyGareth.com
Stretch Database
Leverages Azure to store and query archive data
Archives data transparently and securely
Reduced cost, performance gains against current (on-premises) data
Can join across local and remote data
User code will still point to SQL on-premises instance
Run the Stretch Database Advisor
EXEC sys.sp_configure N'remote data archive', '1';
UNIQUE AND NOT DULL
www.MyGareth.com
Stretch Database
UNIQUE AND NOT DULL
www.MyGareth.com
Stretch DB Limitations (CTP 3.0)
Memory-optimized and replicated tables
Tables that contain FILESTREAM data, use Change Tracking or Change Data
Capture
Data types such as timestamp, sql_variant, XML, geography or columns that
are Always Encrypted
Check, default, or foreign key constraints that reference the table
XML, full-text, spatial, clustered columnstore indexes and indexed views
UPDATE or DELETE statements, CREATE INDEX or ALTER INDEX operations
Limitations: https://msdn.microsoft.com/en-us/library/mt605114.aspx
UNIQUE AND NOT DULL
www.MyGareth.com
AlwaysOn Enhancements
Distributed Transaction Coordinator support
Load Balancing for Readable Secondaries
Additional synchronous failover targets – up to 3
Optional setting to fail over based on database failure
Group Managed Service Accounts are fully supported
Improved log transport performance
UNIQUE AND NOT DULL
www.MyGareth.com
PolyBase
SQL
Results
1
7
Map job
Hadoop
2
SQL 2016
5
6
MapReduce
Introduced in PDW v2
3
Use T-SQL to access data in Hadoop
Configuration:
4
DB
HDFS
Install PolyBase
Choose Hadoop or Azure using sp_configure
Create external data source and file format in SQL
Create external table to reference the data
UNIQUE AND NOT DULL
www.MyGareth.com
JSON Support
JSON - JavaScript Object Notation.
JSON support is one of the highest ranked requests by customers.
JSON is used primarily to transmit data between a server and web
application, as an alternative to XML.
JSON is used in a majority of Web APIs and is growing significantly.
CTP2 – FOR JSON [PATH | AUTO]
CTP3 – OPENJSON, IsJSON (< JSON text >), JSON_Value ()
UNIQUE AND NOT DULL
www.MyGareth.com
TempDB Enhancements
Trace Flags 1117 & 1118 not required
Setup adds TempDB files = CPU count or 8, whichever is lower
Default initial size is 8MB and the default autogrowth is 64MB
Multiple volumes for TempDB files
Configure TempDB during initial setup
UNIQUE AND NOT DULL
www.MyGareth.com
References
Microsoft SQL Server 2016 Product Page:
• http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/
Always Encrypted:
• https://msdn.microsoft.com/en-us/library/mt163865.aspx
Tim Radney - Introduction to Stretch Database:
• http://sqlperformance.com/2015/08/sql-server-2016/intro-stretch-database
SQL Server Query:
• https://msdn.microsoft.com/en-us/library/dn817826.aspx
• https://channel9.msdn.com/Shows/Data-Exposed/Query-Store-in-SQL-Server2016
UNIQUE AND NOT DULL
www.MyGareth.com
References
Row-Level Security:
• https://msdn.microsoft.com/en-us/library/dn765131.aspx
Stretch Database:
• https://msdn.microsoft.com/en-us/library/dn935011.aspx
PolyBase in SQL Server 2016:
• https://channel9.msdn.com/Shows/Data-Exposed/PolyBase-in-SQL-Server-2016
Aaron Bertrand - SQL Server 2016 Advanced JSON Techniques:
• https://www.mssqltips.com/sqlservertip/4073/sql-server-2016-advanced-jsontechniques--part-1/
Klaus Aschenbrenner - TempDB Changes in SQL Server 2016:
• http://www.sqlpassion.at/archive/2015/11/09/tempdb-changes-in-sql-server-2016/
UNIQUE AND NOT DULL
www.MyGareth.com
Thanks
eMail : [email protected]
Twitter : @GarethSwan
Website : www.MyGareth.com
Products
BI products to convert to a Microsoft BI platform
and simplify development on the platform.
UNIQUE AND NOT DULL
Services
Speed development through training and rapid
development services from Pragmatic Works.
Foundation
Helping those who do not have the means to
get into information technology and to achieve
their dreams.
www.MyGareth.com