Transcript SQL Server 2000 Failover Clustering

SQL Server 2000 Clustering
Jeff Dalton
Extreme Logic
January 8, 2003
Agenda






Why Cluster
Cluster Concepts
Windows 2000 Cluster Technology
SQL Server 2000 Cluster Technology
Failover Cluster in Action
Best Practices and Resources
Improve Operational Excellence




How much downtime can my company afford
without losing productivity, profits, sales,
etc.?
The goal of a highly available system is to
provide continuous use of critical data and
applications regardless of planned and
unplanned interruption
Infamous “Five 9s”
How available is available and how much will
it cost?


Knowing the business need will help determine
technology costs
The more you need, the more you will need to
spend
Failover Clusters; part of the
solution!
Clusters Defined


A cluster is a group of independent
computers (nodes) that work together to run
a common set of applications and provide
the image of a single system (virtual server)
to the client and application. The computers
are physically connected by cables and
programmatically connected by cluster
software
Two different cluster models in the industry:


Shared Nothing (the Microsoft Way)

Only one node can own and access a single
hardware resource at any time
Shared Device

Any node can access any hardware resource in
the cluster as the Distributed Lock Manager
(DLM) arbitrates access
Windows 2000 Clustering
Technologies

Server Cluster




Intended primarily as a high availability
technology to provide failover support for
applications such as databases (SQL Server 2000),
messaging systems and file/print services
2-node FAILOVER CLUSTERS in Advanced Server
and 4-node in Datacenter Server
Primary focus of our talk !
Network Load Balancing (NLB) Cluster


Load balances incoming IP traffic across clusters
of up to 32 nodes
Enhances both availability and scalability of
Internet server-based programs such as Web
Servers
Benefits of Windows 2000
Clustering

High Availability (think Server Cluster)




Scalability (think NLB Cluster)



Designed to avoid Single Point of Failure (SPoF)
Ability of an application to continue operation with
loss of single component
Ownership of resources automatically transferred
to surviving server; users experience only a
momentary pause in service
Ability of an application to grow in size as user
demand increases by adding additional processors
and/or nodes
Scale Up and Scale Out
Manageability



Cluster Administrator
Single Point of Control (can be remote)
Cluster appears as a single-system image
Server Cluster
Logical View
Client PCs
Virtual Server
Server B
Server A
Heartbeat
Common Disk Array
Network Load Balancing Cluster
Logical View



No single point of
failure
No performance
bottleneck
No additional
hardware needed
NLB Host
NLB Host

Internet/
intranet

Grow incrementally as
demand increases
Up to 32 nodes in a
cluster
NLB Virtual
IP Address
NLB Host
NLB Host
NLB Host
Server Cluster Terminology

Node



Heartbeat


At least one public network so that outside world can
connect to the cluster
Resource


Private Network between nodes in the cluster to check
if node is alive
External Networking


Individual computers that comprise a cluster
2 in Windows AS, 4 in Windows DCS
Managed Hardware or Software components (Disks, IP
addresses, applications, databases)
Resource Group

Resource Group is the collection of logically related
cluster resources that are treated as a unit during node
operations
Server Cluster Terminology 2

Common “Shared Nothing” Disk Array



Virtual Server (Key Concept!!!!)


The outside world sees the Virtual Server (server name
and IP address) as a single image system of the
multiple nodes
Quorum Resource/Drive


Collection of physical disks (SCSI RAID or Fibre
Channel) that will be accessed by the cluster
Only one node can own a given resource at any given
time
Special and Required Common Resource - physical disk
in the common cluster disk array that plays a critical
role in cluster operations (form/join)
Failover/Failback

Act of moving from failed node(s) to surviving node(s)
and back again (if needed)
SQLServer 2000
Failover Clustering


Feature of SQL Server 2000 Enterprise
Edition
Automatic Failover and “Quick Restart”




Requires specialized hardware solutions
Clients will be disconnected on failure but
can reconnect relatively quickly


Not a scalability solution
Doesn’t obviate need for Database
Disaster/Backup/Recovery
Still connect to the same server/IP Address
Server/transaction recovery is automatic


Because the data and transaction log files are
failed over
Recovery from a failure looks like a normal server
startup to the failed over server
Other SQLServer 2000 HA Options

Log Shipping




New feature of SQL Server 2000 Enterprise Edition
Concept has been in use for a long time

Transaction logs from a primary database and
apply them to a secondary
Great primary or secondary method even if you
can’t afford failover clustering
Replication





Not the traditional method of HA – technology has
been around for a long time
Sometimes better than log shipping for
transactional consistency
Easy to replicate read-only data
Possibly more complex, additional resources
Uses – reporting, read only, possibly updates;
partition data
Failover Configurations

Active-Passive


Active-Active




SQL Server on “primary” instance running,
secondary instance is idle until a failover
SQL Server is running on both servers in the cluster
simultaneously with different databases
But they are independent of each other (no load
balancing or data sharing)
Active/Active/Active/Active on 4-node !
NOTES


In SQL 2000, these terms are “deprecated”, still
popular though…
In SQL 2000, we use the term “SQL Virtual Server
Instances”
Failover Cluster in Action
Server A
Server B
SQL Server
Heartbeat
Disk cabinet A
Cluster management
Disk cabinet B
SQL Server
Bang !
Server A
Server B
SQL Server
Heartbeat
Disk cabinet A
Cluster management
Disk cabinet B
SQL Server
All’s Clear !
Server A
Server B
?
Heartbeat
Disk cabinet A
Cluster management
Disk cabinet B
SQL Server
SQL Server
Failback (if needed)
Server A
Server B
SQL Server
Heartbeat
Disk cabinet A
Cluster management
Disk cabinet B
SQL Server
Rolling Upgrades
1
Resource1
Resource2
Rolling Upgrades
1
2
Resource1
Resource1
Resource2
Resource2
Upgrade
Rolling Upgrades
1
2
Resource1
Resource2
Resource2
3
Upgrade
Resource1
Resource1
Resource2
Upgrade
Rolling Upgrades
1
2
Resource1
Resource1
Resource2
Upgrade
Resource2
3
Upgrade
Resource1
Resource2
4
Resource1
Resource2
Best Practices and Resources
Implementation Tips






Plan, plan, plan!
Plan shared disk space
Don’t skimp on hardware
Test, test, test!
Cluster failover
Hardware failures




Network cards and cables
Shared disk arrays and cables
Entire node shutdowns
SQL Server and application failures
Best Practices and Resources

Be prepared to READ a lot





SQL2K BOL

Great place to start for common questions,
installation order, deployment considerations,
supported configurations, etc.
SQL2K Resource Kit

Must Read Part 4 Chapters 12-16
Presentation Resources Page
Microsoft supports only validated cluster
configurations (Hardware Compatibility List)
Special Considerations





Ensure that each server is a member of the same
domain
Only one MSDTC per Cluster
SQL 2000 Analysis service (OLAP)
SQL Mail (MAPI is not cluster aware)
Nothing should go on the Quorum Drive
More Resources





Web Resources
Windows 2000 Clustering Technologies Home Page (Technical and Introductory
Overviews)
http://www.microsoft.com/windows2000/technologies/clustering/default.asp
Step-by-Step Guide to Installing Cluster Service
http://www.microsoft.com/windows2000/techinfo/planning/server/clustersteps.asp
Building a Highly Available Database Cluster
http://msdn.microsoft.com/library/techart/d5clustering.htm
Best Practices for End-to-End High Availability
http://www.microsoft.com/technet/avail/bestprac/bestprac.htm









Microsoft Support Articles
Q243218, INF: Installation Order for SQL Server 2000 Enterprise Edition on Microsoft
Cluster Server
http://support.microsoft.com/support/kb/articles/Q243/2/18.ASP
Q260758, INF: Frequently Asked Questions - SQL Server 2000 - Failover Clustering
http://support.microsoft.com/support/kb/articles/q260/7/58.asp
Q274446, INF: Upgrade to a SQL Server 2000 Failover Solution Recommended for All nonSQL Server 2000 Virtual Servers
http://support.microsoft.com/support/kb/articles/Q274/4/46.ASP
Microsoft Newsgroups
microsoft.public.sqlserver.clustering
Other Resources
Microsoft Support Webcasts
Windows Clustering: An Overview of Microsoft Clustering Technologies
http://support.microsoft.com/servicedesks/Webcasts/WC012100/WCBLURB012100.ASP
Introduction to Microsoft SQL Server 2000 Clustering
http://support.microsoft.com/servicedesks/webcasts/wc051001/wcblurb051001.asp