SQL Server 2016 What Microsoft Say SQL Server

SQL Server 2016 What Microsoft Say… § “SQL Server 2016 is the biggest leap forward in Microsoft data platform history with features that increase performance, simplify management, and transform your data into actionable insights—all on a database that has the least vulnerabilities of any major platform. ” § “The world’s fastest and most price-performant database for HTAP (Hybrid Transactional and Analytical Processing) with updateable, in-memory columnstores and advanced analytics through deep integration with R Services. ” § “Customers can gain tremendous performance improvement by simply upgrading to SQL Server 2016 without application changes (e. g. queries will run up to 34 x faster). In addition to leading performance benchmarks, SQL Server 2016 also delivers top price/performance for both workloads. ” 1 | 20/01/2017 David Postlethwaite

SQL Server 2016 New Features David Postlethwaite 20/01/2017 David Postlethwaite

Our Sponsors 3 | 20/01/2017 David Postlethwaite

About Me § David Postlethwaite § Liverpool Victoria LV= § § § SQL and Oracle DBA MCSE 2012 Data Platform MCITP 2008, 2005 Oracle OCA 25 years IT Experience 7 years as DBA § david@clunyweb. co. uk § https: //www. linkedin. com/in/davidpostlethwaite § Blog: gethynellis. com 4 | 20/01/2017 David Postlethwaite

SQL Server 2016 What Microsoft Say… § § § “SQL Server 2016 is the biggest leap forward in Microsoft data platform history with features that increase performance, simplify management, and transform your data into actionable insights—all on a database that has the least vulnerabilities of any major platform. ” “The world’s fastest and most price-performant database for HTAP (Hybrid Transactional and Analytical Processing) with updateable, in-memory columnstores and advanced analytics through deep integration with R Services. ” “Customers can gain tremendous performance improvement by simply upgrading to SQL Server 2016 without application changes (e. g. queries will run up to 34 x faster). In addition to leading performance benchmarks, SQL Server 2016 also delivers top price/performance for both workloads. ” 5 | 20/01/2017 David Postlethwaite

Specifications 13. 0. xxxx Feature Enterprise Standard Express with Advanced Services Developer CPU DBMS OS Maximum Limited to lesser of 4 Limited to lesser of 1 sockets or 24 cores 1 socket or 4 cores OS Maximum CPU SSAS OS Maximum Limited to lesser of 4 Limited to lesser of 1 sockets or 24 cores 1 socket or 4 cores OS Maximum Memory DBMS OS Maximum (4 TB) 128 GB 1 GB OS Maximum Memory SSAS OS Maximum Tabular: 16 GB MOLAP: 64 GB N/A OS Maximum Memory SSRS OS Maximum 64 GB N/A 4 GB OS Maximum DB Size 524 PB 10 GB 524 PB SQL Server 2016 Developer Edition free download at Visual Studio Dev Essentials https: //myprodscussu 1. app. vssubscriptions. visualstudio. com/downloads? pid=2057 SP 1 Update: Most “enterprise features” now available in all editions. SQL Server 2016 for Linux (v. Next) in preview. Release in the middle of 2017. 6 | 20/01/2017 David Postlethwaite

Discontinued & Deprecated Features § 64 bit Only – SSMS still 32 bit § Compatibility level 90 (2005) is discontinued § Deprecated – Due to be removed “soon” § § § Data types: text, ntext, image sp_attach_db, sp_adduser and many other similar “sp_” procs Mirroring – Now use “Always On Availability Groups” BACKUP … TO TAPE SQL Trace stored procedures, functions, and catalog views Get into habit of ending lines with a semi colon “; ” § § https: //msdn. microsoft. com/en-us/library/ms 144262. aspx https: //msdn. microsoft. com/en-us/library/ms 143729. aspx 7 | 20/01/2017 David Postlethwaite

Installation Changes SSMS is now separate (web only) installation https: //msdn. microsoft. com/en-us/library/mt 238290. aspx 8 | 20/01/2017 David Postlethwaite

Installation Improvements Perform Volume Maintenance Tasks Instant File Initialization SP 1: New DMV columns Instant File Initialisation SELECT instant_file_initialization_enabled FROM sys. dm_server_services; Lock Pages in Memory SELECT sql_memory_model, sql_memory_model_desc FROM sys. dm_os_sys_info https: //msdn. microsoft. com/en-us/library/ms 175935. aspx 9 | 20/01/2017 David Postlethwaite

Installation Improvements Data Folders 10 | 20/01/2017 David Postlethwaite

Installation Improvements Multiple Temp. DB Database Files 11 | 20/01/2017 David Postlethwaite

Tempdb Contention and File Growth § Trace Flags 1117 and 1118 Enabled § 1118 avoids contention in tempdb § § § 1117 stops uneven file growth 1117 Good for Temp. DB But not always wanted for User Databases § SP 1 Update § Windows Error Log reports uneven tempdb files Date Log 04/01/2017 11: 58: 20 SQL Server (Current - 04/01/2017 11: 57: 00) The tempdb database data files are not configured with the same initial size and autogrowth settings. To reduce potential allocation contention, the initial size and autogrowth of the files should be same. 12 | 20/01/2017 David Postlethwaite

Dynamic Data Masking Enterprise and Standard Mask production data in UAT without running obfuscation scripts CREATE TABLE [Sales]. [Customer. PII]( [Customer. ID] [int] NOT NULL, [First. Name] [dbo]. [Name] NOT NULL, [Phone. Number] [nvarchar](25) MASKED WITH (FUNCTION = 'default()') NULL, [Email. Address] [nvarchar](50) MASKED WITH (FUNCTION = 'email()') NULL, [Last. Name] [dbo]. [Name] MASKED WITH (FUNCTION='partial(2, "XXXX", 2)') NOT NULL ) ON [PRIMARY] GRANT UNMASK TO Sales. Persons 13 | 20/01/2017 David Postlethwaite

Row Level Security Enterprise and Standard § Control access to rows in a table based on the characteristics of the user (e. g. group membership or execution context). § The access restriction logic is located in the database rather than application. § The database applies the access restrictions every time that data is viewed or updated. From anywhere. § Makes security more reliable and robust by reducing the surface area of your security system. § § § ALTER SECURITY POLICY Security. customer. Policy ALTER FILTER PREDICATE Security. customer. Access(Territory. ID) ON Sales. Customer. PII, ALTER BLOCK PREDICATE Security. customer. Access(Territory. ID) ON Sales. Customer. PII 14 | 20/01/2017 David Postlethwaite

Always Encrypted Enterprise. And Standard with SP 1 Encrypt columns using a certificate CREATE TABLE [Sales]. [Customer. PII]( [Credit. Card. Number] [nvarchar](25) COLLATE Latin 1_General_BIN 2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto 1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL, ) ON [PRIMARY] Unencrypted values can only be seen from computer with the certificate installed Need extra parameter in connection string column encryption setting=enabled Currently Only Supported with ADO. NET 4. 6. JDBC 6. 0, and ODBC 13. 1 Must use a parametrised query to update the data 15 | 20/01/2017

Temporal Tables Enterprise and Standard § System-versioned Tables § Allow SQL to automatically keep history of the data in a table § Typical uses § § Audit. With temporal tables you can find out what values a specific entity has had over its entire lifetime. Slowly changing dimensions. A system-versioned table exactly behaves like a dimension with type 2 changing behavior for all of its columns. Repair record-level corruptions. Think of it as a sort of back-up mechanism on a single table. Accidentally deleted a record? Retrieve it from the history table and insert it back into the main table. WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Person]. [Person_History])); § Requires two extra columns for start and end date of the row § Not a replacement for Change Data Capture 16 | 20/01/2017 David Postlethwaite

Stretch Database Enterprise and Standard § Take advantage of “cheap” Azure storage § Split a table in two Place “old” data in a database in Azure Leave current data “on premises” § Query knows if data is in current table or archive table and returns merged results seamlessly § SQL will move data to Azure when it becomes “old” § Lots of limitations § Not as cheap as it looks (minimum of € 1500/month) § => aimed at tables with billions of rows 17 | 20/01/2017 David Postlethwaite

Query Store Enterprise and Standard § Historical Query Plans § § Collects Information on Query Plans for a database over Time Search for problematic or regressed queries Set FORCE PLAN policies to force a query to use an old better execution plan Identify expensive queries by CPU, memory I/O etc. 18 | 20/01/2017 David Postlethwaite

Live Query Statistics Enterprise and Standard Watch Query Plans run in real time See Complex queries being made 19 | 20/01/2017 David Postlethwaite

SSMS Plan Comparison Tool Enterprise and Standard Save execution plans to disk Then compare current plan to a saved file 20 | 20/01/2017 David Postlethwaite

T-SQL Enhancements § Drop If Exists § Old Way IF EXISTS (SELECT 1 FROM sys. objects WHERE object_id = OBJECT_ID(N'[dbo]. [Table 1]') AND [type] IN (N'U')) AGGREGATE SCHEMA USER DROP TABLE [dbo]. [Table 1]; § New Way DROP TABLE IF EXISTS [Person]. [Country. Region]; DROP TRIGGER IF EXISTS trg_Person. Address; ALTER TABLE t 1 DROP CONSTRAINT IF EXISTS t 1_column 1_pk; ALTER TABLE t 1 DROP COLUMN IF EXISTS ID; 21 | 20/01/2017 David Postlethwaite PROCEDURE DEFAULT TABLE SECURITY POLICY ASSEMBLY VIEW ROLE FUNCTION TRIGGER SEQUENCE VIEW INDEX RULE TYPE DATABASE SYNONYM

T-SQL Enhancements § SP 1 Update § CREATE OR ALTER § § Stored Procedures Functions Triggers Views § § § CREATE OR ALTER PROCEDURE New. Proc 1 AS BEGIN PRINT N'SQL Saturday is Great'; END GO 22 | 20/01/2017 David Postlethwaite

T-SQL Enhancements Enterprise and Standard § TRUNCATE TABLE with PARTITION § TRUNCATE TABLE dbo. my. Table WITH (PARTITIONS (1, 5 TO 8)); 23 | 20/01/2017 David Postlethwaite

T-SQL Enhancements § Online DDL changes ALTER TABLE dbo. Table 1 ALTER COLUMN x VARCHAR(255) NOT NULL WITH (ONLINE = ON); 24 | 20/01/2017 David Postlethwaite Enterprise Only

T-SQL Enhancements Enterprise and Standard § Changes to CHECKDB § Improvements to make it faster § Check Scanner uses lock free design § Extended Logical Checks Removed from default § MAXDOP option 25 | 20/01/2017 David Postlethwaite

T-SQL Enhancements Enterprise and Standard § Compress and De. Compress § Gzip compression § § INSERT INTO Table 1 (name, info) VALUES ('David', COMPRESS('SQL Server 2016 is here')); SELECT name, CAST(DECOMPRESS(info) AS VARCHAR(MAX)) 'DECOMPRESSED-Value' FROM Table 1; GO § Reduce table size § Reduce bandwidth between database and application § Not the same as Page and Row Compression 26 | 20/01/2017 David Postlethwaite

Column Store Index Enterprise. And Standard with SP 1 § Introduced in SQL 2012 § Major Improvements in SQL 2016 § A clustered columnstore index can have one or more nonclustered (B-tree) indexes § In-memory tables can have columnstore index § Support for primary keys and foreign keys § compression delay option § Microsoft claim… § 100 x query performance on large data tables § 10 x data compression 29 | 20/01/2017 David Postlethwaite

Enterprise. And Standard with SP 1 Hekaton (In Memory OLTP) § Introduced with SQL Server 2014 § § Gives in-memory tables No Locking / blocking in the table § SQL 2016 Improvements § § § Maximum size from 250 MB to 2 GB File. Stream data Transparent Data Encryption (TDE) Foreign Keys Check Constraints § § § Up to 100 x faster than traditional tables Combine with Column Store index for supercharged performance Requires lots of Memory ! § WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA ) 30 | 20/01/2017 David Postlethwaite

Poly. Base compute node Poly. Base head node 31 | 20/01/2017 David Postlethwaite Enterprise and Standard* Standard and Enterprise Only

R Enterprise and Standard* § Primary analysis language for the majority of data scientists and statisticians § R now integrated into SQL Server Basic R integration Advanced R Integration R Server 32 | 20/01/2017 David Postlethwaite All Editions Enterprise Only

JSON Support § § § § DECLARE @json NVARCHAR(4000) SET @json = N'{ "info": { "type": 1, "address": { "town": "Bristol", "county": "Avon", "country": "England" }, "tags": ["Sport", "Water polo"] }, "type": "Basic" }' § § § -- Using JSON_VALUE SELECT JSON_VALUE(@json, 33 | 20/01/2017 function '$. type') a, '$. info. type') b, '$. info. address. town') c, '$. info. tags[0]') d David Postlethwaite Enterprise and Standard

Time for a Demo 34 | 20/01/2017 David Postlethwaite

Feature Comparison § SP 1 has changed Standard Edition features § § https: //msdn. microsoft. com/en-us/library/cc 645993. aspx 35 | 20/01/2017 David Postlethwaite

Example Databases and Scripts § Wide World Importers § https: //www. mssqltips. com/sqlservertip/4391/installing-new-sqlserver-sample-databases-wideworldimporters/ § Adventure. Works 2016 CTP 3 § https: //www. microsoft. com/en-us/download/details. aspx? id=49502 36 | 20/01/2017 David Postlethwaite

Any Questions § Conclusion § Hopefully this has given you a head start when you start looking at SQL Server 2016 § Q & A § david@clunyweb. co. uk § Blog: gethynellis. com § Watch again on You. Tube (pre SP 1) § http: //tinyurl. com/postledm 37 | 20/01/2017 David Postlethwaite