Transcript Automatic failover

Designing 24x7 SQL
Server Deployments
Vineet Gupta
Evangelist – Database and Integration
Microsoft Corporation
http://spaces.msn.com/members/vineetgupta/
Barriers To Availability
Database Server Failure or Disaster
User or Application Error
Data Access Concurrency Limitations
Database Maintenance and Operations
Upgrades
Availability at Scale
Tuning
Barriers To Availability
As addressed in SQL Server 2005
Database Server Failure or Disaster
Failover Clustering, Database Mirroring,
Log Shipping
User or Application Error
Data Access Concurrency Limitations
Database Maintenance and Operations
Upgrades
Availability at Scale
Tuning
Database Server Failure Mitigation
Attribute
Failure
detection
Automatic
failover
Perceived
downtime
Potential
data loss
Masking
of storage
failure
Special
hardware
Clustering
Log
shipping
Database
mirroring
Yes
No
No
Yes
No
Yes
N/A
3 seconds
Yes – latest
transaction log
Yes – in some
configurations
30 seconds +
recovery
Yes – one copy
of data
No – shared disk Yes
Yes
Certified storage
and servers
No
No
Distance
100 miles
Unlimited
Unlimited
Scope
System and user User
databases
databases
User databases
Cold Standby Solutions
Backup / Restore
Backup / Restore and
Detach / Copy / Attach
Both Provide
Manual detection and failover
Potential for some work loss
Whole-database scope
Standard servers
Limited reporting on standby
Duplicate copy of database
Client must know where to re-connect
Slowest failover – Most downtime
Backup / Restore
Smaller size – only used pages are copied
Log backups allow restore to point in time
Longer restore time
Detach / Copy / Attach
Copies entire files
No possibility of rolling forward subsequent logs
Detach / Copy / Attach
Warm Standby Solutions
Replication
Replication and Log Shipping
Both Provide Multiple copies and Manual failover
Replication – since SQL Server 6.0
Primarily used where availability is required in
conjunction with scale out of read activity
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
Log Shipping
Basic idea: Backup, Copy, Restore Log will always
be supported
But no more investment in the scripts
Database scope
Database accessible but read-only
Users must exit for next log to be applied
Data backups no longer block log backups
Log Shipping
Hot Standby Failover Solutions
Failover Clustering and Database Mirroring
Both Provide
Failover Cluster
Automatic detection
Automatic, fast failover
Manual failover
Transparent client redirect
Zero work loss
Database Mirroring
Failover Cluster
Failover Clustering
Hot Standby
* Inst1
Zero work loss, zero impact on
throughput
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
Failover Cluster
Failover Clustering
SQL Server 2005
More nodes
Match operating system limits
Two-node Failover Clustering is available
in SQL Server 2005 Standard Edition
Unattended setup
Support for mounted volumes (Mount Points)
Full support for Majority Node Set quorum
All SQL Server data services participate
Database Engine, SQL Server Agent,
Full-Text Search
Analysis Services – Now has multiple instances
Database Mirroring
Database Mirroring
New for SQL Server 2005
Hot Standby
Provides a fault-tolerant database
Building block for complex topologies
Database Failover
Very fast failover
Less than five seconds in most cases
Zero data loss
Automatic or manual failover
Automatic re-sync after failover
Automatic, transparent client redirect
Architectural Blueprint
Database Mirroring
Clients
Witness
Log
Records
Principal
Mirror
Witness
Witness and Quorum
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
The Witness does not direct the Mirror to
become Principal
It simply answers the question “Who do you
see?”
Witness
Witness
Witness is an instance of
SQL Server 2005
Perhaps even SQL Server Express
Can be witness for multiple sessions
Consumes very little resources
Not a single point of failure
Partners can form quorum on their own
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
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
Database Mirroring Modes
High-Availability Mode
Safety Full; Synchronous operation
Database is available whenever a quorum exists
Automatic failover
High-Protection Mode
Safety Full; Synchronous operation
No witness – quorum provided by partners
If Principal loses quorum, it stops servicing the database
Ensures high protection; database is never in ‘exposed’ state
Manual failover only; no automatic failover
A transition mode; should not be in this mode for long
High-Performance Mode
Safety Off; Asynchronous operation
Manual failover only
Supports only one form of role switching: forced service
(with possible data loss)
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
Application Impact
Configuration
All setup is within SQL Server; no OS-level setup
DDL using ALTER DATABASE or Management Studio
Application Server-side
Some monitoring of Database Mirroring session
Application Client-side
Transparent to client; reconnect on dropped
connection
May specify principal and mirror servers in
connection string
Database Mirroring
Database Mirroring
Advantages
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
Summary
SQL Server 2005 HA Technology
Database Server Failure
or Disaster
Failover Clustering
Database Mirroring
Peer-to-Peer Replication
User or Application Error
Log Shipping
Database Snapshot
Data Access
Concurrency Limitations
Snapshot Isolation
Online Index Operations
Replication
Upgrade
Software and Hardware
Database Maintenance
and Operations
Fast Recovery
Partial Availability
Online Restore
Media Reliability
Dedicated Administration
Connection
Dynamic Configuration
Availability at Scale
Data Partitioning
Replication
Tuning
Database Tuning Advisor
Questions?
Free Online Training
http://www.microsoft.com/technet/prodtechnol/sql/2005/learning/
default.mspx
2936: Installing and Securing Microsoft SQL Server 2005
2937: Administering and Monitoring Microsoft SQL Server 2005
2938: Data Availability Features in Microsoft SQL Server 2005
2939: Programming Microsoft SQL Server 2005
2940: Building Services and Notifications Using Microsoft SQL
Server 2005
2941: Creating the Data Access Tier Using Microsoft SQL Server
2005
2942: New Features of Microsoft SQL Server 2005 Analysis
Services
2943: Updating Your Data ETL Skills to Microsoft SQL Server 2005
Integration Services
2944: Updating Your Reporting Skills to Microsoft SQL Server 2005
Reporting Services
Worth $ 99 Each
Free till Nov 1, 2006
90 Day Subscription from time of activation!
More Info
SQL Server Community sites
http://www.microsoft.com/sql/community/default.mspx
List of newsgroups
http://www.microsoft.com/sql/community/newsgroups/default.mspx
Locate Local User Groups
http://www.microsoft.com/communities/usergroups/default.mspx
Attend a free chat or web cast
http://www.microsoft.com/communities/chats/default.mspx
http://www.microsoft.com/usa/webcasts/default.asp
Your Feedback
is Important!
Please Fill Out the
feedback form
© 2005 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.