Introducing Microsoft SQL Server 2016 - Ascent Technology

Introducing

Microsoft SQL Server 2016

Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

Stacia Varga, Denny Cherry, Joseph D'Antoni

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.

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 mspinput@. 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. 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

Visit us today at



?Hundreds of titles available ? Books, eBooks, and online resources from industry experts

? Free U.S. 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

Contents

Introduction.............................................................................................................................................. viii Who should read this book ..............................................................................................................................................viii Assumptions ....................................................................................................................................................................... viii This book might not be for you if..................................................................................................................................viii Organization of this book ................................................................................................................................................... ix Conventions and features in this book .......................................................................................................................... ix System requirements............................................................................................................................................................. 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 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 enhancements..................................................................................................................................... 11 Understanding multidimensional performance improvements .................................................................... 11 Understanding tabular performance improvements ......................................................................................... 12

iii 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 ............................................................................................................................................................... 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 Database.................................................................................................... 32

Chapter 3 Higher availability ............................................................................................................... 33 AlwaysOn Availability Groups .......................................................................................................................................... 33 Supporting disaster recovery with basic availability groups........................................................................... 34 Using group Managed Service Accounts ............................................................................................................... 36 Triggering failover at the database level ................................................................................................................ 36 Supporting distributed transactions......................................................................................................................... 37 Scaling out read workloads.......................................................................................................................................... 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 Store...................................................................................................................................................... 51

iv Contents

Understanding Query Store components .............................................................................................................. 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 tables............................................................................................................ 69 Querying temporal tables ............................................................................................................................................. 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

v Contents

Chapter 6 More analytics ...................................................................................................................... 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............................................................................................................................127 Managing parameter layout in paginated reports ...........................................................................................132 Mobile report development ...........................................................................................................................................133 Introducing the mobile report designer ...............................................................................................................133 Connecting data to mobile report elements.......................................................................................................135 Reviewing mobile report elements .........................................................................................................................138 Publishing mobile reports ..........................................................................................................................................147 KPI development.................................................................................................................................................................147 Report access enhancements ........................................................................................................................................148 Accessing reports with modern browsers ............................................................................................................148 Viewing reports on mobile devices.........................................................................................................................149 Printing without ActiveX..............................................................................................................................................150 Exporting to PowerPoint .............................................................................................................................................151 Pinning reports to Power BI .......................................................................................................................................153 Managing subscriptions ..............................................................................................................................................154

Chapter 8 Improved Azure SQL Database ........................................................................................ 158 Introduction to SQL Database .......................................................................................................................................158 Reviewing the evolution of SQL Database...........................................................................................................159 Understanding the pricing model ...........................................................................................................................160 Protecting data with high availability and disaster recovery ........................................................................162 SQL Database security ......................................................................................................................................................163

vi Contents

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download