SQL Server 2005 Always On Technologies: Zero to Cluster in 60 Minutes - PowerPoint PPT Presentation

Loading...

PPT – SQL Server 2005 Always On Technologies: Zero to Cluster in 60 Minutes PowerPoint presentation | free to view - id: 12d215-MTExO



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

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

Description:

Established at 2005 by Meir Dudai, shortly had become a leading SQL Consulting company. ... Designing, implementing and maintaining some of the most complex and ... – PowerPoint PPT presentation

Number of Views:51
Avg rating:3.0/5.0
Slides: 37
Provided by: downloadM
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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 over and is available to clients
Client PCs
Failure Occurs!
Node A
Node B
SQL
Heartbeat
Passive Node
SQL
Disk cabinet A
Disk cabinet B
SCSI Reserve Broken
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 clusters http//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 Cluster http//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)
About PowerShow.com