Transcript SQLSat500-Bertrand-2014-2016x

STANDARD EDITION
USEFUL FEATURES FOR THE REST OF US?
No, you can’t have
Enterprise features
AGENDA
• Features in 2014 Standard Edition – Database Engine & Tooling
• Administration
• Performance
• Productivity
• Security
• High Availability
• Features in 2016 Standard Edition – Database Engine & Tooling
• Administration
• Performance
• Productivity
• Security
• High Availability
• Development Tips
SQL SERVER 2014
• Edition comparison:
• https://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx
2014 : ADMINISTRATION
• 128 GB RAM support (up from 64 GB)
• Managed backup to Azure
• Backup to URL (Azure blob)
• Data files in Windows Azure (blob)
2014 : PERFORMANCE
• New cardinality estimator – better plans based on better guesses
• Careful, there may be regressions
• Blog post (Joe Sack) : A First Look at the New SQL Server Cardinality Estimator
• Parallel SELECT INTO
• tempdb eager write fix – less likely to waste I/O
• Buffer pool extension – treat a local SSD like a page file
• Blog post (Klaus Aschenbrenner) : Buffer Pool Extensions in SQL Server 2014
• Note: single-threaded I/O queue; only beneficial with RAM deficiency & super slow SAN
• Delayed durability – trade resiliency for faster performance
• Blog post (Melissa Connors) : Delayed Durability while Purging Data
2014 : PRODUCTIVITY
• Inline index declaration – for standard indexes
• sys.dm_exec_query_profiles – shows plan operator progress
• Only populated for queries running with showplan on
• 2014 SSMS can’t *display* the data in motion, but 2016 can
2014 : SECURITY
• New server permissions:
• CONNECT ANY DATABASE
• IMPERSONATE ANY LOGIN
• SELECT ALL USER SECURABLES
• Backup Encryption
• Blog post : SQL Server 2014 : Native Backup Encryption
SQL SERVER 2016
• Edition comparisons should appear here soon:
• https://msdn.microsoft.com/en-us/library/cc645993(v=sql.130).aspx
• Right now, this page just links to the marketing site
2016 : ADMINISTRATION
• Setup : multiple tempdb data files, instant file initialization, better data/log defaults
• .NET 3.5 dependency is gone
• SSMS : check for updates, independent release cycle, .NET 3.5 requirement gone
SEPARATE SSMS
INSTANT FILE INITIALIZATION
MORE SENSIBLE TEMPDB
2016 : ADMINISTRATION
• Wider indexes – 32 columns (up from 16), 1,700 bytes (up from 900)
• More incoming foreign keys (10,000! up from 253)
• Index usage stats are now more persistent
• MAXDOP now supported for several DBCC check commands
• Many trace flag / server level behaviors can now be implemented at database level
• DATABASE SCOPED CONFIGURATION – MAXDOP, 4136, 4199, legacy CE
2016 : ADMINISTRATION
• Striped (block blob, up to 12.8 TB) and file-snapshot backups to Azure blob
• Managed backup now uses block blob, and supports
• System databases
• Databases in simple recovery
• Automatic and custom schedules
• Stretch database – offload storage and queries against older data
• Eight new SERVERPROPERTY arguments:
• InstanceDefaultDataPath, InstanceDefaultLogPath, ProductBuild, ProductBuildType, ProductMajorVersion,
ProductMinorVersion, ProductUpdateLevel, ProductUpdateReference
2016 : PERFORMANCE
• Automatic changes:
• Trace flags 610, 1117, 1118, 2371, 4136, 4199 now on by default
• 4199 only for databases in 130 compat mode
• ALTER DATABASE can control 1117 / 1118 for databases other than tempdb
• Another round of cardinality estimator fixes - TF9481 can still disable newest fixes
• Improvements in metadata latching in tempdb
• Batch mode operations (even for row store tables)
• Parallel INSERT INTO … SELECT FROM
2016 : PERFORMANCE
• New extended events to monitor / troubleshoot backup / restore / recovery
• Blog post : T-SQL Tuesday #67 : New Backup and Restore Extended Events
• New hints: MIN_GRANT_REQUEST/MAX_GRANT_REQUEST, NO_PERFORMANCE_SPOOL
• Query store – probably Enterprise only, but no official word yet
• Blog post (Ben Nevarez) : The SQL Server Query Store
2016 : PRODUCTIVITY
• JSON support
• Blog post : SQL Server 2016 : JSON Support
• More inline index support (filtered, CCI/NCCI)
• Temporal tables
• R support (even in Express!)
• PolyBase – query Hadoop / unstructured Azure blob using T-SQL
• New T-SQL
•
•
•
•
•
STRING_SPLIT() / STRING_ESCAPE()
COMPRESS() / DECOMPRESS()
AT TIME ZONE
DATEDIFF_BIG()
SESSION_CONTEXT()
2016 : PRODUCTIVITY
• Live query stats (sys.dm_exec_query_profiles) works in SSMS & Plan Explorer
• New diagnostic columns in sys.dm_exec_query_stats
• last_dop, max_dop, last_grant_kb, total_grant_kb, etc.
• New DMV for session-level wait stats - sys.dm_exec_session_wait_stats
• New DMF to replace DBCC INPUTBUFFER() - sys.dm_exec_input_buffer
• New DMV for function performance stats – sys.dm_exec_function_stats
• UTF-8 support (bulk)
• New DROP IF EXISTS syntax (but no CREATE IF NOT EXISTS)
2016 : PRODUCTIVITY
DEMO
2016 : SECURITY
• Row-level security
• Dynamic data masking
• Always encrypted
• HASHBYTES – 8K limit removed
2016 : HIGH AVAILABILITY
• Basic Availability Groups! (APPLAUSE!)
• Lots of limitations: single database, no listener support, no readable secondaries
• Blog post : SQL Server 2016 : Availability Group Enhancements
• Transactional replication to Azure
You might not need
Enterprise features
DEVELOPMENT TIPS
• Try to develop on Standard Edition if that’s your target
• If you develop on Enterprise or Developer Edition:
• Check sys.dm_db_persisted_sku_features
• Test deploying to Standard Edition often
• Connect items for making Developer act like Standard:
• #496380, #331297, #381744, #507277, #420180
• Try to match production hardware – you can fool SQL Server:
• DBCC OPTIMIZER_WHATIF, SETCPUWEIGHT, SETIOWEIGHT
• Try to match production data – you can fool SQL Server:
• Stats-only (clone) database
RESOURCES
• Simon Sabin assembled links to all the Data Driven Deep Dive videos (see March 14):
• http://sqlblogcasts.com/blogs/simons/archive/2016/03.aspx
• Keep up with developments throughout the 2016 RC phase:
• https://msdn.microsoft.com/en-us/library/bb500435.aspx
• SQL Server 2016 Resources :
• https://msdn.microsoft.com/en-us/library/dn237258.aspx
• SQL Server 2014 Resources :
• https://msdn.microsoft.com/en-us/library/dn237258(sql.120).aspx
THANK YOU!