Introducing Microsoft SQL Server 2016

1 Stacia Varga, Denny Cherry, Joseph D AntoniIntroducingMicrosoft SQL Server 2016 Mission-Critical Applications, Deeper Insights, Hyperscale 12/8/2016 9:28:57 PM Introducing Microsoft SQL Server 2016 Mission-Critical Applications, Deeper Insights,Hyperscale Cloud Stacia Varga, Denny Cherry, Joseph D Antoni PUBLISHED BY Microsoft Press A division of Microsoft Corporation One Microsoft Way Redmond, Washington 98052-6399 Copyright 2016 by Microsoft Corporation All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher.

2 ISBN: 978-1-5093-0195-9 Microsoft Press books are available through booksellers and distributors worldwide. If you need support related to this book, email Microsoft Press Support at Please tell us what you think of this book at This book is provided as-is and expresses the author s views and opinions. The views, opinions and information expressed in this book, including URL and other Internet website references, may change without notice. Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred. Microsoft and the trademarks listed at on the Trademarks webpage are trademarks of the Microsoft group of companies.

3 All other marks are property of their respective owners. Acquisitions and Development Editor: Devon Musgrave Project Editor: John Pierce Editorial Production: Flyingspress Cover: Twist Creative Seattle Hundreds of titles available Books, eBooks, and online resources from industry experts Free shipping eBooks in multiple formats Read on your computer, tablet, mobile device, or e-reader Print & eBook Best Value Packs eBook Deal of the Week Save up to 60% on featured titles Newsletter and special offers Be the first to hear about new releases, specials, and more Register your book Get additional benefits us today at iii Contents Contents Introduction.

4 Viii Who should read this book .. viii Assumptions .. viii This book might not be for you .. viii Organization of this book .. ix Conventions and features in this book .. ix System ix Prerelease software .. x Acknowledgments .. x Errata, updates, & book support .. x Free ebooks from Microsoft Press .. x We want to hear from you .. xi Stay in touch .. xi Chapter 1 Faster queries .. 1 In-Memory OLTP enhancements .. 1 Reviewing new features for memory-optimized tables .. 2 Scaling memory-optimized tables .. 2 Introducing native client enhancements .. 3 Exploring T-SQL enhancements .. 3 Managing memory-optimized tables.

5 5 Planning data migration to memory-optimized tables .. 6 In-memory analytics .. 8 Reviewing columnstore index enhancements .. 8 Getting started with in-memory analytics .. 9 Using filtered columnstore indexes .. 9 Analysis Services 11 Understanding multidimensional performance improvements .. 11 Understanding tabular performance improvements .. 12 iv Contents Chapter 2 Better security .. 14 Always Encrypted .. 14 Getting started with Always Encrypted .. 14 Creating a table with encrypted values .. 20 CREATE TABLE statement for encrypted columns .. 20 Migrating existing tables to Always Encrypted .. 22 Row-Level Security.

6 24 Creating inline table functions .. 25 Creating security policies .. 27 Using block predicates .. 28 Dynamic data masking .. 29 Dynamic data masking of a new table .. 29 Dynamic data masking of an existing table .. 30 Understanding dynamic data masking and permissions .. 30 Masking encrypted values .. 32 Using dynamic data masking in SQL 32 Chapter 3 Higher availability .. 33 AlwaysOn Availability Groups .. 33 Supporting disaster recovery with basic availability 34 Using group Managed Service Accounts .. 36 Triggering failover at the database level .. 36 Supporting distributed transactions .. 37 Scaling out read workloads.

7 38 Defining automatic failover targets .. 39 Reviewing the improved log transport performance .. 40 Windows Server 2016 Technical Preview high-availability enhancements .. 41 Creating workgroup clusters .. 42 Configuring a cloud witness .. 43 Using Storage Spaces Direct .. 45 Introducing site-aware failover clusters .. 46 Windows Server Failover Cluster logging .. 46 Performing rolling cluster operating system upgrades .. 46 Chapter 4 Improved database engine .. 48 TempDB enhancements .. 48 Configuring data files for TempDB .. 49 Eliminating specific trace flags .. 50 Query Store .. 51 Enabling Query 51 v Contents Understanding Query Store components.

8 52 Reviewing information in the query store .. 53 Using Force Plan .. 55 Managing the query store .. 56 Tuning with the query store .. 57 Stretch Database .. 57 Understanding Stretch Database architecture .. 58 Security and Stretch Database .. 58 Identifying tables for Stretch Database .. 59 Configuring Stretch Database .. 60 Monitoring Stretch Database .. 61 Backup and recovery with Stretch Database .. 62 Chapter 5 Broader data access .. 63 Temporal data .. 63 Creating a rowstore temporal table .. 64 Converting an existing table to temporal .. 68 Understanding the effect of data changes .. 68 Using memory-optimized temporal 69 Querying temporal tables.

9 71 Securing temporal tables .. 72 Managing data retention .. 72 Reviewing temporal metadata .. 76 JSON .. 77 Getting acquainted with JSON structures .. 78 Exporting data to JSON .. 78 Importing JSON data .. 81 Converting JSON data to a table structure .. 81 Using other built-in JSON functions .. 84 Indexing JSON data .. 86 PolyBase .. 87 Installing PolyBase .. 87 Scaling out with PolyBase .. 89 Creating PolyBase data objects .. 91 Viewing PolyBase objects in SSMS .. 95 Using T-SQL with PolyBase objects .. 95 Troubleshooting with PolyBase system views and DMVs .. 96 vi Contents Chapter 6 More analytics.

10 98 Tabular enhancements .. 98 Accessing more data sources with DirectQuery .. 99 Modeling with a DirectQuery source .. 99 Working with calculated tables .. 102 Bidirectional cross-filtering .. 104 Writing formulas .. 108 Introducing new DAX functions .. 108 Using variables in DAX .. 111 R integration .. 112 Installing and configuring R Services .. 112 Getting started with R Services .. 114 Using an R Model in SQL Server .. 122 Chapter 7 Better reporting .. 125 Report content types .. 125 Paginated report development enhancements .. 125 Introducing changes to paginated report authoring tools .. 126 Exploring new data visualizations.