1 / 45

Oracle DBAs Deploying Highly Available SQL Server Systems

Oracle DBAs Deploying Highly Available SQL Server Systems. Joe Yong Chief Architect Scalability Experts Inc. jyong@scalabilityexperts.com. About This Session. Goals Overview of SQL Server 2005 High Availability features Drilldown on HA implementation strategies Non-goals

shaman
Download Presentation

Oracle DBAs Deploying Highly Available SQL Server Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle DBAs Deploying Highly Available SQL Server Systems Joe Yong Chief Architect Scalability Experts Inc. jyong@scalabilityexperts.com

  2. About This Session • Goals • Overview of SQL Server 2005 High Availability features • Drilldown on HA implementation strategies • Non-goals • Deep dive into SQL Server • Chest thumping • Make you a HA expert • Pre-requisites • Experience as an Oracle DBA, Architect or Developer DBA • Basic experience in designing, deployment and managing database systems that require medium to high levels of availability

  3. Agenda • What is High Availability • SQL Server 2005 HA overview • Solutions to common scenarios • Case study • Summary

  4. What is High Availability • Uninterrupted usability • A running server is not necessarily available • Is a factor of technology, people and processes • Often measured as a percentage in “uptime” over 1 year • Eg. 99.999% uptime = 5.25 minutes downtime a year • Should includes both planned and unplanned downtime but many only measure unplanned • You may not own every part of the equation but if you have to specify your SLA • Example • Online ordering system requires orders to be confirmed in 30 seconds • Availability is impacted by application scalability, network and database • Don’t forget security impact on HA

  5. Agenda • What is High Availability • SQL Server 2005 HA technologies • Hot Standby • Failover Clustering • Database Mirroring • Warm Standby • Database Mirroring – High Protection / Performance mode • Replication • Log Shipping • Cold Standby • Backup/restore • Online operations • Solutions to common scenarios • Case study • Summary

  6. Failover Cluster * Inst1 Multiple Active Instances N+1: N Active, 1 Inactive Instances N+I: N Active, I Inactive Instances * Inst1 * Inst1 Inst3 * Inst2 * Inst2 * Microsoft Failover ClusteringOverview • Hot Standby – Automatic failover • Protects against local, limited disasters • Built on Microsoft Server Clusters (MSCS) • Multiple nodes provide availability, transparent to client • Supports 2, 4, or 8 nodes depending on OS edition • Automatic detection and failover • Requires cluster certified hardware; see Windows Catalog: Clustered • Supports many scenarios: Multiple Active Instances, N+1, N+I • Up to 25 SQL Server instances per cluster • NOT a load balancing solution

  7. Microsoft Failover ClusteringDetail

  8. Site 1 Site 2 Network N1 N2 N3 N4 Storage Controller S2 Storage Controller S1 Mirror D1 D2 Mirror D4 D3 Geographically Dispersed Clusters • Same functionality and behavior as “standard” failover cluster • Protects against local, total and extended disasters • Requires specially certified cluster hardware from qualified vendors • Requires guaranteed 500ms maximum round trip latency between nodes • SQL Server does not differentiate between standard and geo-cluster

  9. High Availability Toolbox

  10. Agenda • What is High Availability • SQL Server 2005 HA technologies • Hot Standby • Failover Clustering • Database Mirroring – High Availability mode • Warm Standby • Database Mirroring – High Protection / Performance mode • Replication • Log Shipping • Cold Standby • Backup/restore • Online operations • Solutions to common scenarios • Case study • Summary

  11. Database Mirroring Database MirroringOverview • 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 • Works with standard certified servers, storage and networks • No location limitations • No shared components; two separate copies of data

  12. 5 >3 >2 3 2 2 4 Commit 1 Application SQL Server SQL Server Database MirroringHow does it work? Mirror is always redoing – it remains current Witness(optional) Principal Mirror Log Data Log Data

  13. Database MirroringWitness • Only required for automatic failover; • Just another instance of SQL Server 2005 • Can serve multiple sessions • Prevents “split brain” scenario • If partners do not see each other, is it due to network failure or server failure? • To become Principal automatically, a server must talk to at least one other server • WitnessONLY answers the question “Who do you see?”, does not promote a server to be Principal

  14. Database MirroringHigh Availability mode • Safety Full; synchronous operation • Commit when logged on Mirror • Allows automatic failover • No data loss • Database available whenever quorum exists • Formed by any two servers from the three; Principal, Mirror, Witness • Witness is present – automatic Failover

  15. Database MirroringTransparent Client Redirect • SQLConnection object that targets a mirrored database • No application code change required • 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 • If Principal is down upon first connect attempt, connection fails • Workaround via explicit coding or NLB type solution • Supports .NET and SNAC providers

  16. High Availability Toolbox

  17. Agenda • What is High Availability • SQL Server 2005 HA technologies • Hot Standby • Failover Clustering • Database Mirroring – High Availability mode • Warm Standby • Database Mirroring – High Protection / Performance mode • Replication • Log Shipping • Cold Standby • Backup/restore • Online operations • Solutions to common scenarios • Case study • Summary

  18. Database MirroringHigh Protection mode • Safety Full; synchronous operation • Commit when logged on Mirror • No automatic failover; manual failover only • Database quorum formed by Principal and Mirror • If Principal loses quorum, it stops servicing the database • Ensures high protection; database is never in ‘exposed’ state • No Witness present – no automatic failover

  19. Database MirroringHigh Performance mode • Safety Off; asynchronous operation • Commit when logged on Principal • No automatic failover; manual failover only • Possible data loss • If Mirror becomes unavailable; Principal continues working • If Principal becomes unavailable; Mirror can assume workload • Manual failover to Mirror is required • No Witness present; no automatic failover

  20. High Availability Toolbox

  21. Agenda • What is High Availability • SQL Server 2005 HA technologies • Hot Standby • Failover Clustering • Database Mirroring – High Availability mode • Warm Standby • Database Mirroring – High Protection / Performance mode • Replication • Log Shipping • Cold Standby • Backup/restore • Online operations • Solutions to common scenarios • Case study • Summary

  22. Transactional Replication • Requires consideration at design time; cannot just “flip the switch” • High performance – latency measured in seconds • Some (minimal) load on the server • Can be implemented at database or table level • Failover possible; custom designed solution • Two types • Standard transactional replication • Easy to design, setup & manage • Subscriber (standby) can be used for reporting • Peer-to-peer transactional replication • Multi-master model; schema is identical on all sites • Supports distributed applications with data partitioning; enables load balancing • Does not handle conflicts; design to avoid/prevent conflicts

  23. Logreader Agent Logreader Agent Logreader Agent Dist DB Dist DB Dist DB Distribution Agent Distribution Agent Distribution Agent Peer-To-Peer Transactional ReplicationHow does it work? “West” “East” “South”

  24. High Availability Toolbox

  25. Agenda • What is High Availability • SQL Server 2005 HA technologies • Hot Standby • Failover Clustering • Database Mirroring – High Availability mode • Warm Standby • Database Mirroring – High Protection / Performance mode • Replication • Log Shipping • Cold Standby • Backup/restore • Online operations • Solutions to common scenarios • Case study • Summary

  26. Log Shipping • Backup transaction log, copy to secondary server, restore transaction log backup • Failover is manual • Meta data management may be necessary • Read operations on secondary is permitted • Users are disconnected when log restore occurs • Can maintain multiple secondary servers • Optional Monitor server • Records history and status of backup/restore jobs • May be setup to raise alerts when jobs fail

  27. High Availability Toolbox

  28. Agenda • What is High Availability • SQL Server 2005 HA technologies • Hot Standby • Failover Clustering • Database Mirroring – High Availability mode • Warm Standby • Database Mirroring – High Protection / Performance mode • Replication • Log Shipping • Cold Standby • Backup/restore • Online operations • Solutions to common scenarios • Case study • Summary

  29. Backup and Restore • Slowest recovery (but also simplest) • Recommended as secondary or tertiary protection layer • Manual failure detection and switchover • Data loss possible • Recommend maintaining active backups on disk; duplicate, archive and offsite backups on tape • Various levels • Database – full, differential, partial, differential partial, copy-only • File & filegroups – “full”, differential • Transaction log

  30. Backup and Restore • RESTORE VERIFY ONLY • Backup media mirroring • Backup and database page checksums • Fine grained online repair • Online restore • Piecemeal restore • Page-level restore • Database backup does not block Log backup • Backup/restore includes FullText data • Copy-only – via T-SQL only

  31. High Availability Toolbox

  32. Agenda • What is High Availability • SQL Server 2005 HA technologies • Hot Standby • Failover Clustering • Database Mirroring – High Availability mode • Warm Standby • Database Mirroring – High Protection / Performance mode • Replication • Log Shipping • Database Snapshot • Cold Standby • Backup/restore • Online operations • Solutions to common scenarios • Case study • Summary

  33. Online Operations • Backup/restore • Full online backup • Online piecemeal restore; undamaged data remains available • Indexing • Allows create, drop and alter while users continue to access data • LOB datatype indexes not supported for online • Memory allocations • CPU affinity settings • Database snapshots

  34. Database Snapshot • Not originally designed as a specific HA solution but works great in some situations • Turning a Database Mirroring mirror into a reporting server • Isolated historical data for report generation • Protection in case of administrative, developer or user error; classic “Oops!” scenario • Uses copy-on-write technique to reduce disk space consumption

  35. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Database SnapshotHow does it work? CREATE DATABASE mydbSnap AS SNAPSHOT OF mydb USE mydb UPDATE (pages 4, 9, 10) mydb – Database Page 1 2 3 4 4 5 6 7 8 9 10 11 12 13 14 15 16 9 10 mydbSnap – Read-Only Database Snapshot USE mydbSnap SELECT (pages 4, 6, 9, 10, 14)

  36. Agenda • What is High Availability • SQL Server 2005 HA technologies • Solutions to common scenarios • Case study • Summary

  37. Rolling UpgradesIn three steps • Perform upgrades on the mirror, secondary, or subscriber • Switch roles • Database Mirroring Failover to the mirror • Log Shipping • Replication Redirect clients to subscriber • Perform upgrades on the original database server • Optional: Switch roles again • Backup principal log with no-recovery • Recover secondary • Re-direct clients to secondary

  38. Site Disaster Protection • Example Scenarios • Earthquake, fire, or flood causes datacenter outage • Solutions • Database Mirroring to a secondary site • Optimized solution - Allows very fast failover times to the secondary site • Optionally add log shipping for additional site protection • Log Shipping to one or more secondary sites • Basic solution – requires additional effort for failover • Third-party geo-clustering solutions for data center storage level redundancy • Find SQL Server Always On reviewed solutions at the Microsoft Always On website: www.microsoft.com/SQL/AlwaysOn

  39. Database Mirroring ConfigurationIn three steps Step 1: Restore database copy to mirror site with no-recovery option Step 2: Configure communication endpoints Step 3: Set the data protection level and Start Mirroring

  40. Database Query Workload Scale Out With Redundancy • Scenario • Need for near real time reporting on a second server that can also be used for disaster recovery • Need for a tier of identical databases for scaling out application queries with ability to use any one of the database copies for disaster recovery • Solutions • Transactional Replication • Peer-to-Peer Replication

  41. Putting It All Together • Failover Clustering • Local server redundancy • Full server/instance protection • Database Mirroring • Primary disaster site for databases • Reporting with Snapshot • Log Shipping • Additional disaster sites for databases • Logical recovery (with delay) • Replication • Database reporting and read scale out with redundancy Database Scale Out For Queries Replication Hot Standby Database Mirroring Production Database Failover Clustering Log Shipping Warm Standby Log Shipping With Restore Delay Logical Recovery Standby

  42. Agenda • What is High Availability • SQL Server 2005 HA technologies • Solutions to common scenarios • Summary

  43. Summary • <<WiP>>

  44. Resources • www.microsoft.com/sql/ • msdn.microsoft.com/sqlserver/ • www.microsoft.com/technet/ • www.scalabilityexperts.com • www.sqldev.net • www.sqlservercentral.com/

  45. High Availability Toolbox

More Related