Home Page Icon
Home Page
Table of Contents for
Title
Close
Title
by Peter A Carter
Pro SQL Server Administration
Cover
Title
Copyright
Dedication
Contents at a Glance
Contents
About the Author
About the Technical Reviewers
Part I: Installing and Configuring SQL Server
Chapter 1: Planning the Deployment
Editions and License Models
Hardware Considerations
Specifying Strategic Minimum Requirements
Storage
Operating Systems Considerations
Configuring the Operating System
Azure Options
Azure SQL Database
Azure SQL Server Virtual Machine
Selecting Features
Database Engine Service
Analysis Services
Reporting Services
Reporting Services Add-in for SharePoint Products
Data Quality Client
Client Connectivity Tools
Integration Services
Client Tools Backward Compatibility
Client Tools SDK
Documentation Components
Management Tools
Distributed Replay Controller
Distributed Replay Client
SQL Client Connectivity SDK
Master Data Services
Summary
Chapter 2: GUI Installation
Installation Center
The Planning Tab
The Installation Tab
The Maintenance Tab
The Tools Tab
The Resources Tab
The Advanced Tab
The Options Tab
Installing a Stand-Alone Database Engine Instance
Preparation Steps
The Feature Selection Page
The Instance Configuration Page
Selecting Service Accounts
Choosing the Collation
Provisioning Instance Security
Configuring the Instance
Configuring Distributed Replay
Completing the Installation
Summary
Chapter 3: Server Core Installation
Considerations for Server Core Installations
Installing an Instance
Required Parameters
Basic Installation
Smoke Tests
Troubleshooting the Installation
Optional Parameters
Product Update
Using a Config File
Automatic Installation Routines
Summary
Chapter 4: Configuring the Instance
Instance Configuration
Using sp_configure
Processor and Memory Configuration
Trace Flags
Ports and Firewalls
Process of Communication
Ports Required By SQL Server
Configuring the Port That the Instance Will Listen On
Implementing Windows Firewall Rules
System Databases
mssqlsystemresource (Resource)
MSDB
Master
Model
TempDB
Buffer Pool Extension
Uninstall an Instance
Uninstall from Control Panel
Uninstall from PowerShell
Summary
Part II: Database Administration
Chapter 5: Files and Filegroups
Data Storage
Filegroups
FILESTREAM Filegroups
Memory-Optimized Filegroups
Strategies for Structured Filegroups
Strategies for Memory-Optimized Filegroups
File and Filegroup Maintenance
Adding Files
Expanding Files
Shrinking Files
Log Maintenance
Recovery Model
Log File Count
Shrinking the Log
Log Fragmentation
Summary
Chapter 6: Configuring Tables
Table Partitioning
Partitioning Concepts
Implementing Partitioning
Monitoring Partitioned Tables
Sliding Windows
Partition Elimination
Table Compression
Row Compression
Page Compression
Columnstore Compression
Implementing Compression
Memory-Optimized Tables
Durability
Creating and Managing Memory-Optimized Tables
Performance Profile
Table Memory Optimization Advisor
Limitations of Memory-Optimized Tables
Natively Compiled Objects
Summary
Chapter 7: Indexes and Statistics
Clustered Indexes
Tables without a Clustered Index
Tables with a Clustered Index
Clustering the Primary Key
Performance Considerations for Clustered Indexes
Administering Clustered Indexes
Nonclustered Indexes
Covering Indexes
Administering Nonclustered Indexes
Performance Considerations
Filtered Indexes
Indexes for Specialized Application
Columnstore Indexes
Clustered Columnstore Indexes
Nonclustered Columnstore Indexes
Performance Considerations for Columnstore Indexes
In-memory Indexes
XML Indexes
Maintaining Indexes
Index Fragmentation
Missing Indexes
Partitioned Indexes
Statistics
Filtered Statistics
Incremental Statistics
Managing Statistics
Summary
Chapter 8: Database Consistency
Consistency Errors
Understand Consistency Errors
Detecting Consistency Errors
System Database Corruption
DBCC CHECKDB
Checking for Errors
Fixing Errors
Emergency Mode
Other DBCC Commands for Corruption
Consistency Checks on VLDBs
DBCC CHECKDB with PHYSICAL_ONLY
Backing Up WITH CHECKSUM and DBCC CHECKALLOC
Splitting the Workload
Offloading to a Secondary Server
Summary
Part III: Security, Resilience, and Scaling
Chapter 9: SQL Server Security Model
Security Hierarchy
Implementing Instance-Level Security
Server Roles
Logins
Granting Permissions
Implementing Database-Level Security
Database Roles
Schemas
Creating and Managing Contained Users
Implementing Object-Level Security
Ownership Chaining
Server Audit
Creating a Server Audit
Creating a Server Audit Specification
Enabling and Invoking Audits
Database Audit Specifications
Auditing the Audit
Summary
Chapter 10: Encryption
Encryption Hierarchy
Encryption Concepts
SQL Server Encryption Concepts
Transparent Data Encryption
Implementing TDE
Managing TDE
Managing Cell-Level Encryption
Accessing Encrypted Data
Performance Considerations for Cell-Level Encryption
Summary
Chapter 11: High Availability and Disaster Recovery Concepts
Availability Concepts
Level of Availability
Recovery Point Objective and Recovery Time Objective
Cost of Downtime
Classification of Standby Servers
High Availability and Recovery Technologies
AlwaysOn Failover Clustering
Database Mirroring
AlwaysOn Availability Groups
Log Shipping
Combining Technologies
Summary
Chapter 12: Implementing Clustering
Building the Cluster
Installing the Failover Cluster Feature
Creating the Cluster
Configuring the Cluster
Building the AlwaysOn Failover Cluster Instance
Preparation Steps
Cluster-Specific Steps
Installing the Instance with PowerShell
Adding a Node
Managing a Cluster
Moving the Instance between Nodes
Removing a Node from the Cluster
Summary
Chapter 13: Implementing AlwaysOn Availability Groups
Implementing High Availability with AlwaysOn Availability Groups
Configuring SQL Server
Creating the Availability Group
Performance Considerations for Synchronous Commit Mode
Implementing Disaster Recovery with Availability Group
Configuring the Cluster
Configuring the Availability Group
Managing AlwaysOn Availability Groups
Failover
Synchronizing Uncontained Objects
Monitoring
Adding Multiple Listeners
Other Administrative Considerations
Summary
Chapter 14: Implementing Log Shipping
Implementing Log Shipping for DR
GUI Configuration
T-SQL Configuration
Log Shipping Maintenance
Failing Over Log Shipping
Switching Roles
Monitoring
Summary
Chapter 15: Backups and Restores
Backup Fundamentals
Recovery Models
Changing the Recovery Model
Backup Types
Backup Media
Backup Strategies
Full Backup Only
Full and Transaction Log Backups
Full, Differential, and Transaction Log Backups
Filegroup Backups
Partial Backup
Backing Up a Database
Backing Up in SQL Server Management Studio
Backing Up via T-SQL
Restoring a Database
Restoring in SQL Server Management Studio
Restoring via T-SQL
Restoring to a Point in Time
Restoring Files and Pages
Restoring a File
Restoring a Page
Piecemeal Restores
Summary
Chapter 16: Scaling Workloads
Database Snapshots
Implementing Database Snapshots
Recovering Data from a Snapshot
Replication
Replication Concepts
Types of Replication
Implementing Transactional Replication
Adding AlwaysOn Readable Secondary Replicas
Summary
Part IV: Monitoring and Maintenance
Chapter 17: SQL Server Metadata
Introducing Metadata Objects
Server- and Instance-Level Metadata
Exposing Registry Values
Exposing Service Details
Analyzing Buffer Cache Usage
Metadata for Capacity Planning
Exposing File Stats
Using File Stats for Capacity Analysis
Metadata for Troubleshooting and Performance Tuning
Retrieving Perfmon Counters
Analyzing Waits
Finding and Tuning Expensive Queries
Metadata Driven Automation
Dynamically Cycling Database Snapshots
Rebuilding Only Fragmented Indexes
Summary
Chapter 18: Locking and Blocking
Understanding Locking
Lock Granularity
Locking Behaviors for Online Maintenance
Lock Compatibility
Lock Partitioning
Understanding Deadlocks
How Deadlocks Occur
Minimizing Deadlocks
Understanding Transactions
Transactional Properties
Transaction with In-Memory OLTP
Isolation Levels
Cross-Container Transactions
Retry Logic
Observing Transactions, Locks, and Deadlocks
Observing Transactions
Observing Locks and Contention
Observing Deadlocks
Summary
Chapter 19: Extended Events
Extended Events Concepts
Packages
Events
Targets
Actions
Predicates
Types and Maps
Sessions
Creating an Event Session
Using the New Session Wizard
Using the New Session Dialog Box
Using T-SQL
Viewing the Collected Data
Analyzing Data with Data Viewer
Analyzing Data with T-SQL
Correlating Extended Events with Operating System Data
Correlating Events with Perfmon Data
Integrating Event Sessions with Operating System–Level Events
Summary
Chapter 20: Distributed Replay
Distributed Replay Concepts
Distributed Replay Components
Distributed Replay Architecture
Configuring the Environment
Configuring the Controller
Configuring Clients
Configuring the Replay
Working with Distributed Replay
Synchronizing the Target
Creating a Trace
Replaying the Trace
Summary
Chapter 21: Automating Maintenance Routines
SQL Server Agent
SQL Server Agent Concepts
SQL Server Agent Security
Creating SQL Server Agent Jobs
Monitoring and Managing Jobs
Creating Alerts
Multiserver Jobs
Configuring the MSX and TSX Servers
Creating Master Jobs
Managing Target Servers
Maintenance Plans
Creating a Maintenance Plan with the Wizard
Creating a Maintenance Plan Manually
Automating Administration with SSIS
Summary
Chapter 22: Policy-Based Management
PBM Concepts
Facets
Conditions
Targets
Policies
Central Management Servers
Creating Policies
Creating Simple Policies
Creating an Advanced Policy
Managing Policies
Importing and Exporting Policies
Enterprise Management with Policies
Evaluating Policies with PowerShell
Summary
Chapter 23: Resource Governor
Resource Governor Concepts
Resource Pool
Workload Group
Classifier Function
Implementing Resource Governor
Creating Resource Pools
Creating Workload Groups
Creating a Classifier Function
Testing the Classifier Function
Monitoring Resource Governor
Monitoring with Performance Monitor
Monitoring with DMVs
Summary
Chapter 24: Triggers
DDL Triggers
Understanding DDL Triggers
Implementing DDL Triggers
Logon Triggers
Controlling Trigger Order
Summary
Part V: Managing a Hybrid Cloud Environment
Chapter 25: Cloud Backups and Restores
Understanding Cloud Backups
Simple Back Up and Restore to Windows Azure
Creating a Container
Backing Up a Database
Viewing Backup Files from SQL Server Management Studio
Restoring from a Backup
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
Summary
Chapter 26: SQL Data Files in Windows Azure
Understanding SQL Data Files in Windows Azure
Implementing SQL Data Files in Windows Azure
Preparing Windows Azure
Preparing the SQL Server Instance
Creating a Database
Monitoring and Managing SQL Server Data Files in Windows Azure
Summary
Chapter 27: Migrating to the Cloud
Working with Azure SQL Database
Creating an Azure SQL Database
Migrating a Database to Azure SQL Database
Managing Azure SQL Database
Migrating to an Azure VM
Creating a VM
Deploying a Database
Summary
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Cover
Next
Next Chapter
Copyright
Pro SQL Server Administration
Peter A Carter
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset