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) Category 1 nine 2 nines 3 nines 4 nines 5 nines % Uptime 98.000% 99.000% 99.900% 99.990% 99.999% Downtime (Min/mth) 876.00 438.00 43.80 4.38 0.44 Downtime (Hrs/mth) 14.600 7.300 0.730 0.073 0.007 Downtime (Min/Year) 10,512 5,256 526 53 5 Downtime (Hrs/Year) 175.20 87.60 8.76 0.88 0.09 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 SQL Server Replication • • Database Level 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 Hub Shared Disk Array Hub C,D E F G C,D SQL Server Active/Passive SQL Server Cluster Client PCs SQL Server Server A Virtual Server Server B Heartbeat Cluster management Hub Shared Disk Array Hub C,D E F G 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 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 New for SQL Server 2005 • Instant Standby • Conceptually a fault-tolerant server • Database Failover • Very Fast … less than three seconds • Zero data loss Database Mirroring • 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 Database Mirroring • Impact to transaction throughput • Zero to minimal, depending on environment / workload Database Mirroring How it works Application Witness Mirror Commit Mirror is always redoing – it remains current Principal 1 5 2 SQL Server SQL Server 2 Log >2 Data 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 cryan@sdg.com.au Andrew Gannon Business Development Manager agannon@sdg.com.au (03) 9427-1477