Pro SQL Server Always On Availability Groups

316 Pages·2016·23.51 MB·English

by  Uttam Parui

Pro SQL Server
Always On
Availability
Groups
Enterprise-level high-availability and
disaster recovery
—
Uttam Parui
Vivek Sanil
Pro SQL Server
Always On Availability
Groups

Uttam Parui
Vivek Sanil
Pro SQL Server Always On Availability Groups
Uttam Parui Vivek Sanil
Mooresville, North Carolina San Antonio, Texas
USA USA
ISBN-13 (pbk): 978-1-4842-2070-2 ISBN-13 (electronic): 978-1-4842-2071-9
DOI 10.1007/978-1-4842-2071-9
Library of Congress Control Number: 2016955944
Copyright © 2016 by Uttam Parui and Vivek Sanil
This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the
material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation,
broadcasting, reproduction on microfilms or in any other physical way, and transmission or information
storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now
known or hereafter developed.
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with
every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an
editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are
not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to
proprietary rights.
While the advice and information in this book are believed to be true and accurate at the date of publication,
neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or
omissions that may be made. The publisher makes no warranty, express or implied, with respect to the
material contained herein.
Managing Director: Welmoed Spahr
Lead Editor: Jonathan Gennick
Development Editor: Corbin Collins
Technical Reviewer: Rahul Deshmukh
Editorial Board: Steve Anglin, Pramila Balan, Laura Berendson, Aaron Black, Louise Corrigan,
Jonathan Gennick, Todd Green, Robert Hutchinson, Celestin Suresh John, Nikhil Karkal,
James Markham, Susan McDermott, Matthew Moodie, Natalie Pao, Gwenan Spearing
Coordinating Editor: Jill Balzano
Copy Editor: Karen Jameson
Compositor: SPi Global
Indexer: SPi Global
Artist: SPi Global
Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street,
6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail o rders-ny@springer-
sbm.com , or visit w ww.springer.com . Apress Media, LLC is a California LLC and the sole member (owner)
is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware
corporation.
For information on translations, please e-mail [email protected] , or visit w ww.apress.com .
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our Special
Bulk Sales–eBook Licensing web page at w ww.apress.com/bulk-sales .
Any source code or other supplementary materials referenced by the author in this text are available to
readers at w ww.apress.com . For detailed information about how to locate your book’s source code, go to
w ww.apress.com/source-code/ . Readers can also access source code at SpringerLink in the Supplementary
Material section for each chapter .
Printed on acid-free paper
Contents at a Glance
About the Author .....................................................................................................xv
About the Technical Reviewer ...............................................................................xvii
Acknowledgments ..................................................................................................xix
■ Part I: Getting Started .........................................................................1
■ Chapter 1: High Availability and Disaster Recovery Concepts ..............................3
■ Chapter 2: Introduction to Always On ....................................................................9
■ Part II: Planning Always On Availability Groups ...............................17
■ Chapter 3: Concepts and Common Topologies ....................................................19
■ Chapter 4: Data Synchronization Internals ..........................................................35
■ Chapter 5: Introduction to Windows Server Failover Clustering .........................45
■ Chapter 6: Prerequisites ......................................................................................53
■ Part III: Deploying Always On Availability Groups ............................59
■ Chapter 7: Create a Windows Server Failover Cluster .........................................61
■ Chapter 8: Create Availability Groups ..................................................................93
■ Chapter 9: Post-Installation Tasks .....................................................................125
■ Part IV: Active Secondary Replicas ................................................149
■ Chapter 10: Readable Secondary Replicas ........................................................151
■ Chapter 11: Database Maintenance Using Secondary Replicas ........................163
iii
■ CONTENTS AT A GLANCE
■ Part V: Managing Availability Groups .............................................177
■ Chapter 12: Common Management Tasks .........................................................179
■ Chapter 13: Upgrading and Migrating ...............................................................191
■ Chapter 14: Performing Database Maintenance Tasks ......................................205
■ Part VI: Monitoring and Troubleshooting Availability Groups .........209
■ Chapter 15: Monitoring Availability Groups ......................................................211
■ Chapter 16: Troubleshooting Availability Groups ..............................................249
■ Part VII: Availability Groups in Microsoft Azure .............................275
■ Chapter 17: Introduction to Microsoft Azure .....................................................277
■ Chapter 18: Availability Groups in Microsoft Azure...........................................283
Index .....................................................................................................................309
iv
Contents
About the Author .....................................................................................................xv
About the Technical Reviewer ...............................................................................xvii
Acknowledgments ..................................................................................................xix
■ Part I: Getting Started .........................................................................1
■ Chapter 1: High Availability and Disaster Recovery Concepts ..............................3
What Is High Availability? .................................................................................................3
Calculating Availability ............................................................................................................................4
Causes of Downtime ...............................................................................................................................5
What Is Disaster Recovery?..............................................................................................7
Recovery Objectives ...............................................................................................................................7
Planning for Disaster Recovery ..............................................................................................................8
Summary ..........................................................................................................................8
■ Chapter 2: Introduction to Always On ....................................................................9
Common Solutions before Always On ..............................................................................9
Failover Cluster Instances (FCI) ..............................................................................................................9
Database Mirroring ...............................................................................................................................10
Log Shipping .........................................................................................................................................11
Replication ............................................................................................................................................12
Limitations and Concerns .....................................................................................................................14
v
■ CONTENTS
What Is Always On? ........................................................................................................14
SQL Server 2014 ...................................................................................................................................15
SQL Server 2016 ...................................................................................................................................15
Summary ........................................................................................................................16
■ Part II: Planning Always On Availability Groups ...............................17
■ Chapter 3: Concepts and Common Topologies ....................................................19
Availability Group Concepts ............................................................................................19
Availability Group and Replicas ............................................................................................................20
Availability Group Listener ....................................................................................................................21
Availability Mode ..................................................................................................................................22
Failover and Failover Modes .................................................................................................................22
Common Topologies .......................................................................................................25
Stand-alone to Stand-alone ..................................................................................................................25
SQL Failover Clustered Instance (FCI) to Stand-alone and Vice Versa ..................................................27
SQL FCI to SQL FCI ................................................................................................................................28
Extend On-Premises Always On Availability Groups to Microsoft Azure ...............................................29
Always On Availability Groups in Azure Virtual Machines (VMs) ...........................................................30
Distributed Availability Groups ..............................................................................................................30
Summary ........................................................................................................................33
■ Chapter 4: Data Synchronization Internals ..........................................................35
Trade-offs and Opportunity Cost ....................................................................................35
Synchronous-Commit Mode ...........................................................................................35
Asynchronous-Commit Mode .........................................................................................37
Synchronization Behavior in Various Scenarios .............................................................39
Scenario 1: Secondary Replica Goes Offl ine .........................................................................................39
Scenario 2: Primary Replica Goes Offl ine .............................................................................................41
Summary ........................................................................................................................44
vi
■ CONTENTS
■ Chapter 5: Introduction to Windows Server Failover Clustering .........................45
Overview of a Failover Cluster .......................................................................................45
Cluster Node .........................................................................................................................................46
Networks ..............................................................................................................................................47
Cluster Resource ..................................................................................................................................47
Resource Dependency ..........................................................................................................................47
Role.......................................................................................................................................................47
Failover .................................................................................................................................................48
Failback ................................................................................................................................................48
Preferred Owner ...................................................................................................................................48
Possible Owner .....................................................................................................................................49
Heartbeat ..............................................................................................................................................49
Quorum .................................................................................................................................................50
Dynamic Quorum ..................................................................................................................................51
Dynamic Witness ..................................................................................................................................51
Benefi ts and Limitations of Failover Cluster...................................................................51
Summary ........................................................................................................................52
■ Chapter 6: Prerequisites ......................................................................................53
Windows Requirements .................................................................................................53
Recommendations .........................................................................................................55
SQL Server Instance Requirements ................................................................................55
Availability Database Requirements ...............................................................................56
Availability Group Interoperability ..................................................................................57
Summary ........................................................................................................................58
■ Part III: Deploying Always On Availability Groups ............................59
■ Chapter 7: Create a Windows Server Failover Cluster .........................................61
Create a Windows Server Failover Cluster Using Failover Cluster Manager ..................62
Step 1: Install Failover Clustering Feature ............................................................................................62
Step 2: Validate Failover Cluster ...........................................................................................................69
vii
■ CONTENTS
Step 3: Create Windows Server Failover Cluster ..................................................................................76
Step 4: Confi gure Cluster Quorum ........................................................................................................82
Create a Windows Server Failover Cluster Using PowerShell ........................................89
Step 1: Install Failover Clustering Feature Using PowerShell ...............................................................90
Step 2: Validate Failover Cluster Using PowerShell ..............................................................................90
Step 3: Create Windows Server Failover Cluster Using PowerShell .....................................................90
Step 4: Confi gure Cluster Quorum Using PowerShell ...........................................................................90
Workgroup and Multi-Domain Clusters ..........................................................................91
Step 1: Create a Local User Account on Each Cluster Node .................................................................91
Step 2: Ensure All Nodes Have Primary DNS Suffi x ..............................................................................92
Step 3: Create Workgroup or Multi-Domain Cluster ..............................................................................92
Step 4: Confi gure Quorum ....................................................................................................................92
Summary ........................................................................................................................92
■ Chapter 8: Create Availability Groups ..................................................................93
Step 1: Enable the Always On Availability Groups Feature .............................................93
Step 2: Invoke Availability Group Wizard ........................................................................97
Step 3: Select Availability Group Name ..........................................................................98
Step 4: Select Databases .............................................................................................100
Step 5: Specify Replicas ...............................................................................................102
Step 6: Confi gure Endpoint ...........................................................................................106
Step 7: Confi gure Backup Preferences .........................................................................106
Step 8: Confi gure Listener ............................................................................................108
Step 9: Select Initial Data Synchronization ..................................................................112
Step 10: Validate ...........................................................................................................114
Other Ways to Create an Availability Group ..................................................................117
Availability group dialog box ...............................................................................................................117
T-SQL ..................................................................................................................................................118
PowerShell..........................................................................................................................................124
Summary ......................................................................................................................124
viii
■ CONTENTS
■ Chapter 9: Post-Installation Tasks .....................................................................125
Reviewing the Availability Group ..................................................................................125
Using Object Explorer .........................................................................................................................125
Using Always On Availability Groups Dashboard.................................................................................131
Using Windows Server Failover Cluster Manager ...............................................................................140
Replicating Logins and Jobs ........................................................................................146
Replicating Logins ..............................................................................................................................146
Using Contained Database with Availability Groups ...........................................................................147
Replicating SQL Agent Jobs and SSIS Packages ................................................................................147
Summary ......................................................................................................................148
■ Part IV: Active Secondary Replicas ................................................149
■ Chapter 10: Readable Secondary Replicas ........................................................151
Offl oading Reporting Workload .....................................................................................151
Solutions Prior to SQL Server 2012 ....................................................................................................151
Offl oading Reporting Workload Using Always On Availability Groups Secondary Replicas ................152
How to Confi gure Readable Secondary ........................................................................153
Using SQL Server Management Studio ...............................................................................................153
Using Transact-SQL ............................................................................................................................154
Using PowerShell ................................................................................................................................155
Confi gure Client Connectivity .............................................................................................................155
Confi gure Read-Only Routing .............................................................................................................155
Confi gure Load Balancing Across Replicas ........................................................................................157
Considerations, Limitations, and Best Practices ..........................................................158
Impact of Read Workload ....................................................................................................................159
The Role of Row Versioning ................................................................................................................160
Query Performance .............................................................................................................................160
Data Latency .......................................................................................................................................160
Blocking on Existing Transactions ......................................................................................................161
No Support for Change Tracking and Change Data Capture ...............................................................161
Ghost Record Cleanup ........................................................................................................................161
ix