Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

by Stacia Varga, Denny Cherry, Joseph D’Antoni
Released June 2016
Publisher(s): Microsoft Press
ISBN: 9781509301959

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

With Microsoft SQL Server 2016, a variety of new features and enhancements to the data platform deliver breakthrough performance, advanced security, and richer, integrated reporting and analytics capabilities. In this ebook, we introduce new security features: Always Encrypted, Row-Level Security, and dynamic data masking; discuss enhancements that enable you to better manage performance and storage: TemDB configuration, query store, and Stretch Database; review several improvements to Reporting Services; and also describe AlwaysOn Availability Groups, tabular enhancements, and R integration.

Table of contents

  1. Cover
  2. Title Page
  3. Copyright Page
  4. Contents
  5. Introduction
    1. Who should read this book
      1. Assumptions
    2. This book might not be for you if...
    3. Organization of this book
    4. Conventions and features in this book
    5. System requirements
    6. Prerelease software
    7. Acknowledgments
    8. Errata, updates, & book support
    9. Free ebooks from Microsoft Press
    10. We want to hear from you
    11. Stay in touch
  6. Chapter 1. Faster queries
    1. In-Memory OLTP enhancements
      1. Reviewing new features for memory-optimized tables
      2. Scaling memory-optimized tables
      3. Introducing native client enhancements
      4. Exploring T-SQL enhancements
      5. Managing memory-optimized tables
      6. Planning data migration to memory-optimized tables
    2. In-memory analytics
      1. Reviewing columnstore index enhancements
      2. Getting started with in-memory analytics
      3. Using filtered columnstore indexes
    3. Analysis Services enhancements
      1. Understanding multidimensional performance improvements
      2. Understanding tabular performance improvements
  7. Chapter 2. Better security
    1. Always Encrypted
      1. Getting started with Always Encrypted
      2. Creating a table with encrypted values
      3. CREATE TABLE statement for encrypted columns
      4. Migrating existing tables to Always Encrypted
    2. Row-Level Security
      1. Creating inline table functions
      2. Creating security policies
      3. Using block predicates
    3. Dynamic data masking
      1. Dynamic data masking of a new table
      2. Dynamic data masking of an existing table
      3. Understanding dynamic data masking and permissions
      4. Masking encrypted values
      5. Using dynamic data masking in SQL Database
  8. Chapter 3. Higher availability
    1. AlwaysOn Availability Groups
      1. Supporting disaster recovery with basic availability groups
      2. Using group Managed Service Accounts
      3. Triggering failover at the database level
      4. Supporting distributed transactions
      5. Scaling out read workloads
      6. Defining automatic failover targets
      7. Reviewing the improved log transport performance
    2. Windows Server 2016 Technical Preview high-availability enhancements
      1. Creating workgroup clusters
      2. Configuring a cloud witness
      3. Using Storage Spaces Direct
      4. Introducing site-aware failover clusters
      5. Windows Server Failover Cluster logging
      6. Performing rolling cluster operating system upgrades
  9. Chapter 4. Improved database engine
    1. TempDB enhancements
      1. Configuring data files for TempDB
      2. Eliminating specific trace flags
    2. Query Store
      1. Enabling Query Store
      2. Understanding Query Store components
      3. Reviewing information in the query store
      4. Using Force Plan
      5. Managing the query store
      6. Tuning with the query store
    3. Stretch Database
      1. Understanding Stretch Database architecture
      2. Security and Stretch Database
      3. Identifying tables for Stretch Database
      4. Configuring Stretch Database
      5. Monitoring Stretch Database
      6. Backup and recovery with Stretch Database
  10. Chapter 5. Broader data access
    1. Temporal data
      1. Creating a rowstore temporal table
      2. Converting an existing table to temporal
      3. Understanding the effect of data changes
      4. Using memory-optimized temporal tables
      5. Querying temporal tables
      6. Securing temporal tables
      7. Managing data retention
      8. Reviewing temporal metadata
    2. JSON
      1. Getting acquainted with JSON structures
      2. Exporting data to JSON
      3. Importing JSON data
      4. Converting JSON data to a table structure
      5. Using other built-in JSON functions
      6. Indexing JSON data
    3. PolyBase
      1. Installing PolyBase
      2. Scaling out with PolyBase
      3. Creating PolyBase data objects
      4. Viewing PolyBase objects in SSMS
      5. Using T-SQL with PolyBase objects
      6. Troubleshooting with PolyBase system views and DMVs
  11. Chapter 6. More analytics
    1. Tabular enhancements
      1. Accessing more data sources with DirectQuery
      2. Modeling with a DirectQuery source
      3. Working with calculated tables
      4. Bidirectional cross-filtering
      5. Writing formulas
      6. Introducing new DAX functions
      7. Using variables in DAX
    2. R integration
      1. Installing and configuring R Services
      2. Getting started with R Services
      3. Using an R Model in SQL Server
  12. Chapter 7. Better reporting
    1. Report content types
    2. Paginated report development enhancements
      1. Introducing changes to paginated report authoring tools
      2. Exploring new data visualizations
      3. Managing parameter layout in paginated reports
    3. Mobile report development
      1. Introducing the mobile report designer
      2. Connecting data to mobile report elements
      3. Reviewing mobile report elements
      4. Publishing mobile reports
    4. KPI development
    5. Report access enhancements
      1. Accessing reports with modern browsers
      2. Viewing reports on mobile devices
      3. Printing without ActiveX
      4. Exporting to PowerPoint
      5. Pinning reports to Power BI
      6. Managing subscriptions
  13. Chapter 8. Improved Azure SQL Database
    1. Introduction to SQL Database
      1. Reviewing the evolution of SQL Database
      2. Understanding the pricing model
      3. Protecting data with high availability and disaster recovery
    2. SQL Database security
      1. Configuring security
      2. Auditing SQL Database
      3. Encrypting data
      4. Securing data at the row level
      5. Dynamic data masking
      6. Developing secure applications
    3. Elastic database features
      1. Managing elastic scale
      2. Managing performance levels in elastic database pools
      3. Using elastic database jobs
      4. Distributing queries with elastic query
  14. Chapter 9. Introducing Azure SQL Data Warehouse
    1. Introduction to SQL Data Warehouse
    2. Security
    3. Scalability
      1. Scaling storage and compute resources
      2. Deciding how to scale
    4. Data loads
      1. Using Azure Data Factory
      2. Using PolyBase
      3. Using SSIS
    5. Statistics for SQL Data Warehouse
      1. Creating statistics
      2. Maintaining statistics
    6. Integration options
      1. Predictive modeling in Azure Machine Learning
      2. Working with streaming data in Azure Stream Analytics
      3. Analyzing data by using Power BI
  15. About the authors
  16. Visit us today at
  17. Hear about it first
  18. Survey
  19. Free ebooks
  20. Code Snippets

Product information

  • Title: Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud
  • Author(s): Stacia Varga, Denny Cherry, Joseph D’Antoni
  • Release date: June 2016
  • Publisher(s): Microsoft Press
  • ISBN: 9781509301959