SQL Server 2016 High Availability Unleashed

587 Pages·2017·28.05 MB·English

by  Paul Bertucci

About This E-Book
EPUB is an open, industry-standard format for e-books. However, support for
EPUB and its many features varies across reading devices and applications. Use your
device or app settings to customize the presentation to your liking. Settings that you
can customize often include font, font size, single or double column, landscape or
portrait mode, and figures that you can click or tap to enlarge. For additional
information about the settings and features on your reading device or app, visit the
device manufacturer’s Web site.
Many titles include programming code or configuration examples. To optimize the
presentation of these elements, view the e-book in single-column, landscape mode
and adjust the font size to the smallest setting. In addition to presenting code and
configurations in the reflowable text format, we have included images of the code
that mimic the presentation found in the print book; therefore, where the reflowable
format may compromise the presentation of the code listing, you will see a “Click
here to view code image” link. Click the link to view the print-fidelity code image.
To return to the previous page viewed, click the Back button on your device or app.

SQL Server 2016 High Availability
UNLEASHED
Paul Bertucci
800 East 96th Street, Indianapolis, Indiana 46240 USA
SQL Server 2016 High Availability Unleashed
Copyright © 2018 by Pearson Education, Inc.
All rights reserved. Printed in the United States of America. This publication is
protected by copyright, and permission must be obtained from the publisher prior to
any prohibited reproduction, storage in a retrieval system, or transmission in any
form or by any means, electronic, mechanical, photocopying, recording, or likewise.
For information regarding permissions, request forms and the appropriate contacts
within the Pearson Education Global Rights & Permissions Department, please visit
www.pearsoned.com/permissions/.
ISBN-13: 978-0-672-33776-5
ISBN-10: 0-672-33776-2
Library of Congress Cataloging-in-Publication Data: 2017942001
Printed in the United States of America
1 18
Trademarks
All terms mentioned in this book that are known to be trademarks or service marks
have been appropriately capitalized. Sams Publishing cannot attest to the accuracy of
this information. Use of a term in this book should not be regarded as affecting the
validity of any trademark or service mark.
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as
possible, but no warranty or fitness is implied. The information provided is on an “as
is” basis. The authors and the publisher shall have neither liability nor responsibility
to any person or entity with respect to any loss or damages arising from the
information contained in this book.
Special Sales
For information about buying this title in bulk quantities, or for special sales
opportunities (which may include electronic versions; custom cover designs; and
content particular to your business, training goals, marketing focus, or branding
interests), please contact our corporate sales department at
[e-mail] or [phone].
For government sales inquiries, please contact [e-mail].
For questions about sales outside the U.S., please contact [e-mail].
Editor-in-chief
Greg Wiegand
Editor
Trina MacDonald
Managing Editor
Sandra Schroeder
Development Editor
Mark Renfrow
Project Editor
Lori Lyons
Production Manager
Dhayanidhi
Copy Editor
Catherine D. Wilson
Indexer
Lisa Stumpf
Proofreader
H. S. Rupa
Technical Editor
J. Boyd Nolan
Editorial Assistant
Courtney Martin
Cover Designer
Chuti Prasertsith
Compositor
codeMantra
Contents at a Glance
Introduction
Part I Understanding High Availability
1 Understanding High Availability
2 Microsoft High Availability Options
Part II Choosing the Right High Availability Approaches
3 Choosing High Availability
Part III Implementing High Availability
4 Failover Clustering
5 SQL Server Clustering
6 SQL Server AlwaysOn and Availability Groups
7 SQL Server Database Snapshots
8 SQL Server Data Replication
9 SQL Server Log Shipping
10 High Availability Options in the Cloud
11 High Availability and Big Data Options
12 Hardware and OS Options for High Availability
13 Disaster Recovery and Business Continuity
14 Bringing HA Together
15 Upgrading Your Current Deployment to HA
16 High Availability and Security
17 Future Direction of High Availability
Index
Table of Contents
Introduction
Part I Understanding High Availability
1 Understanding High Availability
Overview of High Availability
Calculating Availability
Availability Example: A 24×7×365 Application
The Availability Continuum
Availability Variables
General Design Approach for Achieving High Availability
Development Methodology with High Availability Built In
Assessing Existing Applications
What Is a Service Level Agreement?
High Availability Business Scenarios
An Application Service Provider
Worldwide Sales and Marketing—Brand Promotion
Investment Portfolio Management
Call-Before-You Dig Call Center
Microsoft Technologies That Yield High Availability
Summary
2 Microsoft High Availability Options
Getting Started with High Availability
Creating a Fault-Tolerant Disk: RAID and Mirroring
Increasing System Availability with RAID
Mitigating Risk by Spreading Out Server Instances
Microsoft Options for Building an HA Solution
Windows Server Failover Clustering (WSFC)
SQL Clustering
AlwaysOn Availability Groups
Data Replication
Log Shipping
Database Snapshots
Microsoft Azure Options and Azure SQL Databases
Application Clustering
Summary
Part II Choosing the Right High Availability Approaches
3 Choosing High Availability
A Four-Step Process for Moving Toward High Availability
Step 1: Launching a Phase 0 HA Assessment
Resources for a Phase 0 HA Assessment
The Phase 0 HA Assessment Tasks
Step 2: Gauging HA Primary Variables
Step 3: Determining the Optimal HA Solution
A Hybrid High Availability Selection Method
Step 4: Justifying the Cost of a Selected High Availability Solution
ROI Calculation 75
Adding HA Elements to Your Development Methodology
Summary
Part III Implementing High Availability
4 Failover Clustering
Variations of Failover Clustering
How Clustering Works
Understanding WSFC
Extending WSFC with NLB
How WSFC Sets the Stage for SQL Server Clustering and AlwaysOn
Installing Failover Clustering
A SQL Clustering Configuration
An AlwaysOn Availability Group Configuration
Configuring SQL Server Database Disks
Summary
5 SQL Server Clustering
Installing SQL Server Clustering Within WSFC
Potential Problems to Watch Out for with SQL Server Failover Clustering
Multisite SQL Server Failover Clustering
Scenario 1: Application Service Provider with SQL Server Clustering
Summary
6 SQL Server AlwaysOn and Availability Groups
AlwaysOn and Availability Groups Use Cases
Windows Server Failover Clustering
AlwaysOn Failover Clustering Instances
AlwaysOn and Availability Groups
Combining Failover with Scale-out Options
Building a Multinode AlwaysOn Configuration
Verifying SQL Server Instances
Setting Up Failover Clustering
Preparing the Database
Enabling AlwaysOn HA
Backing Up the Database
Creating the Availability Group
Selecting the Databases for the Availability Group
Identifying the Primary and Secondary Replicas
Synchronizing the Data
Setting Up the Listener
Connecting Using the Listener
Failing Over to a Secondary
Dashboard and Monitoring
Scenario 3: Investment Portfolio Management with AlwaysOn and Availability
Groups
Summary
7 SQL Server Database Snapshots