NLB Cluster - DB포탈사이트 DBguide.net
Download
Report
Transcript NLB Cluster - DB포탈사이트 DBguide.net
SQL Server 2000
High Availability
김병기
Senior Technology Specialist
[email protected]
Microsoft Corporation
Agenda
What Is High Availability?
HA Technology
Designing a Solution for HA
High Availability
Is
design, people, process ,technology의 집합체
오해…..
technology solution이 아님
scalability 또는 manageability와 유사한 개념이
아님.
Breaking Down The Nines
Percentage
Downtime (per year)
100%
None
99.999%
< 5.26 minutes
99.99%
5.26 – 52 minutes
99.9 %
52 m – 8 h, 45 min
99 %
8 h, 45 m – 87 h, 36 m
90%
788 h, 24 m – 875 h, 54 m
In Summary
99999
Unmanaged
Hardware is well managed
• Can tolerate some hardware failures
Good management and planning
• Can tolerate most hardware failures
• Can tolerate normal ops tasks (e.g., software upgrades)
• Can tolerate some software failures
Operational, planning, design excellence
• Can handle most planned and
unplanned downtime
• Can tolerate some operations failures
Cost
The Cost Of Availability
99
99.1
99.2
99.3
99.4
99.5
Availability
99.6
99.7
99.8
99.9
100
Agenda
What Is High Availability?
HA Technology
Designing a Solution for HA
Two Levels …
SQL Technology
Failover Clustering
Log Shipping
Replication
Windows Technology
Windows Clustering
NLB/WLBS
How Failover Clustering
Works
Client PCs
Node A
Node B
SQL Server
Heartbeat
Shared Disk Array
SQL Server
Log Shipping
Transaction Logs
Primary
Secondary
Log Shipping Monitor
Log Shipping Uses
H.A 사용:
Facilitate 7.0 2000 upgrade
원격지 서버문제 해결 방법 중 failover
cluster외의 또 다른 해결 방법으로 사용가능
Performing maintenance on Primary
Check DB health
SQL HA Technology Comparison
Feature
Failover Clustering
Log Shipping
Transactional
Replication
Failure detection
Automatic
Not Automatic
Not Automatic
Automatic switch to
secondary
Yes
Manual
Manual
Protects against
failed server
process
Yes
Yes, but …
Yes, but …
Protects against fail
ed disk
No, Shared-disk clustering
Yes, but …
Yes, but …
Meta data support
All system and user schema and
data for all databases
Some system, all user
schema and data for
select databases
Some user
schema and data
Transactionally con
sistent
Yes
Yes
Yes
Transactionally curr
ent
Yes
No, since last transac
tion log backup
No, since last repli
cated transaction
SQL HA Technology Comparison
Feature
Failover Clustering
Log Shipping
Transactional
Replication
Performance
impact
None
Minimal (file copying
on primary)
Log reader contin
ually running
Time to switch
Seconds to minutes, depends on d
b recovery time
Seconds, more to rec
over more thoroughly
Seconds, more to
recover more thor
oughly
Locations
Close (unless using distance cluster
s on HCL)
Not location bound
Not location boun
d
Additional
complexity
Some
Some
More
Maximum number
of servers
4
32 with NLB, otherwis
e no limit
No limit
Standby available
for reporting, etc.
N/A – not a warm standby solution
Yes. Possible Readonly access when
logs are not being
loaded
Yes
Partitioning of
data to standby
No
No
Yes
Agenda
What Is High Availability?
HA Technology
Designing a Solution for HA
Picking The Right
Technology
모든 경우에 적용되는 완전한 guideline
은 있을 수 없다.
Make sure the technology is
supportable in your environment
Just because something is “cool” , it
may not be right
failover clustering이 대부분의 경우에 해당
하는 최상의 option이라 할지라도 ,
항상 올바른 선택은 아님.
How Should I Deploy
Replication?
Replication은 high availability solution의 부분으
로 이용될 수 있다.
예를 들어, catalog data를 정기적으로 복제해야 하는
web site의 경우
Update가 점증적으로 발생하는 DSS database의 경우
It works very well for read-only data
NLB Cluster
IIS Server
IIS Server
IIS Server
LAN(Ethernet/FDDI)
Load Balancing
& Replication
Public Network
NLB Cluster
Read-Only SQL
Server
Read/Write SQL
Server
Publisher/Distributor
Read-Only SQL
Server
Read only data
Windows NT® Load
Balancing Service
(WLBS) is used to
distribute the load
Data is replicated to
multiple servers
Using Replication…
“continuous” mode replication로 운영시
대기시간을 낮출 수 있음
주의사항
Watch for the exceptions
(i.e., 10,000,000 row deletes)
Only synchronizes what you request
추가의 변화 관리가 필요.
절대 backup의 대체 솔루션이 아님
How Should I Deploy
Log Shipping?
Planned failover scenario
Secondary or remote failover
logical failures + physical failures에 대한
보호책으로 활용 가능
저장 대상 log들에 대한 지연 setting가능
1 to 15 minutes is what we typically see
1 minute for availability
15 minutes or more for logical data
protection
NLB and Log Shipping
One way to handle switching is via
Windows NT® Load Balancing Service
(WLBS)
설정:
2개의 log shipping SQL Servere들간의 a
private network 필요
LMHosts 또는 WINS server를 통한 resolve
IP addresses 필요
정기적인 sync master and msdb db간의
sync필요
NLB And Log Shipping
Switch NLB when primary goes down
Clients don’t have to know that they’re
connecting to a new SQL Server
But the clients can’t depend on the server
name either
One solution: Use IIS server to point to
SQL Server
Problem: 중재안이 필요함
Better for planned failover
Consider using Data Dependent Routing
NLB, IIS And Log Shipping
Clients
IIS Server
VIP
DIP1
DIP1
NLB Cluster
DIP2
DIP2
Primary
SQL Server
Log Shipping
Secondary
SQL Server
Log Shipping With Snapshot
Hardware-assisted solution
Create database on standby with snapshot backup/restore
Set up log shipping
Conventional log backups are copied over network
Primary
Standby
Disaster
Recovery Site
Split
Remote Mirroring
Mirror
Deploying Failover Clustering
Secondary server에 대한 service level을 결정
Do you need to be at 100% capacity after a failover
Will help for software upgrades, hardware upgrades,
helps mask hardware faults…
This will drive the decisions surrounding Single
Instance or Multi Instance
N+1 clustering with Windows 2000 Data Center
Server (4 nodes)
A Failover Cluster
Public Network(s)
Node 2
Virtual SQL Server(s)
Private Network
Node 1
Cluster Deployment: Wins
Windows 2000 Data Center and SQL
Server 2000 can provide N+1 failover
Note that 4th node could also support
an active instance
Node1:
Accounting
Node2:
Research
Node3:
HR
Node4:
Spare
A “Distance” Cluster
Chicago
New York City
Node 3
Node 1
Node 4
Node 2
Primary Site
Disaster
Recovery
Site
Remote Mirroring
Log Shipping With Replication
Replication can fail over
Typically you’d protect the publisher
Failover
Primary
Distributor
Subscribers
Standby
Standby
Distributor
Subscribers
Primary
Log Shipping With Replication
Choices
Synchronous model
+
–
Fault tolerant
Slower change propagation to subscribers
Semi-synchronous model
+
–
Subscribers are updated as changes occur
Subscribers see duplicate changes in
some failure situations but replication
designed to handle this now
Combining Log Shipping
And Failover Clustering
Protection against both logical and physical
failures
Log Ship
standby server
for each Virtual
SQL Server
Secondary
standby, for
the truly
paranoid
Log Shipping Versus
Failover Clustering
Failover clustering a good enterprise solution
Clients are unaware of failover since the server
name/IP address doesn’t change
Single copy of data
Less synchronization issues
No data integrity concerns
Requires specific expensive hardware
Log Shipping Versus
Failover Clustering
Hot Standby has a separate copy of the data
Provides for logical data protection
Can be kept hours behind for more protection
Hot Standby need not be geographically close
Can have multiple standby servers with
log shipping
Clients may need to know name/location
of log shipping server
Unless you use NLB/WLBS
Unless you handle this through Data Dependent
Routing (DDR)
Which One To Use?
The most popular choice is the failover
cluster, with a log-shipped remote
standby server.
Log Shipping is also often used for
reasons other than availability
Replication can be used for availability of
read-only data
You can use all three together…
Summary
Microsoft SQL Server 2000 has several
solutions to improve availability
Failover clusters
Log shipping
Replication
High availability is based on process
Pay attention to best practices
Practice disaster recovery
Investigate Microsoft Operations Framework
Availability Resources
Best Practices in Change, Configuration and Problem
Management
http://www.microsoft.com/mof
High Availability Operations Guide
Monitoring Reliability and Availability of Windows NTbased Servers
http://www.microsoft.com/windows2000/library/operations/
cluster/monitorrel.asp
Failover Clustering And Load Balancing
http://www.microsoft.com/NTServer/nts/deployment/planguide
/HighAvail.asp
http://www.microsoft.com/windows2000/library/technologies/
cluster/default.asp
Network Load Balancing Technical Overview
http://www.microsoft.com/windows2000/library/howitworks/
cluster/nlb.asp
Availability Resources
Capacity Planning for High Availability
Best Practices for End-to-End High Availability
http://www.microsoft.com/technet/avail/bestprac/bestprac.htm
Planning, Deploying, and Managing Highly
Available Solutions
http://www.microsoft.com/ISN/whitepapers/capacity_planning_
for_ha_921.asp
http://www.microsoft.com/technet/avail/overview/default.htm
Monitoring Reliability and Availability of Windows 2000
Servers
http://www.microsoft.com/windows2000/library/opeartions/
cluster/monitorrel.asp
Availability Resources
Windows 2000 Reliability and
Availability Improvements
Cluster Strategy: High Availability and Scalability with
Industry-Standard Hardware
http://www.microsoft.com/ntserver/ntserverenterprise/exec/
prodstrat/cluster2.asp
Windows Clustering Technologies: Cluster
Service Architecture
http://www.microsoft.com/windows2000/guide/platform/
strategic/relavail.asp
http://www.microsoft.com/windows/server/technical/
management/ClusterArch.asp
Don’t forget Technet and MSDN…
http://www.microsoft.com/technet
http://msdn.microsoft.com