All You Need to Know About Microsoft SQL

All You Need to Know About Microsoft SQL Server 2008 Failover Clustering Gopal Ashok Program Manager Microsoft Corp Session Code: DAT 312

Session Agenda Failover Clustering; Better together

Database Downtime Drivers and Clustering Database Downtime Unplanned Downtime Failure Protection SQL Server Failover Clusters • Instance-level HA • Automatic failure detection • Automatic failover User Errors Planned Downtime Online Administration 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 Predictable Resourcing

Windows Server 2008 Highlights HCL is not needed anymore!!!! More flexible support policy for cluster hardware Any hardware configuration supported…… Components should have Windows Server 2008 R 2 logo The configuration should pass cluster validation test Configuration change on an existing cluster? ? Just re-run the cluster validation

Windows Server 2008 Highlights Extremely Simplified Setup

Quorum Model Node 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 Clustering Better together with Windows Server 2008 Clustering Cluster Validation Support IPv 6, DHCP and ISCSI Support 16 nodes on Enterprise Edition Multi-subnet not yet supported Resilient SQL Server Failover Cluster Setup Integrated OS cluster health checks Integrated SQL Server ® setup health checks No remote execution during setup

SQL Server ® 2008 on Windows Server® 2008 R 2 SQL Server ® 2008 RTM cluster install does not work on Windows Server ® 2008 R 2 Required: SQL Server ® 2008 CU 2 and above using SQL Server ® 2008 patchable setup process Recommended: SQL Server ® 2008 SP 1 and above using slipstream process

Failover Clustering Setup Integrated Install local machine bits and create single-node SQL Server ® cluster in one integrated step Run Add Node on each additional node Advanced/Enterprise Install Prepare – install local machine bits first on each node Complete – combine prepared nodes and create SQL Server ® cluster as final step

Integrated Install Windows Server® Failover Cluster SQL Server® Failover Cluster Integrated Install Node 1 Add Node 2 Add Node 3 Add Node 4

Advanced failover cluster install Phase 1: Prepare Failover Cluster On 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 Cluster On 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.

AdvancedEnterprise Setup Create from multiple cluster-prepared SQL Server ® instances Phase 1: Prepare Failover Cluster Server ® • Prepare SQL failover cluster instance on each node separately • Instances are not useable at the end Notes: • Same Instance. ID/Instance Name on all nodes • Can use third-party remote deployment solution 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

demo Integrated Cluster Setup

Reduce Planned Downtime Introducing: Rolling Version Upgrade and Rolling Patch Updates Rolling Upgrade 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 Upgrade the remaining nodes Upgrade manages failover of SQL Server instance to upgraded node based on upgraded node count On each passive node, initiate engine upgrade installation process

Rolling Failover Cluster Upgrade Active Ø Windows Server ® 2003 R 2 EE SP 2, 64 -Bit Ø SQL Server ® 2005 EE SP 3, 64 -Bit Passive Disk Array

Upgrade Interface Shows 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 Best practice – Installation of prerequisites Step #2: Install Prerequisites: 1 - Microsoft. Net Framework ® 3. 5 SP 1 2 - Microsoft Windows Installer ® 4. 5 3 - On Microsoft Windows Server ® 2003, QFE (KB 937444) 4 - SQL Server ® 2008 Setup Support files Step #1: Install Prerequisites: 1 - Microsoft. Net Framework ® 3. 5 SP 1 2 - Microsoft Windows Installer ® 4. 5 3 - On Microsoft Windows Server ® 2003, QFE (KB 937444) 4 - SQL Server ® 2008 Setup Support files REBOOT … REBOOT… Active SQL Server ® Instance Manual Failover Passive

Rolling Failover Cluster Upgrade Step #4: Upgrade to SQL Server ® 2008 on Active Node Step #3: Upgrade to SQL Server ® 2008 on Passive Node SQL Server® 2008 Removed from Cluster Group Possible Owners Passive Active No client connection for 1 -2 minutes while db is being upgraded to SQL Server® 2008 on the left node SQL Server ® 2008 Active Step 5: SQL Server ® Instance Automatic Failover

® SQL Server Rolling Failover Cluster Upgrade With database mirroring Step#2: Step#4: Manual. Failoverto tothe SQL Server® 2008 SQL Server® Cluster Step #3: Upgrade Cluster to SQL Server® 2008 Active Mirroring suspended resumed Passive Principal. SQL Mirrored SQL Server® 2008 databasemirroring partnerfor foreach database Step #1: Note: Mirror version >= Principal version Upgrade to SQL Server ® 2008 on Mirrored Instance

Failover during upgrade SQL Server® 2008 Start Upgrade…… SQL Server® 2005

Controlling Failover during upgrade Use command line for upgrade /FAILOVERCLUSTERROLLOWNERSHIP parameter controls the behavior 0 = Don’t failover; Don’t add to possible owners 1= Failover; Upgrade; Add to possible owners 2 = 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 Validate that SQL Server instance comes online and check version of engine for proper value Fail over SQL Server instance to an updated node Add passive nodes back to node owner list Remove remaining non-updated nodes from possible node owner list Apply update on remaining nonupdated passive nodes Add updated nodes back to node owner list and test failover

demo Rolling Patch Upgrade

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

Resources www. microsoft. com/teched www. microsoft. com/learning Sessions On-Demand & Community Microsoft Certification & Training Resources http: //microsoft. com/technet http: //microsoft. com/msdn Resources for IT Professionals Resources for Developers

Documents and Related Content SQL Server ® 2008 Failover Clustering White Paper: http: //sqlcat. com/whitepapers/archive/2009/07/08/sql-server-2008 -failoverclustering. aspx Recommended Books Online Doc Refresh #7 (May, 2009), or later: http: //msdn. microsoft. com/en-us/library/ms 130214. aspx Failover Clusters - Getting Started: http: //msdn. microsoft. com/enus/library/ms 189134. aspx Rolling upgrade process and best practice: http: //msdn. microsoft. com/en-us/library/ms 191295. aspx Maintaining a Failover Cluster: http: //msdn. microsoft. com/enus/library/ms 178061. aspx Setup command line usage: http: //msdn. microsoft. com/enus/library/ms 144259. aspx Configuration. ini file usage: http: //msdn. microsoft. com/enus/library/dd 239405. aspx

Related Content Breakout Sessions (session codes and titles) Interactive Theater Sessions (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 Comm. Net and enter to win an Xbox 360 Elite!

Please join us for the Community Drinks this evening In Halls 3 & 4 from 18: 15 – 19: 30

© 2009 Microsoft Corporation. All rights reserved. Microsoft, 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.