SQL Server 2016 High Availability Unleashed (includes Content Update Program)

SQL Server 2016 High Availability Unleashed (includes Content Update Program)

by Paul Bertucc, Raju Shreewastava
Released July 2017
Publisher(s): Sams
ISBN: 9780134467610

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

Book + Content Update Program

SQL Server 2016 High Availability Unleashed provides start-to-finish coverage of SQL Server’s powerful high availability (HA) solutions for your traditional on-premise databases, cloud-based databases (Azure or AWS), hybrid databases (on-premise coupled with the cloud), and your emerging Big Data solutions.

This complete guide introduces an easy-to-follow, formal HA methodology that has been refined over the past several years and helps you identity the right HA solution for your needs. There is also additional coverage of both disaster recovery and business continuity architectures and considerations. You are provided with step-by-step guides, examples, and sample code to help you set up, manage, and administer these highly available solutions. All examples are based on existing production deployments at major Fortune 500 companies around the globe.

This book is for all intermediate-to-advanced SQL Server and Big Data professionals, but is also organized so that the first few chapters are great foundation reading for CIOs, CTOs, and even some tech-savvy CFOs.

  • Learn a formal, high availability methodology for understanding and selecting the right HA solution for your needs

  • Deep dive into Microsoft Cluster Services

  • Use selective data replication topologies

  • Explore thorough details on AlwaysOn and availability groups

  • Learn about HA options with log shipping and database mirroring/ snapshots

  • Get details on Microsoft Azure for Big Data and Azure SQL

  • Explore business continuity and disaster recovery

  • Learn about on-premise, cloud, and hybrid deployments

  • Provide all types of database needs, including online transaction processing, data warehouse and business intelligence, and Big Data

  • Explore the future of HA and disaster recovery

In addition, this book is part of InformIT’s exciting Content Update Program, which provides content updates for major technology improvements! As significant updates are made to SQL Server, sections of this book will be updated or new sections will be added to match the updates to the technologies. As updates become available, they will be delivered to you via a free Web Edition of this book, which can be accessed with any Internet connection. To learn more, visit informit.com/cup.

How to access the Web Edition: Follow the instructions inside to learn how to register your book to access the FREE Web Edition.

*The companion material is not available with the online edition on O'Reilly Learning

Table of contents

  1. About This E-Book
  2. Title Page
  3. Copyright Page
  4. Contents at a Glance
  5. Table of Contents
  6. Introduction
  7. Part I Understanding High Availability
    1. 1 Understanding High Availability
      1. Overview of High Availability
      2. Calculating Availability
        1. Availability Example: A 24×7×365 Application
        2. The Availability Continuum
      3. Availability Variables
      4. General Design Approach for Achieving High Availability
      5. Development Methodology with High Availability Built In
        1. Assessing Existing Applications
        2. What Is a Service Level Agreement?
      6. High Availability Business Scenarios
        1. An Application Service Provider
        2. Worldwide Sales and Marketing—Brand Promotion
        3. Investment Portfolio Management
        4. Call-Before-You Dig Call Center
      7. Microsoft Technologies That Yield High Availability
      8. Summary
    2. 2 Microsoft High Availability Options
      1. Getting Started with High Availability
        1. Creating a Fault-Tolerant Disk: RAID and Mirroring
        2. Increasing System Availability with RAID
        3. Mitigating Risk by Spreading Out Server Instances
      2. Microsoft Options for Building an HA Solution
        1. Windows Server Failover Clustering (WSFC)
        2. SQL Clustering
        3. AlwaysOn Availability Groups
        4. Data Replication
        5. Log Shipping
        6. Database Snapshots
        7. Microsoft Azure Options and Azure SQL Databases
        8. Application Clustering
      3. Summary
  8. Part II Choosing the Right High Availability Approaches
    1. 3 Choosing High Availability
      1. A Four-Step Process for Moving Toward High Availability
      2. Step 1: Launching a Phase 0 HA Assessment
        1. Resources for a Phase 0 HA Assessment
        2. The Phase 0 HA Assessment Tasks
      3. Step 2: Gauging HA Primary Variables
      4. Step 3: Determining the Optimal HA Solution
        1. A Hybrid High Availability Selection Method
      5. Step 4: Justifying the Cost of a Selected High Availability Solution
        1. ROI Calculation 75
        2. Adding HA Elements to Your Development Methodology
      6. Summary
  9. Part III Implementing High Availability
    1. 4 Failover Clustering
      1. Variations of Failover Clustering
      2. How Clustering Works
        1. Understanding WSFC
        2. Extending WSFC with NLB
        3. How WSFC Sets the Stage for SQL Server Clustering and AlwaysOn
        4. Installing Failover Clustering
      3. A SQL Clustering Configuration
      4. An AlwaysOn Availability Group Configuration
      5. Configuring SQL Server Database Disks
      6. Summary
    2. 5 SQL Server Clustering
      1. Installing SQL Server Clustering Within WSFC
      2. Potential Problems to Watch Out for with SQL Server Failover Clustering
      3. Multisite SQL Server Failover Clustering
      4. Scenario 1: Application Service Provider with SQL Server Clustering
      5. Summary
    3. 6 SQL Server AlwaysOn and Availability Groups
      1. AlwaysOn and Availability Groups Use Cases
        1. Windows Server Failover Clustering
        2. AlwaysOn Failover Clustering Instances
        3. AlwaysOn and Availability Groups
        4. Combining Failover with Scale-out Options
      2. Building a Multinode AlwaysOn Configuration
        1. Verifying SQL Server Instances
        2. Setting Up Failover Clustering
        3. Preparing the Database
        4. Enabling AlwaysOn HA
        5. Backing Up the Database
        6. Creating the Availability Group
        7. Selecting the Databases for the Availability Group
        8. Identifying the Primary and Secondary Replicas
        9. Synchronizing the Data
        10. Setting Up the Listener
        11. Connecting Using the Listener
        12. Failing Over to a Secondary
      3. Dashboard and Monitoring
      4. Scenario 3: Investment Portfolio Management with AlwaysOn and Availability Groups
      5. Summary
    4. 7 SQL Server Database Snapshots
      1. What Are Database Snapshots?
      2. Copy-on-Write Technology
      3. When to Use Database Snapshots
        1. Reverting to a Snapshot for Recovery Purposes
        2. Safeguarding a Database Prior to Making Mass Changes
        3. Providing a Testing (or Quality Assurance) Starting Point (Baseline)
        4. Providing a Point-in-Time Reporting Database
        5. Providing a Highly Available and Offloaded Reporting Database from a Database Mirror
      4. Setup and Breakdown of a Database Snapshot
        1. Creating a Database Snapshot
        2. Breaking Down a Database Snapshot
      5. Reverting to a Database Snapshot for Recovery
        1. Reverting a Source Database from a Database Snapshot
        2. Using Database Snapshots with Testing and QA
        3. Security for Database Snapshots
        4. Snapshot Sparse File Size Management
        5. Number of Database Snapshots per Source Database
        6. Adding Database Mirroring for High Availability
      6. What Is Database Mirroring?
        1. When to Use Database Mirroring
        2. Roles of the Database Mirroring Configuration
        3. Playing Roles and Switching Roles
        4. Database Mirroring Operating Modes
      7. Setting Up and Configuring Database Mirroring
        1. Getting Ready to Mirror a Database
        2. Creating the Endpoints
        3. Granting Permissions
        4. Creating the Database on the Mirror Server
        5. Identifying the Other Endpoints for Database Mirroring 180
        6. Monitoring a Mirrored Database Environment
        7. Removing Mirroring
      8. Testing Failover from the Principal to the Mirror
        1. Client Setup and Configuration for Database Mirroring
      9. Setting Up DB Snapshots Against a Database Mirror
        1. Reciprocal Principal/Mirror Reporting Configuration
      10. Scenario 3: Investment Portfolio Management with DB Snapshots and DB Mirroring
      11. Summary
    5. 8 SQL Server Data Replication
      1. Data Replication for High Availability
        1. Snapshot Replication
        2. Transactional Replication
        3. Merge Replication
        4. What Is Data Replication?
      2. The Publisher, Distributor, and Subscriber Metaphor
        1. Publications and Articles
        2. Filtering Articles
      3. Replication Scenarios
        1. Central Publisher
        2. Central Publisher with a Remote Distributor
      4. Subscriptions
        1. Pull Subscriptions
        2. Push Subscriptions
      5. The Distribution Database
      6. Replication Agents
        1. The Snapshot Agent
        2. The Log Reader Agent
        3. The Distribution Agent
        4. The Miscellaneous Agents
      7. User Requirements Driving the Replication Design
      8. Setting Up Replication
        1. Enabling a Distributor
        2. Publishing
        3. Creating a Publication
        4. Creating a Subscription
      9. Switching Over to a Warm Standby (Subscriber)
        1. Scenarios That Dictate Switching to the Warm Standby
        2. Switching Over to a Warm Standby (the Subscriber)
        3. Turning the Subscriber into a Publisher (if Needed)
      10. Monitoring Replication
        1. SQL Statements
        2. SQL Server Management Studio
        3. The Windows Performance Monitor and Replication
        4. Backup and Recovery in a Replication Configuration
      11. Scenario 2: Worldwide Sales and Marketing with Data Replication
      12. Summary
    6. 9 SQL Server Log Shipping
      1. Poor Man’s High Availability
        1. Data Latency and Log Shipping
        2. Design and Administration Implications of Log Shipping
      2. Setting Up Log Shipping
        1. Before Creating Log Shipping
        2. Using the Database Log Shipping Task
        3. When the Source Server Fails
      3. Scenario 4: Call Before Digging with Log Shipping
      4. Summary
    7. 10 High Availability Options in the Cloud
      1. A High Availability Cloud Nightmare
      2. HA Hybrid Approaches to Leveraging the Cloud
        1. Extending Your Replication Topology to the Cloud
        2. Extending Log Shipping to the Cloud for Additional HA
        3. Creating a Stretch Database to the Cloud for Higher HA
        4. Using AlwaysOn and Availability Groups to the Cloud
        5. Using AlwaysOn and Availability Groups in the Cloud
        6. Using Azure SQL Database for HA in the Cloud
        7. Using Active Geo Replication
        8. HA When Using Azure Big Data Options in the Cloud
      3. Summary
    8. 11 High Availability and Big Data Options
      1. Big Data Options for Azure
        1. HDInsight
        2. Machine Learning Web Service
        3. Stream Analytics
        4. Cognitive Services
        5. Data Lake Analytics
        6. Data Lake Store
        7. Data Factory
        8. Power BI Embedded
        9. Microsoft Azure Data Lake Services
      2. HDInsight Features
        1. Using NoSQL Capabilities
        2. Real-Time Processing
        3. Spark for Interactive Analysis
        4. R for Predictive Analysis and Machine Learning
        5. Azure Data Lake Analytics
        6. Azure Data Lake Store
      3. High Availability of Azure Big Data
        1. Data Redundancy
        2. High Availability Services
      4. How to Create a Highly Available HDInsight Cluster
      5. Accessing Your Big Data
      6. The Seven-Step Big Data Journey from Inception to Enterprise Scale
      7. Other Things to Consider for Your Big Data Solution
      8. Azure Big Data Use Cases
        1. Use Case 1: Iterative Exploration
        2. Use Case 2: Data Warehouse on Demand
        3. Use Case 3: ETL Automation
        4. Use Case 4: BI Integration
        5. Use Case 5: Predictive Analysis
      9. Summary
    9. 12 Hardware and OS Options for High Availability
      1. Server HA Considerations
        1. Failover Clustering
        2. Networking Configuration
        3. Clustered Virtual Machine Replication
        4. Virtualization Wars
      2. Backup Considerations
        1. Integrated Hypervisor Replication
        2. VM Snapshots
        3. Disaster Recovery as a Service (DRaaS)
      3. Summary
    10. 13 Disaster Recovery and Business Continuity
      1. How to Approach Disaster Recovery
        1. Disaster Recovery Patterns
        2. Recovery Objectives
        3. A Data-centric Approach to Disaster Recovery
      2. Microsoft Options for Disaster Recovery
        1. Data Replication
        2. Log Shipping
        3. Database Mirroring and Snapshots
        4. Change Data Capture
        5. AlwaysOn and Availability Groups
        6. Azure and Active Geo Replication
      3. The Overall Disaster Recovery Process
        1. The Focus of Disaster Recovery
        2. Planning and Executing Disaster Recovery
      4. Have You Detached a Database Recently?
      5. Third-Party Disaster Recovery Alternatives
        1. Disaster Recovery as a Service (DRaaS)
      6. Summary
    11. 14 Bringing HA Together
      1. Foundation First
      2. Assembling Your HA Assessment Team
      3. Setting the HA Assessment Project Schedule/Timeline
      4. Doing a Phase 0 High Availability Assessment
        1. Step 1: Conducting the HA Assessment
        2. Step 2: Gauging HA Primary Variables
        3. High Availability Tasks Integrated into Your Development Life Cycle
      5. Selecting an HA Solution
      6. Determining Whether an HA Solution Is Cost-Effective
      7. Summary
    12. 15 Upgrading Your Current Deployment to HA
      1. Quantifying Your Current Deployment
        1. Scenario 1 Original Environment List
      2. Deciding What HA Solution You Will Upgrade To
        1. Scenario 1 Target HA Environment List
      3. Planning Your Upgrade
      4. Doing Your Upgrade
      5. Testing Your HA Configuration
      6. Monitoring Your HA Health
      7. Summary
    13. 16 High Availability and Security
      1. The Security Big Picture
        1. Using Object Permissions and Roles
        2. Object Protection Using Schema-Bound Views
      2. Ensuring Proper Security for HA Options
        1. SQL Clustering Security Considerations
        2. Log Shipping Security Considerations
        3. Data Replication Security Considerations
        4. Database Snapshots Security Considerations
        5. AlwaysOn Availability Group Security Considerations
      3. SQL Server Auditing
        1. General Thoughts on Database Backup/Restore, Isolating SQL Roles, and Disaster Recovery Security Considerations
      4. Summary
    14. 17 Future Direction of High Availability
      1. High Availability as a Service (HAaaS)
      2. 100% Virtualization of Your Platforms
      3. Being 100% in the Cloud
      4. Advanced Geo Replication
      5. Disaster Recovery as a Service?
      6. Summary
      7. Conclusion
  10. Index
  11. Code Snippets

Product information

  • Title: SQL Server 2016 High Availability Unleashed (includes Content Update Program)
  • Author(s): Paul Bertucc, Raju Shreewastava
  • Release date: July 2017
  • Publisher(s): Sams
  • ISBN: 9780134467610