550 likes | 827 Views
SQL Server. High Availability. Amit Vaid. 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.
E N D
SQL Server High Availability • Amit Vaid
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)
Barriers To Availability • Database Server Failure or Disaster • User or Application Error • Data Access Concurrency Limitations • Database Maintenance and Operations • Upgrades • Availability at Scale Many barriersOnly some are addressable by DBMS technologyBe sure to consider people, planning, and procedures
Warm Standby SolutionsReplication and Log Shipping • Database Object Level • SQL Server Replication • Database Level • Log Shipping • Both provide multiple copies and a MANUAL fail over
Log Shipping 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
Demo SQL 2000 Log Shipping
Replication 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 on-going Database management • Database schema may need to be changed
Demo SQL 2000 Transactional Replication
Failover Clustering Microsoft Cluster Services Failover Cluster • 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 Virtual Server Server A Server B Heartbeat Cluster management Hub Shared Disk Array Hub E F G C,D C,D SQL Server
Active/Passive SQL Server Cluster Client PCs SQL Server Virtual Server Server A Server B Heartbeat Cluster management Hub Shared Disk Array Hub E F G C,D C,D SQL Server
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 AvailabilityAs 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 Clustering SQL Server 2005 Failover Cluster 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 MirroringNew for SQL Server 2005 Database Mirroring • 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 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 How it works >3 >2 3 1 5 2 2 4 Commit Application SQLServer SQL Server Mirror is always redoing – it remains current Witness Principal Mirror Log Data Log 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 • 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 • Database Mirroring • Database scope • Standard servers • Fastest failover • Limited reporting on standby • Duplicate copy of database • Failover Clustering • System scope • Certified hardware • Fast failover • No reporting on standby • Single 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 cryan@sdg.com.au Andrew Gannon Business Development Manager agannon@sdg.com.au (03) 9427-1477