Contents
Part I: Installing and Configuring SQL Server
Chapter 1: Planning the Deployment
Specifying Strategic Minimum Requirements
Operating Systems Considerations
Configuring the Operating System
Azure SQL Server Virtual Machine
Reporting Services Add-in for SharePoint Products
Client Tools Backward Compatibility
Installing a Stand-Alone Database Engine Instance
The Instance Configuration Page
Provisioning Instance Security
Configuring Distributed Replay
Chapter 3: Server Core Installation
Considerations for Server Core Installations
Troubleshooting the Installation
Automatic Installation Routines
Chapter 4: Configuring the Instance
Processor and Memory Configuration
Configuring the Port That the Instance Will Listen On
Implementing Windows Firewall Rules
mssqlsystemresource (Resource)
Part II: Database Administration
Chapter 5: Files and Filegroups
Strategies for Structured Filegroups
Strategies for Memory-Optimized Filegroups
File and Filegroup Maintenance
Creating and Managing Memory-Optimized Tables
Table Memory Optimization Advisor
Limitations of Memory-Optimized Tables
Chapter 7: Indexes and Statistics
Tables without a Clustered Index
Performance Considerations for Clustered Indexes
Administering Clustered Indexes
Administering Nonclustered Indexes
Indexes for Specialized Application
Nonclustered Columnstore Indexes
Performance Considerations for Columnstore Indexes
Chapter 8: Database Consistency
Other DBCC Commands for Corruption
DBCC CHECKDB with PHYSICAL_ONLY
Backing Up WITH CHECKSUM and DBCC CHECKALLOC
Offloading to a Secondary Server
Part III: Security, Resilience, and Scaling
Chapter 9: SQL Server Security Model
Implementing Instance-Level Security
Implementing Database-Level Security
Creating and Managing Contained Users
Implementing Object-Level Security
Creating a Server Audit Specification
SQL Server Encryption Concepts
Managing Cell-Level Encryption
Performance Considerations for Cell-Level Encryption
Chapter 11: High Availability and Disaster Recovery Concepts.
Recovery Point Objective and Recovery Time Objective
Classification of Standby Servers
High Availability and Recovery Technologies
Chapter 12: Implementing Clustering
Installing the Failover Cluster Feature
Building the AlwaysOn Failover Cluster Instance
Installing the Instance with PowerShell
Moving the Instance between Nodes
Removing a Node from the Cluster
Chapter 13: Implementing AlwaysOn Availability Groups
Implementing High Availability with AlwaysOn Availability Groups
Creating the Availability Group
Performance Considerations for Synchronous Commit Mode
Implementing Disaster Recovery with Availability Group
Configuring the Availability Group
Managing AlwaysOn Availability Groups
Synchronizing Uncontained Objects
Other Administrative Considerations
Chapter 14: Implementing Log Shipping
Implementing Log Shipping for DR
Chapter 15: Backups and Restores
Full and Transaction Log Backups
Full, Differential, and Transaction Log Backups
Backing Up in SQL Server Management Studio
Restoring in SQL Server Management Studio
Implementing Database Snapshots
Recovering Data from a Snapshot
Implementing Transactional Replication
Adding AlwaysOn Readable Secondary Replicas
Part IV: Monitoring and Maintenance
Chapter 17: SQL Server Metadata
Server- and Instance-Level Metadata
Metadata for Capacity Planning
Using File Stats for Capacity Analysis
Metadata for Troubleshooting and Performance Tuning
Finding and Tuning Expensive Queries
Dynamically Cycling Database Snapshots
Rebuilding Only Fragmented Indexes
Chapter 18: Locking and Blocking
Locking Behaviors for Online Maintenance
Transaction with In-Memory OLTP
Observing Transactions, Locks, and Deadlocks
Observing Locks and Contention
Using the New Session Dialog Box
Analyzing Data with Data Viewer
Correlating Extended Events with Operating System Data
Correlating Events with Perfmon Data
Integrating Event Sessions with Operating System–Level Events
Chapter 20: Distributed Replay
Distributed Replay Architecture
Working with Distributed Replay
Chapter 21: Automating Maintenance Routines
Creating SQL Server Agent Jobs
Configuring the MSX and TSX Servers
Creating a Maintenance Plan with the Wizard
Creating a Maintenance Plan Manually
Automating Administration with SSIS
Chapter 22: Policy-Based Management
Importing and Exporting Policies
Enterprise Management with Policies
Evaluating Policies with PowerShell
Implementing Resource Governor
Creating a Classifier Function
Testing the Classifier Function
Monitoring with Performance Monitor
Part V: Managing a Hybrid Cloud Environment
Chapter 25: Cloud Backups and Restores
Simple Back Up and Restore to Windows Azure
Viewing Backup Files from SQL Server Management Studio
SQL Server Managed Backup to Windows Azure
Understanding Managed Backup to Windows Azure
Configuring Managed Backup to Windows Azure for an Individual Database
Configuring Managed Backup to Windows Azure for an Instance
Managed Backup to Windows Azure Advanced Configuration
Disabling Managed Backup to Windows Azure
Chapter 26: SQL Data Files in Windows Azure
Understanding SQL Data Files in Windows Azure
Implementing SQL Data Files in Windows Azure
Preparing the SQL Server Instance
Monitoring and Managing SQL Server Data Files in Windows Azure
Chapter 27: Migrating to the Cloud
Working with Azure SQL Database
Creating an Azure SQL Database