Downloaded 64 times
Uploaded byJohn Sterrett
Introduction to High Availability with SQL Server
The document provides an overview of high availability (HA) and disaster recovery (DR) solutions in SQL Server, outlining various options such as log shipping, database mirroring, replication, failover cluster instances, and AlwaysOn availability groups. It covers definitions, functionalities, and pros and cons of each method, emphasizing the importance of reducing downtime and ensuring data protection. The content is aimed at improving business operations through effective database management strategies.
Introduction to High Availability with SQL Server
- 1. Introduction to High Availability with SQL Server 10/14/2014 www.linchpinpeople.com 1
- 2. John Sterrett LinchpinPeople Group Principle @johnsterrett John.Sterrett@linchpinpeople.com Johnsterrett.com linked.com/in/johnsterrett google.com/+johnsterrett Leader Austin SQL Server User Group Leader Virtual Chapter “High Availability and Disaster Recovery” PASS Regional Mentor Specialties / Focus Areas / Passions: • Performance Tuning & Troubleshooting • Virtualization • Infrastructure • Architecture • High Availability • Disaster Recovery • Health Monitoring • Capacity Management • License Efficiency www.linchpinpeople.com 2
- 3. You know usindividually, but do you know Linchpin People®? Linchpin People® are database coaches and wellness experts for the Microsoft SQL Server Ecosystem. Our team includes more than 15 of the world’s top SQL Server experts, and we have presented over 175 combined sessions at SQL Saturdays, PASS Summit, and other events in the past year. Do you have specific questions about your database environment? Check out www.LinchpinPeople.com and take a look at the range of services we provide, or contact one of us today to learn how we can help improve your business. www.linchpinpeople.com 3
- 4. Agenda • HA!= DR • Log Shipping • Database Mirroring • Transactional Replication • AlwaysOn Failover Cluster Instance • AlwaysOn Availability Groups • Wrap Up / Question and Answer 10/14/2014 www.linchpinpeople.com 4
- 5. Introduction Definitions •What do we mean when we say a system is Highly Available? • What does it mean to have a Disaster Recovery system? 10/14/2014 www.linchpinpeople.com 5
- 6. High Availability Whatis High Availability • High availability is a system design approach and associated service implementation that ensures a prearranged level of operational performance will be met during a contractual measurement period. – Wikipedia • A System Design that allows for minimal downtime. • Protection from OS or hardware failure 10/14/2014 www.linchpinpeople.com 6
- 7. Examples of Disasters Photo https://www.flickr.com/photos/ross/39556863/ 10/14/2014 www.linchpinpeople.com 7
- 8. Most Common Disaster 10/14/2014 www.linchpinpeople.com 8
- 9. Disaster Recovery Whatis Disaster Recovery • Risk Management • Recovery Point Objective - RPO • Recovery Time Objective - RTO • Disaster Recovery Plan - DRP • Backups are not enough 10/14/2014 www.linchpinpeople.com 9
- 10. Options SQL ServerOptions for HA/DR • Log Shipping • Database Mirroring • Replication • Failover Cluster Instances • AlwaysOn Availability Groups 10/14/2014 www.linchpinpeople.com 10
- 11. Intro to LogShipping 1. Transactional Log Backup 2. Copy Transactional log backups 3. Restore Transactional log backups http://www.mssqltips.com/sqlservertip/2073/migrating-a-vldb-in-sql-server-with-log-shipping/ 10/14/2014 www.linchpinpeople.com 11
- 12.
- 13. Log Shipping •Transaction Log backups occur on the primary • Process ships logs to secondary server(s) • Data can be read on the secondary except during transaction log restore • Can have a Log Ship monitor server 10/14/2014 www.linchpinpeople.com 13
- 14. Monitoring Log Shipping • sp_help_log_shipping_monitor 10/14/2014 www.linchpinpeople.com 14
- 15. Log Shipping Pros/Cons • Works with Standard Edition • Multiple target support • Readable secondary copies • Can delay restores • Dependent on backup of primary • Manual failover process • Reasonably high complexity 10/14/2014 www.linchpinpeople.com 15
- 16. Database Mirroring -Modes • High Safety (Synchronous) with Automatic Failover • High Safety without Automatic Failover • High Performance (Asynchronous) Primary Server Secondary Server Witness Instance Mirror DB Mirror DB 10/14/2014 www.linchpinpeople.com 16
- 17. Synchronous Mode… Mode Principal • Transaction (Insert/Update/Delete) inserted into Transactional Log • Transaction read from the transactional log • Transaction shipped to mirror Mirror • Transaction written to mirrors transactional log • Acknowledgement sent to principal • Transaction committed Principal • Acknowledgement received • Transaction committed. 10/14/2014 www.linchpinpeople.com 17
- 18. Asynchronous Mode… (Enterprise) Mode Principal • Transaction (Insert/Update/Delete) inserted into Transactional Log • Transaction read from the transactional log • Transaction committed • Transaction shipped to mirror Mirror • Transaction written to mirrors transactional log • Acknowledgement sent to principal • Transaction committed Principal • Acknowledgement received 10/14/2014 www.linchpinpeople.com 18
- 19. Database Mirroring •Deprecated in SQL 2012 • Database transactions are compressed and shipped to a secondary (2008+) • Transfer may be sync or async (Enterprise) • Optional witness server can facilitate failover 10/14/2014 www.linchpinpeople.com 19
- 20. Warning.. Warning.. Warning.. http://technet.microsoft.com/en-us/library/ms366349.aspx
- 21. Pros and Cons • Pros • Witness can be any edition of SQL Server • Page level corruption fixes • Doesn’t require Active Directory • Database level High Availability • High Safety only requires Standard Edition • Cons • You cannot mirror system databases • Automatic Failover requires a witness • Database must be in full recovery • High Performance requires Enterprise Edition • Must create and sync Instance Objects (Jobs, Logins, etc..) 10/14/2014 www.linchpinpeople.com 21
- 22. Replication • Numeroustopologies and options involved with replication • Snapshot Replication • Transactional Replication • Merge Replication • Peer to Peer Replication 10/14/2014 www.linchpinpeople.com 22
- 23.
- 24. How Transactional Replication Works http://technet.microsoft.com/en-us/library/ms151706(v=sql.105).aspx
- 25. Replication Pros/Cons •Replicate to multiple servers • Replicate subset of data • Can be done with Simple Recovery Model • Standard Edition for transactional • Manual failover process • Unknown RPO • Known to be fragile • Re-sync can be cumbersome • Complex 10/14/2014 www.linchpinpeople.com 25
- 26. Failover Cluster Instances Windows Failover Cluster SQL Instance Node 1 Node 2 Cluster Virtual Name 10/14/2014 www.linchpinpeople.com 26
- 27. How Windows -SQL Clustering works • Physical Nodes • Windows cluster • Windows cluster group • MSDTC • SQL cluster • Other clustered application • End User Access 27
- 28. Failover Cluster Instance(FCI) • Pre Windows 2012 – Shared Storage • Windows Cluster (Windows 2012 Standard Ed) • Two Node Limit on SQL 2012 Standard • Quorum 10/14/2014 www.linchpinpeople.com 28
- 29. How Window –SQL Clustering Works – Failover Cluster Manager Things that you should see in Service and Application/ Role • Current Status • Current Owner • Resources • Client Access Point • Virtual Network Name • IP address assigned with the name • Storage allocated to the application • Services / Applications or Predefined Resources • Others - optional • File shares • Printers and so on 29
- 30.
- 31.
- 32.
- 33.
- 34.
- 35. Failover Cluster Pros/Cons • Multiple Servers (HA) • Allows for entire instance protection • Automatic failover • Transparent connections • Complex setup • Idle hardware • Some cases storage single point of failure 10/14/2014 www.linchpinpeople.com 35
- 36. Introduction to AvailabilityGroups Instance 1 Instance 2 AG (P) AG (S) Node 1 Node 2 Data Center 1 Data Center 2 Listner Name (VCO) Windows Failover Cluster 10/14/2014 www.linchpinpeople.com 36
- 37. Database Mirroring Problems… • Cannot group databases • Only get one mirror database • No readable copies • Requires witness (extra SQL instance) for automatic failover 10/14/2014 www.linchpinpeople.com 37
- 38. Availability Group Benefits.. • No shared storage • Listener (Virtual Computer Object for connectivity) • Multiple mirrors replicas • Readable copies 10/14/2014 www.linchpinpeople.com 38
- 39. AlwaysOn Availability Groups • SQL Server Enterprise Edition • Windows Cluster • Async and Sync modes • Databases failover as a group • No shared storage needed • Readable replicas • Supports up to 8 replicas 10/14/2014 www.linchpinpeople.com 39
- 40.
- 41.
- 42. AG Configurations 10/14/2014www.linchpinpeople.com 42
- 43. AG Dashboard 10/14/2014www.linchpinpeople.com 43
- 44. AlwaysOn Pros/Cons •No shared storage • Readable secondaries • SSMS administration • Configuration is easy • Grouping DB failover helps for complex applications setups • Enterprise Only - Large setups can be expensive • Newer technology • App code changes may be needed 10/14/2014 www.linchpinpeople.com 44
- 45.