Loading in 2 Seconds...
Loading in 2 Seconds...
SESSION CODE: DAT407. Windows Server 2008 R2 and Microsoft SQL Server 2008: Failover Clustering Implementations. Allan Hirt Clustering MVP Consultant and CEO Megahirtz , LLC. Agenda. Windows Server 2008 RTM/R2 Failover Clustering SQL Server 2008 RTM/R2 Failover Clustering Summary
SESSION CODE: DAT407 Windows Server 2008 R2 andMicrosoft SQL Server 2008:Failover Clustering Implementations Allan Hirt Clustering MVP Consultant and CEO Megahirtz, LLC
Agenda • Windows Server 2008 RTM/R2 Failover Clustering • SQL Server 2008 RTM/R2 Failover Clustering • Summary • Book Giveaway
Three Variations of Clustered SQL Server Installations • Traditional • Install instance(s) on top of a Windows failover cluster • Cluster Windows with Hyper-V and implement SQL Server using clustered VMs • Cluster VMs under a hypervisor as you would physical nodes (aka “guest clustering”)
Basics for SQL Server Failover Clustering • Core variant of Windows Server 2008 RTM or R2 not supported for SQL Server failover clustering implementations • Failover clustering is a Feature of Windows Server 2008 R2 – must enable • Can only implement a 32-bit SQL Server instance on a 32-bit OS • Windows Server 2008 RTM is your only option • SQL Server 2005 and 2008/2008 R2 supported in a side-by-side configuration • SQL Server 2000 not supported under any version of Windows Server 2008
Networking for Failover Clustering • Still need redundancy and separate paths • Teaming fully supported • SQL Server 2008 does not support the multiple subnet feature of Windows Server 2008 failover clustering • Must use traditional VLAN for a geographically dispersed cluster • Network prioritization possible with Windows Server 2008 R2 (good for things like Live Migration) • Minimum of two networks; may need more • Example 1: iSCSI requires its own dedicated network • Example 2: Live Migration (Public, Private, Live Migration traffic, CSV, etc.) see http://technet.microsoft.com/en-us/library/ff428137(WS.10).aspx
Security for Failover Clustering • Windows • Domain account needed for creation and administration • Requires Create Computer Objects right on Computers OU • If cannot give CCO, must create CNO and VCO manually • Domain account NOT used to run the cluster service; runs in a special context • SQL Server • Still requires service accounts • Use a Service SID during the installation process • Only need to be domain users NOT domain admins • Do not need to be added to the local Administrator group unless using something like xp_cmdshell (which is off by default)
Anti-Virus and SQL Server Failover Clustering • Not recommended if not needed • If you do put it on, exclude the following: • .mdf (data), .ldf (log), .ndf (additional data) • .bak (default backup extension), .trn (default t-log backup extension) • All directories with Analysis Services data, log, temporary files, backups • Entire quorum/witness disk • \MSDTC directory for MSDTC disks (if used) • \Cluster subdirectory under %windir% • SQL Server & anti-virus KB: http://support.microsoft.com/KB/309422 • Cluster & anti-virus KB: http://support.microsoft.com/kb/250355
Storage for Failover Clustering • Drive types supported: only SAS, iSCSI, Fibre; no old parallel SCSI • Storage must be SCSI-3 SPC-3 command compliant • Storage must support persistent reservations • SQL Server 2008 still requires at least one drive letter • Can use mount points for everything else if desired • Cluster Shared Volume (CSV) not supported for SQL Server use; VMs only • Can select multiple drives during SQL Server 2008 install • TIP: Rename drives to make them easier to discern
Renaming a Cluster Disk & Adding a Disk to a SQL Server Failover Clustering Instance DEMO
Cluster Validation • No more Windows Server Catalog/HCL reliance • Only “real” HW requirement: must be logoed for Windows Server 2008 R2 • Different types of tests run (storage, networking, etc.) • Report location: %windir%\Cluster\Reports • Pass, Warning, Fail • Warning not fatal, but must investigate • Fix any problems and re-run • TIP: Re-run failed tests first, then re-run full suite • Additional validation tests if run after configured in Windows Server 2008 R2 • SQL Server relies on a successful result • Prevent a false positive
MSDTC, SQL Server 2008/R2, and Windows Server 2008 R2 • OS supports multiple DTCs – no longer need to share one per Windows failover cluster • Installation options • Old way: DTC in its own group (with its own IP, disk) • One DTC per SQL Server instance • Old way + bind to instance • Put in same group with SQL Server • Do not cluster DTC at all; clustered SQL Server will “negotiate down” to use a local DTC if nothing clustered • Use MSDTC instance installed to the local group • Else • Use the mapped instance of MSDTC • Else • Use the cluster's default instance of MSDTC • Else • Use the local machine's instance of MSDTC
SQL Server Failover Clustering Installation Tips • Installation is a node-by-node, instance-by-instance affair • Two variations of a new install: • “Normal” • Cluster prep • Slipstream the install media • SQL Server 2008 + SQL Server 2008 SP1 for Windows Server 2008 R2 • SQL Server 2008 R2 + SQL Server 2008 R2 CU1 (if need later hotfixes in SQL Server 2008 SP1) • Instance ID • Watch nested dialogs
SQL 2K8 Ins Upgraded SQL Server 2008 Service Packs and Failover Clusters 4. Remove nodes not upgraded, upgrade, and add back in 1. Remove half (or more) of the nodes from the possible owners SQL 2K8 Ins Not Upgraded SQL 2K8 Ins Not Upgraded SQL 2K8 Ins Not Upgraded SQL 2K8 Ins Not Upgraded SQL 2K8 Ins Not Upgraded SQL 2K8 Ins Not Upgraded SQL 2K8 Ins Not Upgraded Failover Initiated 2. Upgrade the removed nodes 3. Add the nodes back and remove other nodes except the one running the instance Removed nodes cannot be failed over to until added back in
Removing Possible Owners for a SQL Server Instance Using PowerShell DEMO
Guest Clustering • Fully supported • Windows Server 2008 RTM/R2 only for guest OS • SQL Server 2005 or 2008/2008 R2 only • Recommendation: split VMs acting as nodes over different hypervisors • Otherwise you have a single point of failure • The problem: ensuring separate network paths and things like multi-path I/O • iSCSI for shared disks • Watch other hypervisors – vSphere 4.0 currently does not support iSCSI for clustered setups (p. 13 of Setup for Failover Clustering and Microsoft Cluster Service)
Summary • Validation is key to a successful SQL Server failover clustering implementation • PowerShell is a powerful alternative to a GUI-based administration scheme for failover clustering with Windows Server 2008 R2 • Installing, upgrading, and patching is very different with SQL Server 2008 (and SQL Server 2008 R2) failover clustering • The story for the Microsoft Distributed Transaction Coordinator is very different with Windows Server 2008/R2 • Virtualization may be an option for clustering in your environment • Live Migration is a good story if you are using Hyper-V
Related Sessions & Labs • Sessions • WSV313 – Failover Clustering Success • WSV314 – Failover Clustering Pro Troubleshooting with Windows Server 2008 R2 • WSV315 – Guest vs.Host Clustering: What, When, and Why • Labs • DAT02-HOL – Create a Windows Server 2008 R2 MSDTC Cluster • DAT09-HOL – Installing a Microsoft SQL Server 2008 + SP1 Clustered Instance • DAT12-HOL – Maintaining a Microsoft SQL Server 2008 Failover Cluster • DAT14-HOL – Rolling Upgrade to Microsoft SQL Server 2008 • WSV01-HOL – Failover Clustering in Windows Server 2008 R2
Failover Clustering Resources • Pro SQL Server 2008 Failover Clustering (Allan Hirt; Apress, 2009) • Blog at SQLHA.com (http://www.sqlha.com/blog) • SQL Server 2008 failover clustering whitepaper • Server Virtualization Validation Program • The Microsoft SQL Server support policy for Microsoft Clustering (KB327518) • Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment (KB956893)
Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. DAT Track Scratch 2 Win • Find the DAT Track Surface Table in the Yellow Section of the TLC • Try your luck to win a Zune HD • Simply scratch the game pieces on the DAT Track Surface Table and Match 3 Zune HDs to win
Required Slide Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn
Required Slide Complete an evaluation on CommNet and enter to win!
Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st http://northamerica.msteched.com/registration You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year
© 2010 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.