SQL Server 2005 Always On Technologies: Zero to Cluster in 60 Minutes

1
SQL Server 2005 Always On Technologies Zero to
Cluster in 60 Minutes

• Shahar Bar
• Senior SQL consultant
• CEO

2
One slide for PR.

• Established at 2005 by Meir Dudai, shortly had
  become a leading SQL Consulting company.
• Focused on Microsoft SQL Server .
• Designing, implementing and maintaining some of
  the most complex and exiting projects on SQL
  Server platform, in Israel and around the glob
  (30TB Database)
• Continuously growing!
• Visit us at http//www.valinor.co.il

3
Agenda

• Brief architecture discussion on clustering
• Pre-clustering checklist
• Intensive demos on
• Creating a Failover Cluster in Windows 2003
• Clustering SQL Server 2005
• Administrating a cluster
• ClusPrep
• Tips

4
Shared Nothing Model
Clients connect to the path \\ClusterName\FileShar
e
Transparent to users what node owns the resource
Requires a shared disk. There can only be 1
handle to a disk at any given time
\\ClusterName
Node 2
Node 1
Only 1 node owns any resource at any given time
\FileShare
5
Cluster Failover
SQL fails overand is available to clients
Client PCs
Failure Occurs!
Node A
Node B
SQL
Heartbeat
Passive Node
SQL
Disk cabinet A
Disk cabinet B
SCSIReserveBroken
New Reservation Established
6
Failover Cluster Topology
Client PCs
Public Network
Private Network
Cluster Nodes
Redundant Fibre Channel Switched Fabric
Shared Disk Storage
7
The IsAlive and LooksAlive Process

• LooksAlive A lighter but more frequent check
• IsAlive A more thorough check
• Operating-system checks
• Checks the network
• Checks the disk reservations
• Checks that a number of services are running
• Checks that file shares can be accessed
• SQL Server checks
• LooksAlive check the availability of the SQL
  Server service
• IsAlive check runs SELECT _at__at_SERVERNAME query

8
The Failover Process

• Other node (based on configuration) attempts to
  make reservation of shared drive
• Drive and network resources start on other
  instance
• Failover to another node
• Failover clustering attempts restart on same node
  or fails over to another node (configurable by
  admin)
• SQL Server service starts
• Brings master online
• Database recovery proceeds
• End users and applications must reconnect

9
Pre-Installation Check

• Confirm hardware is in the Windows Server Catalog
• Get guarantees from vendors!
• Need 7 IP addresses
• 2 for public communication
• 2 for private communication
• 1 for each SQL Server instance virtual IP
• 1 for Cluster IP address (for administration)
• 1 for MSDTC
• Need 2 shared drives
• Quorum
• SQL Server data drive(s)
• MSDTC

10
Installation Order
11
Installation Process
12
Setting Up the Failover Cluster
13
Installation Process
14
Motivation for ClusPrep

• Configuration Issues
• Cabling mistakes
• SP and Hotfix binaries
• Driver mismatches
• Inconsistent Settings

• Complexity
• Best Practices
• Supportability Requirements
• Hardware Compatibility

If we can eliminate the configuration issues up
front, we can ensure a better cluster experience
(installation and operation)
15
ClusPrep

• Misconfigured systems cause most installation and
  operation problems
• ClusPrep is a free tool that is downloadable on
  Microsoft.com. Integrated in windows 2008.
• Runs a set of tests to ensure collection of
  servers can be clustered
• When executed on a configured cluster, it will do
  a software inventory, perform network testing,
  validate system configuration

16
Verification steps of ClusPrep

• Verify 2 NICs per server
• Each NIC has different IP address, and each is on
  a different subnet
• Each server can communicate with every other
• Verify shared disks accessible from all machines,
  visible only once, and uniquely identifiable
• Verify network and disk I/O latencies
• Verify bus reset or LUN reset
• Verify SCSI reserve/release, reservation
  breaking, reservation defense

17
ClusPrep
18
Installation Process
19

• Faster Failover through Fast Recovery
• Supports up to an 8-node Failover Cluster with
  Enterprise Edition
• Supports up to a 2-node Failover Cluster with
  Standard Edition
• Supports mounted volumes for better explicit disk
  usage helps in server consolidation
• Supports dynamic AWE for better memory
  utilization
• Setup enhancements
• All SQL Server data services participate
• Database Engine, SQL Server Agent, Full-Text
  Search
• Analysis Services Now has multiple instances

20

• Not only beneficial to Failover Clustering
• On every server startup, Restart Recovery runs to
  guarantee consistency
• Restart Recovery has two phases
• REDO rolls forward all operations from log
• UNDO rolls back any incomplete transactions
• In SQL Server 2005, users are allowed access
  after REDO

21
Security

• Must have access to cluster administrator account
  for install
• SQL Server service account for services must be a
  valid domain account
• SQL Server service account for services will be
  granted proper permissions on the nodes

22
Installing a SQL Server Instance
23
Installation Process
24
Managing the Cluster

• Use CluAdmin.exe tool
• Move drives to new groups if SQL has more than
  one drive
• Test failover
• Setup dependencies after installation

25
Administering the Cluster
26
While installation is still running
Some additional tips!
27
Buy the Right Hardware

• Ask your preferred vendor for help
• Get guarantees!
• Purchase support agreements that align with
  availability needs Purchase support agreements
  that align with availability needs
• Remember a PSS contract, too!
• Hardware that supports Unique IDs (New disk
  signature standard)
• Ensure all nodes are identical
• Same HBA model
• Disk drives revisions

28
Hardware Considerations

• Buy systems from the Windows Server Catalog
  Cluster Solution Hardware Compatibility List
  (HCL)
• http//www.microsoft.com/windows/catalog/server/de
  fault.aspx?xsltcategoryproductsubid22pgn8b712
  458-b91c-4a7d-8695-23e9cd3ae95b
• Entire systems, not individual components

29
Hardware Considerations

• Uninterruptible power supply (UPS)
• Generator
• Fault tolerant hardware components (dual power,
  fans) in each node
• Apply latest system BIOS, device firmware and
  drivers
• Check with hardware manufacturer first to ensure
  latest versions have been tested
• Use Storport mini-port

30
Operating System

• Operating configuration
• Windows Server 2003 Enterprise, Windows Server
  2003 Datacenter, or R2 of either
• Add Nodes to Domain as member servers
• DCs are not recommended (and in some cases, not
  supported) on clustered nodes
• Install Windows Support Tools
• Install Resource Kit Tools Several cluster
  tools included
• Use cluster aware Monitoring software like
  Microsoft Operations Manager 2005 (MOM 2005)

31
Operating System Configuration

• Service packs SP1 is a strong recommendation
• Disable unnecessary services
• Patches
• 895092 Recommended hotfixes for Windows Server
  2003-based server clustershttp//support.microsof
  t.com/default.aspx?scidkbEN-US895092
• Drivers
• Anti-virus support
• Exclude the Quorum
• Exclude SQL or Exchange Databases and data

32
Cluster Service Account

• Properties
• 6 rights see 269229 How to manually re-create the
  Cluster service account http//support.microsoft.c
  om/?id269229
• Restrict logon location in Active Directory to
  the cluster nodes
• Password settings per your security guidelines
• Local administrator on every node
• Group Policy Objects can adversely affect your
  cluster, put each node in a dedicated
  Organizational Unit
• Regular Domain User account
• Never apply Application permissions
• Windows 2003 domain recommended

33
Cluster Service

• Do not install applications into the default
  Cluster Group
• Do not delete or rename the default Cluster Group
  or remove any resources from that resource group
• Do not set the Cluster service account to be a
  member of the domain administrator group
• Turn off cluster event log replication if
  auditing is enabled and security logging is
  heavy, or if you do not want event log entries to
  be replicated (224969)
• DNS - required
• WINS not required, and should not to be used if
  following security best practices

34
Resources

• Using Virtual Server 2005 to Create a Two-Node
  Windows Server 2003 Clusterhttp//www.microsoft.c
  om/technet/prodtechnol/virtualserver/deploy/cvs200
  5.mspx
• Welcome to the Clustering Technologies Community
  http//www.microsoft.com/windowsserver2003/communi
  ty/centers/clustering/default.mspx
• Server Clusters Network Configuration Best
  Practices for Windows 2000 and Windows Server
  2003 http//www.microsoft.com/technet/prodtechnol/
  windowsserver2003/technologies/clustering/clstntbp
  .mspx
• Clustering newsgroup support msnews.microsoft.co
  m
• Microsoft.public.sqlserver.clustering
• Microsoft.public.windows.server.clustering

35
Resources

• Clustering Windows Server 2003 and SQL Server
  2000/2005 http//www.microsoft.com/technet/communi
  ty/events/windows2003srv/tnt1-150.mspx
• SQL Server 2005 Mission Critical High
  Availability http//www.microsoft.com/technet/pro
  dtechnol/sql/themes/high-availability.mspx
• Visit the SQL Server Web site www.microsoft.com/s
  ql

36
(No Transcript)