Transcript SQL Server 2005

SQL Server
High Availability
Craig Ryan
National Manager Database Services
Agenda
• How much Availability do you need?
• SQL 2000 High Availability Options
• Log Shipping, Replication, Clustering
• Demo – SQL 2000 H.A Options
• New HA options in SQL 2005
• Database Mirroring
• Demo – SQL 2005 Database Mirroring
How Much Availability do you need?
Need to ask yourself:
• How long can we afford to be down?
• How much data can we afford to lose?
Availability = up/(up+down)
Uptime
Downtime
(Min/mth)
Downtime
(Hrs/mth)
1 nine
98.000%
876.00
14.600
10,512
175.20
2 nines
99.000%
438.00
7.300
5,256
87.60
3 nines
99.900%
43.80
0.730
526
8.76
4 nines
99.990%
4.38
0.073
53
0.88
5 nines
99.999%
0.44
0.007
5
0.09
Category
%
Downtime
(Min/Year)
Downtime
(Hrs/Year)
Barriers To Availability
Many barriers
Only some are addressable by DBMS technology
Be sure to consider people, planning, and procedures
•
•
•
•
•
•
Database Server Failure or Disaster
User or Application Error
Data Access Concurrency Limitations
Database Maintenance and Operations
Upgrades
Availability at Scale
Warm Standby Solutions
Replication and Log Shipping
•
Database Object Level
•
•
Database Level
•
•
SQL Server Replication
Log Shipping
Both provide multiple copies
and a MANUAL fail over
Log Shipping
• Minimal impact on the production
server
• No changes to the database are
required
• Transactional consistency
• Supports delayed load of
transaction logs
• Not all SQL Server objects are
automatically copied
• Users must exit for next log to be
applied
Log Shipping
Demo
SQL 2000 Log Shipping
Replication
• Failover possible; a custom
solution
• Not limited to entire database; Can
define subset of source database
or tables
• Copy of database is continuously
accessible for read activity
• Latency between source and copy
can be as low as seconds
• Significantly increases the ongoing Database management
• Database schema may need to be
changed
Replication
Demo
SQL 2000 Transactional Replication
Failover Cluster
Failover Clustering
Microsoft Cluster Services
•
•
Hot Standby – Automatic failover
Built on Microsoft Cluster Services (MSCS)
•
•
•
•
•
•
•
•
Multiple nodes provide availability, transparent to client
Automatic detection and failover
Requires certified hardware
Supports many scenarios: Active/Active, N+1, N+I
Instance Failover – entire instance works as a unit
Single copy of instance databases
Available since SQL Server 7.0
Standby is not available for reporting, queries, etc.
•
May support other instances
Active/Passive SQL Server Cluster
Client PCs
SQL Server
Server A
Virtual
Server
Server B
Heartbeat
Cluster management
Shared
Disk Array
Hub
C,D
E
F
Hub
G
SQL Server
C,D
Active/Passive SQL Server Cluster
Client PCs
SQL Server
Server A
Virtual
Server
Server B
Heartbeat
Cluster management
Shared
Disk Array
Hub
C,D
E
F
Hub
G
SQL Server
C,D
What Clustering doesn’t do:
• Clustering is not a mechanism to scale
• Doesn’t protect your server against site
outage
• Doesn’t protect your disk subsystem
• Doesn’t protect against database
corruption
• Doesn’t protect against logical corruption
• Doesn’t protect against user error
• Doesn’t protect application crash
• Clustering is not a method to load-balance
Still a single point of failure – The Database!
Demo
SQL 2000 Failover Clustering
Log Shipping vs Clustering vs SQL Replication
So what’s the best solution….
… it depends
… On your business requirements
You can combine the SQL H.A options.
E.g. A/P Cluster with Log shipping
SQL Server 2005
High Availability
Barriers To Availability
As addressed in SQL Server 2005
• Database Server Failure or Disaster
• Failover Clustering
• Database Mirroring
• Transparent Client Redirect
• User or Application Error
• Data Access Concurrency Limitations
• Database Maintenance and
Operations
• Availability at Scale
Failover Cluster
Failover Clustering
SQL Server 2005
Further refined in SQL Server 2005
• More nodes
• Match operating system limits
• Unattended setup
• Support for mounted volumes (Mount Points)
• All SQL Server services participate
• Database Engine, SQL Server Agent,
Analysis Services, Full-Text Search, etc.
Database Mirroring
Database Mirroring
New for SQL Server 2005
• Instant Standby
• Conceptually a fault-tolerant server
• Database Failover
• Very Fast … less than three seconds
• Zero data loss
• Automatic or manual failover
• Automatic re-sync after failover
• Automatic, transparent client redirect
Database Mirroring
• Hardware
• Works with standard computers,
storage, and networks
• No shared storage components,
virtually no distance limitations
• Impact to transaction throughput
• Zero to minimal, depending on
environment / workload
Database Mirroring
Database Mirroring
How it works
Application
Mirror is always
redoing – it
remains current
Witness
Commit
Principal
Mirror
1
5
2
SQL Server
2
Log
>2
Data
SQL Server
4
3
Log
>3
Data
Witness and Quorum
Witness
• Sole purpose of the Witness is
to provide automatic failover
• To survive the loss of one server you
must have at least three
• Prevents “split brain”
• Does a lost connection mean the partner is
down or is the network down?
• To become the Principal, a server must
talk to at least one other server
Witness
Witness
• Witness is an instance of
SQL Server 2005
• Single witness for multiple sessions
• Consumes very little resources
• Not a single point of failure
• Partners can form quorum on their own
Safety / Performance
• There is a trade-off between
performance and safety
• Database Mirroring has two safety
levels
• FULL – commit when logged on Mirror
• Allows automatic failover
• No data loss
• OFF – commit when logged on Principal
• System does its best to keep up
• Prevents failover; to make mirror available
• Must ‘force’ service
• Or terminate Database Mirroring session
Transparent Client Redirect
•
•
No changes to application code
Client automatically redirected if
session is dropped
• Client library is aware of Principal and
Mirror servers
• Upon initial connect to Principal, library
caches Mirror name
• When client attempts to reconnect
•
•
If Principal is available, connects
If not, client library automatically redirects
connection to Mirror
Database Mirroring Setup Steps
1.
2.
3.
4.
5.
Ensure SQL 2005 is installed on both the
principle & the mirror Server
Setup Security (Endpoints)
Prepare the mirror database
Setup & Start the database mirroring session
Optionally add a witness
Demo
SQL 2005 Database Mirroring
Database States for Database Mirroring
•
•
•
•
•
SYNCHRONIZING
SYNCHRONIZED
SUSPENDED
PENDING_FAILOVER
DISCONNECTED
Automatic Failover
• Automatic Failover requires the following
conditions:
• Database mirroring running in synchronous mode
• The database must be in a synchronised state
• A witness must exist
• During Failover the following actions occur:
• The witness & mirror server agree the primary is dead,
which puts the database into a suspend state
• If possible the database on the Primary server changes
to a disconnected state
• The mirror finishes rolling forward and records the LSN
• The mirror database comes online
• When the principle returns, it becomes the mirror
Failover Solutions At A Glance
• Clustering & Mirroring both provide:
•
•
•
Automatic detection and failover
Manual failover
Transparent client connect
• Failover Clustering
•
•
•
•
•
System scope
Certified hardware
Fast failover
No reporting on standby
Single copy of database
• Database Mirroring
•
•
•
•
•
Database scope
Standard servers
Fastest failover
Limited reporting on standby
Duplicate copy of database
Summary
•
•
•
•
How many 9’s do you NEED?
SQL 2000 potential downtime ~3 mins
SQL 2005 potential downtime ~3 secs
Remember to consider process too!
Feel free to contact us
Craig Ryan
National Manager Database Services
[email protected]
Andrew Gannon
Business Development Manager
[email protected]
(03) 9427-1477