You are previewing Pro SQL Server Administration.
O'Reilly logo
Pro SQL Server Administration

Book Description

This book brings SQL Server administration into the modern era with strong coverage of hybrid cloud environments, In-Memory OLTP, and installation on Server Core. This comprehensive guide to SQL Server Administration for today’s DBA helps you to administer the new and key areas of SQL Server, including Columnstore indexes and the In-Memory OLTP feature set introduced in 2014.

You will also be guided through the administration of traditional areas of SQL Server, including how to secure your instance, monitor and maintain your instance, and to use features such as AlwaysOn to make your instance highly available. Also covered is the use of SQL Server features to scale out read-only workloads. Pro SQL Server Administration is an all-new book taking up-to-date and modern approach that you'll want and need to further your career as a SQL Server database administrator.

In this book, you'll find:

  • • Extensive coverage of hybrid cloud environments involving Azure SQL Database
  • • Detailed discussions on all new, key features, including AlwaysOn and in-memory support
  • • Comprehensive coverage of key skills, such as monitoring, maintenance and indexing
  • Table of Contents

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