کتاب آموزش SQL 2016 نسخه اصلی - انگلیسی Contents Chapter 2 Better security ........................................................................................................................ 1 Always Encrypted ..................................................................................................................................................................... 1 Getting started with Always Encrypted ...................................................................................................................... 1 Creating a table with encrypted values ...................................................................................................................... 7 CREATE TABLE statement for encrypted columns ................................................................................................. 7 Migrating existing tables to Always Encrypted ....................................................................................................... 9 Row-Level Security ............................................................................................................................................................... 11 Creating inline table functions .................................................................................................................................... 11 Creating security policies .............................................................................................................................................. 14 Using block predicates ................................................................................................................................................... 15 Dynamic data masking ....................................................................................................................................................... 15 Dynamic data masking of a new table .................................................................................................................... 16 Dynamic data masking of an existing table .......................................................................................................... 16 Understanding dynamic data masking and permissions ................................................................................. 17 Masking encrypted values ............................................................................................................................................ 18 Using dynamic data masking in SQL Database.................................................................................................... 18 Chapter 3 Higher availability ............................................................................................................... 20 AlwaysOn Availability Groups .......................................................................................................................................... 20 Supporting disaster recovery with basic availability groups........................................................................... 21 Using group Managed Service Accounts ............................................................................................................... 23 Triggering failover at the database level ................................................................................................................ 23 Supporting distributed transactions ......................................................................................................................... 24 Scaling out read workloads .......................................................................................................................................... 25 Defining automatic failover targets .......................................................................................................................... 26 Reviewing the improved log transport performance ........................................................................................ 27 Windows Server 2016 Technical Preview high-availability enhancements .................................................... 28 Creating workgroup clusters ....................................................................................................................................... 29 Configuring a cloud witness ........................................................................................................................................ 30 Using Storage Spaces Direct ....................................................................................................................................... 32 v Introducing site-aware failover clusters .................................................................................................................. 33 Windows Server Failover Cluster logging .............................................................................................................. 33 Performing rolling cluster operating system upgrades .................................................................................... 33 Chapter 4 Improved database engine ................................................................................................ 35 TempDB enhancements ..................................................................................................................................................... 35 Configuring data files for TempDB ........................................................................................................................... 36 Eliminating specific trace flags .................................................................................................................................... 37 Query Store ............................................................................................................................................................................. 38 Enabling Query Store...................................................................................................................................................... 38 Understanding Query Store components .............................................................................................................. 39 Reviewing information in the query store .............................................................................................................. 40 Using Force Plan ............................................................................................................................................................... 42 Managing the query store ............................................................................................................................................ 43 Tuning with the query store ........................................................................................................................................ 44 Stretch Database ................................................................................................................................................................... 44 Understanding Stretch Database architecture ..................................................................................................... 45 Security and Stretch Database .................................................................................................................................... 45 Identifying tables for Stretch Database ................................................................................................................... 46 Configuring Stretch Database ..................................................................................................................................... 47 Monitoring Stretch Database ...................................................................................................................................... 48 Backup and recovery with Stretch Database ......................................................................................................... 49 Chapter 6 More analytics ...................................................................................................................... 50 Tabular enhancements ....................................................................................................................................................... 50 Accessing more data sources with DirectQuery .................................................................................................. 51 Modeling with a DirectQuery source ....................................................................................................................... 51 Working with calculated tables .................................................................................................................................. 54 Bidirectional cross-filtering .......................................................................................................................................... 56 Writing formulas ............................................................................................................................................................... 60 Introducing new DAX functions ................................................................................................................................. 60 Using variables in DAX ................................................................................................................................................... 63 R integration ........................................................................................................................................................................... 64 Installing and configuring R Services ....................................................................................................................... 64 Getting started with R Services ................................................................................................................................... 65 Using an R Model in SQL Server ................................................................................................................................ 74 Chapter 7 Better reporting ................................................................................................................... 77 Report content types ........................................................................................................................................................... 77 Paginated report development enhancements ........................................................................................................ 77 Introducing changes to paginated report authoring tools ............................................................................. 78 vi Exploring new data visualizations .............................................................................................................................. 79 Managing parameter layout in paginated reports ............................................................................................. 84 Mobile report development ............................................................................................................................................. 85 KPI development ................................................................................................................................................................... 85 Report access enhancements .......................................................................................................................................... 86 Accessing reports with modern browsers .............................................................................................................. 86 Viewing reports on mobile devices........................................................................................................................... 88 Printing without ActiveX ................................................................................................................................................ 88 Exporting to PowerPoint ............................................................................................................................................... 90 Pinning reports to Power BI ......................................................................................................................................... 92 Managing subscriptions ................................................................................................................................................ 93 About the authors ................................................................................................................................. 96