High Availability options Explored with SQL Server

1. High Availability options Explored with SQL Server BalmukundLakhani Technical Lead – SQL Support | Microsoft GTSC http://blogs.msdn.com/blakhani | blakhani@microsoft.com Team Blog – http://blogs.msdn.com/SQLServerFAQ

2. About me… • Working with SQL Technology since 2001 (almost 10 years) • Currently working as Technical Lead with Microsoft SQL Support team. • Premier Field Engineer @ Microsoft • Developer @ Ramco Systems • DBA @ Ramco Systems • ERP Onsite Support @ Ramco Systems

3. Agenda • Why High Availability? • Backup/restore related technologies. • Database Snapshots • Log-shipping • Database Mirroring • Failover Clustering • Replication • SQL Server “Denali” High Availability

4. Business Needs • RTO (Recovery Time Objective) • The duration of acceptable application downtime, whether from an unplanned outage or from scheduled maintenance/upgrades • RPO (Recovery Point Objective) • The ability to accept potential data loss from an outage

5. Causes of Downtime and Data Loss • Planned Downtime* • Performing database maintenance • Performing batch operations • Performing an upgrade. • Unplanned Downtime • Data center failure • Server failure • I/O subsystem failure • Human error

6. What do we need? • Minimize or avoid service downtime • Whether planned or unplanned • When components fail,service interruption is brief or non-existent • Automatic failover • Eliminate single points of failure (as affordable) • Redundant components • Fault-tolerant servers

7. Single-Instance Technologies

8. Backup, Restore, and Related Technologies • Partial Database Availability and Online Piecemeal Restore • Instant File Initialization • Mirrored Backups • Backup Checksums • Database Snapshots • Backup Compression

9. Minimizing downtime Backup Compression 4.01GB 295.8s 219s 34.2 MB 126.7s 126s

10. Database Snapshots

11. What Are Database Snapshots? • Read-only, consistent view of a database • Specified point-in-time • Modifying data • Copy-on-write of affected pages • Reading data • Accesses snapshot if data has changed • Redirected to original database otherwise Page Page 12:00 Snapshot

12. Script the object in the database snapshot 1 Execute the script in the source database 2 Repopulate the object (if appropriate) 3 Caution: Not a substitute for a comprehensive backup and restore strategy Using Database Snapshot to Recover Data INSERT INTO Production.WorkOrderRouting SELECT * FROM AdventureWorks_dbsnapshot_1800.Prod.WorkOrderRouting UPDATE HR.Department SET Name = ( SELECT Name FROM AdventureWorks_dbsnapshot_1800.HR.Department WHERE DepartmentID = 1) WHERE DepartmentID = 1

13. Demo Database Snapshot

14. Quick Puzzle

15. Transaction log restore - Puzzle 03:22 PM

16. Multi-Instance Technologies

17. Log shipping

18. Log Shipping • Log shipping allows you to automatically send transaction log backups from one database to a secondary database on another server • Logs are restored automatically • Secondary server can be the failover server and can become primary if the main server goes down

19. Copy Copy Copy Log Shipping (in Action) Copy and Restore Backups Perform Backups Secondary Database Copy and Restore Backups Secondary Database Primary Database Copy and Restore Backups Raise Alerts Secondary Database Monitor Database

20. Strength & weakness • Strengths • Can Ship Logs Across WAN (Wide-Area Network) • Protects an Entire Database • Weaknesses • Configured Per Database • NO AUTOMATIC FAILOVER

21. Demo Log shipping

22. Database Mirroring

23. 4 >3 >2 3 1 5 2 2 Commit Application SQL Server SQL Server Database Mirroring How it works Mirror is always redoing – it remains current Witness Principal Mirror Log Data Log Data

24. 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)

25. 6. Write Page 5. Transfer page Log Log Data Data 2. Request page 3. Find page 1. Bad Page Detected X 4. Retrieve page DBM – Automatic Page Recovery Witness Client Principal Mirror

26. DBM – Log Compression

27. Strength & Weakness • Strengths • Can Mirror Across WAN • Automatic Failover, and Nearly Instantaneous, Better than Failover Clustering • Protects an Entire Database • Weaknesses • Requires Enterprise Edition • Must be Configured Per Database

28. Demo Mirroring Setup

29. Demo Auto Page Repair

30. Failover Clustering

31. Failover Clustering

32. Failover Cluster * Inst1 Multiple Active Nodes N+1: N Active, 1 Inactive Nodes N+M: N Active, M Inactive Nodes * Inst1 * Inst1 Inst3 * Inst2* Inst2* SQL Server Cluster Topologies • Supports many scenarios: • Single Instance • Multiple Instance • Multiple Active Nodes • N+1 • N+M

33. Failover Clustering (Facts) • Redundancy at database instance level • All databases fail over together • Shared copy of system databases • Single data copy on shared storage device • No I/O overhead reducing throughput • Storage unit is single point of failure for cluster • All database services are clustered • SQL Agent; Analysis Services; Full-Text engine, MS DTC • Automatic failover (up to minutes) • DBMS accessed over virtual IP • Storage is controlled by one cluster node at a time • Requires hardware certified by Microsoft for Microsoft Cluster Service

34. Strength & Weakness • Strengths • Provides Protection Against a Node Failure, Protects the Entire SQL Instance • Automatic Failover Supported • Weaknesses • Generally Expensive, Requires Specialty Hardware • Specialty Hardware Requirements • Not Trivial to Configure and Manage • Doesn’t Protect Against a Complete Site Failure

35. Replication

36. Replication • 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

37. Peer-to-Peer Replication • Provides high availability and read scalability • Builds redundancy by eliminating single point of failure • Enable online upgrades of servers • Maximize Application Uptime • Support for both Ring and Grid Topology • Centralized Management using Management Studio Peer Node Peer Node Peer Node Peer Node

38. New Features Replicated Data Write Read Application Server User Requests

39. Strength & Weakness • Strengths • Perpetual or on-demand replication of data, local or remote • Protects (duplicates or merges) the exact portion of the database I want • Weaknesses • Configured per database, even per table • Generally does not protect or duplicate an entire Database

40. SQL Server “Denali” High Availability

41. SQL Server AlwaysOn • Increased Application Availability at Lower TCO with Ease of Use: • Multi-Database Failover • Multiple Secondaries • Active Secondaries • Fast Client Connection Redirection • Windows Server Core • Multisite Clustering

42. Availability Group Concepts • Availability Group • Defines the high availability requirements • Databases, Replicas, Availability Mode, Failover Mode etc • Availability Replica • SQL Server Instances that are part of the availability group which hosts the physical copy of the database • Role: Primary, Secondary, Resolving • Availability Database • SQL Server database that is part of an availability group • This can be a regular database or contained database

43. Readable Secondary Reports SQLservr.exe SQLservr.exe Primary Secondary • Readable secondary allow offloading read queries to secondary • Close to real-time data, latency of log synchronization impact data freshness InstanceA InstanceB Database Log Synchronization DB1 DB2 DB1 DB2

44. Demo SQL Server “Denali” High Availability Configuration Read-only Secondary

45. Comparing High Availability Technologies

46. Comparing High Availability Technologies

47. SummarySQL Server High Availability 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 • Database Maintenanceand Operations • Fast Recovery • Partial Availability • Online Restore • Media Reliability • Dedicated Administration Connection • Dynamic Configuration • Availability at Scale • Data Partitioning • Replication • Tuning • Database Tuning Advisor

48. References • SQL Server Code Name Denali CTP1 http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx • SQL Server Books Online

49. Feedback / Q&A • Your Feedback is Important! Please take a few moments to fill out our online feedback form at: www.virtualtechdays.com For detailed feedback, use the form at http://www.connectwithlife.co.in/vtd/helpdesk.aspx Or email us at vtd@microsoft.com • Use the Question Manager on LiveMeeting to ask your questions now!

50. Contact • Blog Address http://blogs.msdn.com/BLakhani http://blogs.msdn.com/SQLServerFAQ • Email Address blakhani@microsoft.com