All You Need to Know About Microsoft SQL Server 2008 Failover Clustering Gopal Ashok

Program ManagerMicrosoft CorpSession Code: DAT 312

Session Agenda

Failover Clustering; Better together

Database Downtime

Unplanned Downtime

Failure Protection User Errors

Planned Downtime

Online Administration

Predictable Resourcing

Database Downtime Drivers and Clustering

SQL Server Failover Clusters• Instance-level HA• Automatic failure

detection• Automatic failover

SQL Server 2008 Failover Cluster• Rolling in-place upgrade with

minimal downtime• Rolling patch with minimal

downtime• Add node (at RTM+SP+CU

level, all at once), with no downtime

Windows Server 2008 HighlightsHCL is not needed anymore!!!!

More flexible support policy for cluster hardware

Any hardware configuration supported……Components should have Windows Server 2008 R2 logoThe configuration should pass cluster validation test

Configuration change on an existing cluster??Just re-run the cluster validation

Windows Server 2008 HighlightsExtremely Simplified Setup

Quorum ModelNode Majority: Each node that is available and in communication can vote

Node and Disk Majority: Each node plus a designated disk in the cluster storage (the “disk witness”) can vote, whenever they are available and in communication.

Node and File Share Majority: Each node plus a designated file share created by the administrator (the “file share witness”) can vote, whenever they are available and in communication.

No Majority: Disk Only: The cluster has quorum if one node is available and in communication with a specific disk in the cluster storage. Only the nodes that are also in communication with that disk can join the cluster.

Description of cluster

Quorum recommendation

Odd number of nodes Node Majority

Even number of nodes (but not a multi-site cluster)

Node and Disk Majority

Even number of nodes, multi-site cluster

Node and File Share Majority

Even number of nodes, no shared storage

Node and File Share Majority

SQL Server 2008 Failover ClusteringBetter together with Windows Server 2008 Clustering

Cluster Validation SupportIPv6,DHCP and ISCSI Support16 nodes on Enterprise EditionMulti-subnet not yet supported

Resilient SQL Server Failover Cluster Setup

Integrated OS cluster health checksIntegrated SQL Server ® setup health checksNo remote execution during setup

SQL Server ® 2008 on Windows Server® 2008 R2

SQL Server ® 2008 RTM cluster install does not work on Windows Server ® 2008 R2

Required: SQL Server ® 2008 CU2 and above using SQL Server ® 2008 patchable setup process

Recommended: SQL Server ® 2008 SP1 and above using slipstream process

Integrated InstallInstall local machine bits and create single-node SQL Server ® cluster in one integrated stepRun Add Node on each additional node

Advanced/Enterprise InstallPrepare – install local machine bits first on each nodeComplete – combine prepared nodes and create SQL Server

® cluster as final step

Failover Clustering Setup

IntegratedInstallNode1

Add NodeNode2

Add NodeNode3

Add NodeNode4

SQL Server®Failover Cluster

Windows Server® Failover Cluster

Integrated Install

Phase 1: Prepare Failover ClusterOn all the nodes that are going to be in the SQL Server ® failover cluster, run SQL Server ® setup and use “Prepare” option.These prepared instances will not be usable until the installation process is “completed” in Step 2.

Phase 2: Complete Failover ClusterOn one of the prepared nodes, run SQL Server ® setup and use “Complete” option.SQL Server ® setup will form the SQL Server ® failover cluster and join all the prepared nodes for the selected instance as a cluster.

Advanced failover cluster install

Phase 1: Prepare Failover Cluster• Prepare SQL Server ® failover cluster instance on

each node separately • Instances are not useable at the end

Phase 2: Complete Failover Cluster• Create a SQL Server ® Failover Cluster from

cluster-prepared SQL Server ® instance at once.• Instance becomes useable at the end

Notes:• Same InstanceID/Instance Name on all nodes• Can use third-party remote deployment solution

Create from multiple cluster-prepared SQL Server ® instances

Advanced\Enterprise Setup

Integrated Cluster Setupdemo

Reduce Planned DowntimeIntroducing:

Rolling Version Upgrade and Rolling Patch Updates

Install prerequisites on each passive node and

upgrade the shared SQL Server components

Restart passive nodes as prompted

Fail over SQL Server instance to prepared

node (has prerequisites and updated components)

Install prerequisites on remaining passive

node(s) and upgrade shared SQL Server

components

On each passive node, initiate engine upgrade

installation process

Upgrade manages failover of SQL Server instance to upgraded

node based on upgraded node count

Upgrade the remaining nodes

Rolling Upgrade

Rolling Failover Cluster Upgrade

PassiveActive

Windows Server ® 2003 R2 EE SP2, 64-Bit

SQL Server ® 2005 EE SP3, 64-Bit

Disk Array

Upgrade InterfaceShows the upgrade state of all nodes in the cluster and the current version on the node

Also indicates whether the database and replication scripts have been upgraded

Rolling Failover Cluster Upgrade

PassiveActive

Step #1:Install Prerequisites:1- Microsoft .Net Framework ® 3.5 SP12- Microsoft Windows Installer ® 4.53- On Microsoft Windows Server ® 2003,

QFE (KB937444)4- SQL Server ® 2008 Setup Support files

REBOOT…

Step #2:Install Prerequisites:1- Microsoft .Net Framework ® 3.5 SP12- Microsoft Windows Installer ® 4.53- On Microsoft Windows Server ® 2003,

QFE (KB937444)4- SQL Server ® 2008 Setup Support files

REBOOT …

SQL Server ® Instance Manual Failover

Best practice – Installation of prerequisites

Rolling Failover Cluster Upgrade

ActivePassive

Step #4:Upgrade to SQL Server ® 2008 on Active Node

Step #3:Upgrade to SQL Server ® 2008 on Passive Node

Step 5: SQL Server ® Instance Automatic Failover

No client connection for 1-2 minutes while db is being upgraded to SQL Server® 2008 on the left node

SQL Server ® 2008SQL Server® 2008

Active

Removed from Cluster Group Possible Owners

SQL Server ® Rolling Failover Cluster Upgrade

PassiveActive

Mirrored SQ

L

Step #1: Note: Mirror version >= Principal version

Upgrade to SQL Server ® 2008 on Mirrored Instance

Step#2: Manual Failover to the database mirroring partner for each database

Mirroring suspended

SQL Server® 2008

SQL Server® Cluster

Principal

SQLServer®

2008

Mirroring resumed

Step #3:Upgrade

Cluster to SQL Server® 2008

Step#4: Manual Failover to the database mirroring partner for each database

With database mirroring

Failover during upgrade

SQL Server® 2008

SQL Server® 2005SQL Server® 2008

Start Upgrade……

Controlling Failover during upgrade

Use command line for upgrade

/FAILOVERCLUSTERROLLOWNERSHIP parameter controls the behavior

0 = Don’t failover; Don’t add to possible owners1= Failover; Upgrade; Add to possible owners2 = Default; Setup controls failover

Rolling Patching

No built-in logic to do automatic failover during upgrade

Plan the upgrade to ensure maximum uptime and availability

Identify passive nodes to be

upgraded first

Remove passive nodes from

possible node owner list

Apply update on each passive node

Add passive nodes back to node

owner list

Fail over SQL Server instance to an updated node

Validate that SQL Server instance comes

online and check version of engine for

proper value

Remove remaining non-updated nodes from possible node

owner list

Apply update on remaining non-updated passive

nodes

Add updated nodes back to node

owner list and test failover

Rolling Patch Upgradedemo

Summary

Windows Server 2008 and SQL Server 2008 provides a seamless experience for clustering

Removes lot of the deployment and hurdles from Windows Server 2003 and SQL Server 2005

Provides a much improved setup experience, better manageability and troubleshooting infrastructure

question & answer

www.microsoft.com/teched

Sessions On-Demand & Community

http://microsoft.com/technet

Resources for IT Professionals

http://microsoft.com/msdn

Resources for Developers

www.microsoft.com/learning

Microsoft Certification & Training Resources

Resources

SQL Server ® 2008 Failover Clustering White Paper: http://sqlcat.com/whitepapers/archive/2009/07/08/sql-server-2008-failover-clustering.aspx

Recommended Books Online Doc Refresh #7 (May, 2009), or later: http://msdn.microsoft.com/en-us/library/ms130214.aspx

Failover Clusters - Getting Started: http://msdn.microsoft.com/en-us/library/ms189134.aspx

Rolling upgrade process and best practice: http://msdn.microsoft.com/en-us/library/ms191295.aspx

Maintaining a Failover Cluster: http://msdn.microsoft.com/en-us/library/ms178061.aspx

Setup command line usage: http://msdn.microsoft.com/en-us/library/ms144259.aspx

Configuration.ini file usage: http://msdn.microsoft.com/en-us/library/dd239405.aspx

Documents and Related Content

Related Content

Breakout Sessions (session codes and titles)

Interactive Theater Sessions (session codes and titles)

Hands-on Labs (session codes and titles)

Hands-on Labs (session codes and titles)

Track Resources

Resource 1

Resource 2

Resource 3

Resource 4

Complete an evaluation on CommNet and enter to win an Xbox 360 Elite!

Please join us for theCommunity Drinks this evening

In Halls 3 & 4from 18:15 – 19:30

© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,

IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.