High Availability Solutions

High Availability Solutions

SQL Server 2012 Books Online

Summary: This book introduces two SQL Server high-availability solutions that improve

the availability of servers or databases: AlwaysOn Failover Cluster Instances and

AlwaysOn Availability Groups. A high-availability solution masks the effects of a

hardware or software failure and maintains the availability of applications so that the

perceived downtime for users is minimized.

Category: Reference

Applies to: SQL Server 2012

Source: SQL Server Books Online (link to source content)

E-book publication date: June 2012

Copyright ? 2012 by Microsoft Corporation

All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means

without the written permission of the publisher.

Microsoft and the trademarks listed at

are trademarks of the

Microsoft group of companies. All other marks are property of their respective owners.

The example companies, organizations, products, domain names, email addresses, logos, people, places, and events

depicted herein are fictitious. No association with any real company, organization, product, domain name, email address,

logo, person, place, or event is intended or should be inferred.

This book expresses the author¡¯s views and opinions. The information contained in this book is provided without any

express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or distributors will

be held liable for any damages caused or alleged to be caused either directly or indirectly by this book.

Contents

High Availability Solutions ........................................................................................................................................... 7

Windows Server Failover Clustering (WSFC) with SQL Server........................................................................ 8

WSFC Quorum Modes and Voting Configuration ....................................................................................... 15

View Cluster Quorum NodeWeight Settings ............................................................................................. 18

Configure Cluster Quorum NodeWeight Settings................................................................................... 20

WSFC Disaster Recovery through Forced Quorum ..................................................................................... 22

Force a WSFC Cluster to Start Without a Quorum .................................................................................. 26

SQL Server Multi-Subnet Clustering ................................................................................................................. 28

AlwaysOn Failover Cluster Instances .................................................................................................................... 32

Failover Policy for Failover Cluster Instances ................................................................................................. 36

Configure HealthCheckTimeout Property Settings ................................................................................. 41

Configure FailureConditionLevel Property Settings ................................................................................ 43

View and Read Failover Cluster Instance Diagnostics Log ................................................................... 45

Failover Cluster Instance Administration and Maintenance .................................................................... 48

Add Dependencies to a SQL Server Resource........................................................................................... 49

Recover from Failover Cluster Instance Failure ......................................................................................... 51

Change the IP Address of a Failover Cluster Instance ............................................................................ 52

AlwaysOn Availability Groups.................................................................................................................................. 53

Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups ................. 57

Failover Clustering and AlwaysOn Availability Groups .............................................................................. 77

Getting Started with AlwaysOn Availability Groups .................................................................................... 81

Overview of AlwaysOn Availability Groups .................................................................................................... 90

Availability Modes (AlwaysOn Availability Groups) ................................................................................. 97

Change the Availability Mode of an Availability Replica ................................................................. 102

Failover and Failover Modes (AlwaysOn Availability Groups) ........................................................... 105

Change the Failover Mode of an Availability Replica ....................................................................... 116

Flexible Failover Policy for Automatic Failover of an Availability Group ................................... 119

Configure the Flexible Failover Policy to Control Conditions for Automatic Failover

(AlwaysOn Availability Groups) ............................................................................................................. 123

Possible Failures During Sessions Between Availability Replicas ................................................. 128

Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)............... 131

Configure Backup on Availability Replicas ............................................................................................ 134

Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups) ................. 142

About Client Connection Access to Availability Replicas ................................................................ 149

Configure Read-Only Access on an Availability Replica .................................................................. 153

Availability Group Listeners, Client Connectivity, and Application Failover ................................. 158

Prerequisites, Restrictions, and Recommendations for AlwaysOn Client Connectivity ....... 166

Create or Configure an Availability Group Listener........................................................................... 172

Configure Read-Only Routing for an Availability Group ................................................................. 181

Overview of Transact-SQL Statements for AlwaysOn Availability Groups.................................... 187

Overview of PowerShell Cmdlets for AlwaysOn Availability Groups .............................................. 189

Configuration of a Server Instance for AlwaysOn Availability Groups ............................................... 194

Enable and Disable AlwaysOn Availability Groups ................................................................................ 196

Create a Database Mirroring Endpoint for AlwaysOn Availability Groups (SQL Server

PowerShell) ....................................................................................................................................................... 202

Troubleshoot AlwaysOn Availability Groups Configuration............................................................... 204

Creation and Configuration of Availability Groups ................................................................................... 207

Use the New Availability Group Wizard (SQL Server Management Studio) ................................ 209

Specify Availability Group Name Page (New Availability Group Wizard/Add Database

Wizard) ........................................................................................................................................................... 215

Select Databases Page (New Availability Group Wizard/Add Database Wizard)................... 215

Specify Replicas Page (New Availability Group Wizard/Add Replica Wizard)......................... 216

Select Initial Data Synchronization Page (AlwaysOn Availability Group Wizards) ................. 222

Validation Page (AlwaysOn Availability Group Wizards) ................................................................. 226

Summary Page (AlwaysOn Availability Group Wizards) .................................................................. 227

Progress Page (AlwaysOn Availability Group Wizards) .................................................................... 228

Results Page (AlwaysOn Availability Group Wizards) ....................................................................... 229

Use the New Availability Group Dialog Box (SQL Server Management Studio) ........................ 230

Create an Availability Group (Transact-SQL)............................................................................................ 234

Create an Availability Group (SQL Server PowerShell) ......................................................................... 248

Specify the Endpoint URL When Adding or Modifying an Availability Replica .......................... 254

Join a Secondary Replica to an Availability Group ................................................................................ 258

Start Data Movement on an AlwaysOn Secondary Database ........................................................... 261

Manually Prepare a Secondary Database for an Availability Group............................................ 262

Join a Secondary Database to an Availability Group ........................................................................ 269

Management of Logins and Jobs for the Databases of an Availability Group............................ 271

Troubleshoot AlwaysOn Availability Groups Configuration............................................................... 272

Administration of an Availability Group ........................................................................................................ 276

Perform a Planned Manual Failover of an Availability Group............................................................ 278

Perform a Forced Manual Failover of an Availability Group .............................................................. 280

Use the Fail Over Availability Group Wizard (SQL Server Management Studio)........................ 294

Add a Database to an Availability Group .................................................................................................. 300

Use the Add Database to Availability Group Wizard (SQL Server Management Studio).... 304

Suspend an Availability Database ................................................................................................................ 308

Resume an Availability Database ................................................................................................................. 312

Remove a Secondary Database from an Availability Group .............................................................. 314

Remove a Primary Database from an Availability Group .................................................................... 317

Add a Secondary Replica to an Availability Group ................................................................................ 319

Use the Add Replica to Availability Group Wizard (SQL Server Management Studio) ........ 322

Change the Session-Timeout Period for an Availability Replica ...................................................... 327

Remove a Secondary Replica from an Availability Group................................................................... 329

Remove an Availability Group Listener ...................................................................................................... 331

Remove an Availability Group ....................................................................................................................... 333

Troubleshoot a Failed Add-File Operation (AlwaysOn Availability Groups) ................................ 336

AlwaysOn Policies for Operational Issues with AlwaysOn Availability Groups ............................... 336

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download