Transcript A Technical Overview of SQL 2005 High Availability Features

A Technical Overview of Microsoft® SQL Server™
2005 High Availability
Beta 2
Matthew Stephen IT Pro Evangelist (SQL Server)
http://blogs.technet.com/mat_stephen
What We Will Cover
Barriers to availability
Database server failure or disaster
User or application error
Data access concurrency limitations
Barriers to Availability
Overview
Business requirements
– Time and location
– Processes
– Expectations
Technology
– Hardware
– DBMS software
– Application software
Barriers to Availability
Primary Server or DBMS Barriers
Database failure or disaster
–Hardware failure
–Human-made disaster
–Natural disaster
Barriers to Availability
Primary Server or DBMS Barriers
User or application error
– Accidental data modifications
– Malicious data modifications
Data access concurrency limitations
– Multiple transactions accessing same data
– Persistent data structure changes
Database Server Failure or Disaster
Overview
Failover clustering
Database mirroring
Peer-to-peer replication
Comparison of other alternatives
– Standard replication topologies
– Log shipping
– Backup and restore
– Detach, copy, attach
Database Server Failure or Disaster
Failover Clustering
Provides a hot standby
Built on Microsoft Cluster Services (MSCS)
– Multiple nodes (Now 8)
– Automatic failover of instance (~20 seconds)
– Certified hardware required
Zero committed work loss
Single copy of instance databases
Standby not available for any other use
Supports more SQL services
MS-2087 Implementing Microsoft Windows 2000 Clustering
Database Server Failure or Disaster
Failover Clustering
Primary Network
Virtual Server
Clients
Clustered Servers
2nd Private ‘Heartbeat’ network
Shared Disk Array
Database Server Failure or Disaster
Database Mirroring
Provides a hot standby (instant)
Database failover
– Very fast automatic failover (less than 3 seconds)
– Transparent client redirect
Zero committed work loss
Some performance impact
Maximum one mirror for each database
Standby not directly available for any other use
MS-2733 Updating Your Database Administration Skills to
Microsoft SQL Server 2005
Database Server Failure or Disaster
Database Mirroring Hardware
No special hardware required
– Requires a second server
– Servers do not need duplicate hardware
Virtually no distance limitations
– No shared disk arrays
– Transaction latency requirements determine acceptable network
latency
MS-2733 Updating Your Database Administration Skills to
Microsoft SQL Server 2005
Database Server Failure or Disaster
Database Mirroring With Automatic Failover
Clients
Witness Server
Principal Server
Mirror Server
Separate Disk Arrays
Database Server Failure or Disaster
Database Mirroring With Automatic Failover
Database Server Failure or Disaster
Peer-to-Peer Replication
Provides a hot standby of publication(s)
Can replicate entire database
Improved performance
No distance limitations
Some committed data loss
No conflict detection
MS-2733 Updating Your Database Administration Skills to
Microsoft SQL Server 2005
Database Server Failure or Disaster
Fault Tolerance with Peer-to-Peer Replication
Database Server Failure or Disaster
Load Balancing with Peer-to-Peer Replication
Database Server Failure or Disaster
Peer-to-Peer Replication Topology
User or Application Error
Comparison of High Availability Options
Hot Standby
Feature
Warm Standby
Cold Standby
Database
Mirroring
Failover
Clustering
Peer-to-Peer
Transactional
Replication
Log
Shipping
Backup /
Restore
Detach /
Copy /
Attach
No data loss
option
No data loss
Some Data Loss
possible
Some data loss
possible
Some data loss
possible
Some data
loss
possible
Some data
loss likely
Yes
Yes
Optional
No
No
No
No
Yes, AutoRedirect
Yes, Reconnect
to same IP
Optional
No, NLB helps
No, NLB helps
No
No
< 3 Seconds
20 Sec + DB
Recovery
None
Seconds
Seconds + DB
Recovery
Detect,
Restore,
Manual
failover
Detect,
Attach,
Manual
failover
Continuously
accessible
Snapshot
No
Continuously
accessible
Continuously
accessible
Intermittently
accessible
No
No
Data Loss
Automatic
Failover
Transparent
to Client
Downtime
Standby Read
Access
User or Application Error
Comparison of High Availability Options
Hot Standby
Feature
Warm Standby
Cold Standby
Database
Mirroring
Failover
Clustering
Peer-to-Peer
Replication
Transactional
Replication
Log
Shipping
Backup/
Restore
Detach/
Copy/
Attach
Data
Granularity
Database
Only
All System and
User Databases
Table or View
Table or View
Database Only
Database
Only
Database
Only
Masks Disk
Failure
Yes
No, Shared Disk
Yes
Yes
Yes
Yes
Yes
No, Dup.
system
needed
Specialized
Hardware from
Cluster HCL
No, Dup. system
needed
No, Dup. system
needed
No, Dup.
system needed
No, Dup.
system
needed
No, Dup.
system
needed
Some
More
More
More
More
Some
Some
Special
Hardware
Needed
Complexity
User or Application Error
Overview
Database snapshots
Database snapshot scenarios
User or Application Error
Database Snapshots
Read-only
Static view of database
Transaction consistent at point of time
Does not store all data pages
Reads only original data pages
– Reads data from snapshot if data has changed
– Reads data from database if data has not changed
Increases disk I/O of source database
MS-2733 Updating Your Database Administration Skills to
Microsoft SQL Server 2005
User or Application Error
How Database Snapshots Work
User or Application Error
Database Snapshot Scenarios
Enabling mirroring database for reporting
Historical point-in-time reporting
Recovering from administrative error
Protecting against application or user error
Data Access Concurrency Limitations
Overview
Pessimistic concurrency controls
Optimistic concurrency controls
Snapshot isolation level
Read committed (Snapshot)
ONLINE index operations
Data Access Concurrency Limitations
Pessimistic Concurrency Controls
Default Setting and Behavior
Designed for Data Integrity
SELECT Operations Require Shared Resource Locks
Different Isolation Levels Control Behavior of Shared Locks
MS-2072 Administering a Microsoft SQL Server 2000 Database
Data Access Concurrency Limitations
Optimistic Concurrency Controls
Optional setting and behavior
Implemented using row versioning
Designed for data integrity but allows reads of committed data
SELECT operations do not require shared resource locks
Different isolation levels control behavior of committed reads
Data Access Concurrency Limitations
Row Versioning
Several Uses
– Triggers for Inserted and Deleted tables
– Multiple Active Result Sets (MARS) sessions
– ONLINE Index Operations
– Optimistic concurrency controls (row snapshots)
Versions built in tempdb for all modified rows
Versions deleted when no remaining transaction or operation
needs them
Data Access Concurrency Limitations
Snapshot Isolation Level
Reads committed data at time of transaction start
ALLOW _SNAPSHOT_ISOLATION database option
TRANSACTION ISOLATION LEVEL SNAPSHOT session
option
Data Access Concurrency Limitations
Read Committed (Snapshot)
Reads committed data at time of statement
READ_COMMITTED_SNAPSHOT database option
TRANSACTION ISOLATION LEVEL READ COMMITTED
session option
Data Access Concurrency Limitations
ONLINE Index Operations
Table accessible for reads and modifications during ONLINE
index operations
Existing non-clustered indexes available to optimize during
ONLINE clustered index operations
Non-clustered index is not available during its index operation
Data Access Concurrency Limitations
ONLINE Index Operation Commands
CREATE INDEX
ALTER INDEX
DROP INDEX
ALTER TABLE
– ADD or DROP of UNIQUE index
– ADD or DROP of PRIMARY KEY with CLUSTERED index option
Data Access Concurrency Limitations
ONLINE Clustered Index Creation Operation
Session Summary
Several new features available in SQL Server 2005 to
protect against server failure
Database snapshots can be used to protect against
application, user, or administrative error
Use the new optimistic concurrency controls and ONLINE
index operations to reduce database contention issues