1 / 38

Course Topics

Course Topics. 06 | High Availability Options. George Squillace | Senior Technical Trainer – New Horizons Great Lakes Richard Currey | Senior Technical Trainer – New Horizons United. Module 6 Overview. What Is High Availability? Replication Log Shipping Mirroring AlwaysOn

jena-juarez
Download Presentation

Course Topics

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. Course Topics

  2. 06 | High Availability Options George Squillace | Senior Technical Trainer – New Horizons Great Lakes Richard Currey | Senior Technical Trainer – New Horizons United

  3. Module 6 Overview • What Is High Availability? • Replication • Log Shipping • Mirroring • AlwaysOn • Failover Clustering

  4. Topic: What Is High Availability?

  5. Topic: What Is High Availability (HA)? • Redundancy of some kind • Protection against media failure • Replication • Log Shipping • Database Mirroring • AlwaysOn • Protection against hardware or physical server failure • Failover Clustering • Sometimes a combination of HA technologies are used together

  6. Topic: Replication

  7. Topic: Replication • The Basics • Types of Replication • Implementing Replication • Replication Strengths and Weaknesses

  8. The Basics • Publisher / distributor / subscriber • Publication type • Select DB and article(s) • Table (complete or filtered) • Sproc • View • Indexed view • UDF (TVF) • Configure job schedules • Configure agent security

  9. Types of Replication • Snapshot • Transactional • Transactional with updateable subscriptions • Merge

  10. Implementing Replication • Multiple destinations • Separate IP subnets • Multiple job schedules • Multiple shared folders • Witness optional • Manual failover

  11. Replication Strengths and Weaknesses • Strengths • Mature and stable • Flexible configuration options • No specialty hardware required • Can span data centers • Secondary database copies can be queried and potentially modified • Weaknesses • Manual client failover • Protects only data; does not protect schema, system tables and so on • Can be difficult to repair • Configured on a per-database basis

  12. Implementing Replication

  13. Topic: Log Shipping

  14. Topic: Log Shipping • The Basics • Implementing Log Shipping • Failover Basics • Log Shipping Strengthsand Weaknesses

  15. The Basics • Protects one user database at a time • Uses a scheduled log backup job of the primary database from the primary server • Each secondary server uses a scheduled file copy job to place log backups nearby • Each secondary server uses a scheduled log restore job to restore to its secondary database copy • Provides limited read-only access to secondary database copies

  16. Implementing Log Shipping • SSMS • Stored procedures • sp_Add_Log_Shipping_Primary_Database • sp_Add_Job_Schedule • sp_Add_Log_Shipping_Secondary_Database • sp_Add_Log_Shipping_Alert_Job • sp_Add_Log_Shipping_Primary_Secondary • sp_Add_Log_Shipping_Secondary_Primary • Set DB recovery model full or bulk_logged • Create a backup job • Create copy job(s) • Create restore job(s) • Configure monitor (optional)

  17. Failover Basics • Copy transaction log backups (if available) • Backup up primary database log with NORECOVERY • Restore primary database log on secondary server with RECOVERY • Disable log shipping jobs • Configure log shipping on new primary server

  18. Log Shipping Strengths and Weaknesses • Strengths • Mature and stable • Multiple copy jobs and restore jobs each with different schedules for applying restores • Not too difficult to initially configure or manage • No specialty hardware required • Can span data centers • Secondary database copies can be queried (but not modified) • Alert job can raise an alert if a backup or restore operation does not complete within a threshold • Weaknesses • Manual failover • Configured on a per-database basis • Can’t protect Master

  19. Implementing Log Shipping

  20. Topic: Mirroring

  21. Topic: Mirroring • The Basics • Configuration Options • Handling Failover • Mirroring Strengths and Weaknesses

  22. The Basics • User database transactions are live shipped to a single secondary and applied synchronously or asynchronously • Depending on configuration, database failover and recovery can occur within ten seconds with automatic client redirection • Scope of protection–single user database at a time

  23. Configuration Options • Single mirror database copy • Three major configurations • High safety with automatic failover • Witness required • High safety without automatic failover • High performance (asynchronous log hardening) • Full recovery model required • Endpoint configuration required • Port • Authentication • Encryption • Geographical spanning support • GUI or code configuration

  24. Mirroring Strengths and Weaknesses • Strengths • Very fast and automatic database and client failover • Not too difficult to initially configure or manage • No specialty hardware required • Can span data centers • Weaknesses • Deprecated • Requires three servers in high-availability mode • Cannot query the mirrored database unless database snapshots are implemented • Configured on a per-database basis • Does not protect system databases

  25. Implementing Database Mirroring

  26. Topic: AlwaysOn

  27. Topic: AlwaysOn • The Basics • Implementing AlwaysOn • AlwaysOn Failover • AlwaysOn Strengths and Weaknesses

  28. The Basics • New feature in SQL Server 2012 • Provides a failover environment for a set of databases that fail together • A collection of primary replica databases support read-write connections • A collection of secondary replica databases (up to four) support read-only connections • Requires a Windows Server failover cluster • Synchronous-commit and asynchronous-commit modes • Planned and automatic failover withno data loss

  29. Implementing AlwaysOn • Each instance hosting an availability group must be a Windows Server (Enterprise Edition) failover clustering node • Same collation on each instance • Enable the AlwaysOn availability groups feature on each instance (SQL Server Configuration Manager or Windows PowerShell) • Run the new Availability Group Wizard • Add desired databases, full recovery model • Configure a database mirroring endpoint • Create an availability group listener • Specify replicas • Select an initial synchronization option • Full • Join only • Skip initial data synchronization

  30. AlwaysOn Failover • Three failover modes • Automatic failover (without data loss) • Planned manual failover (without data loss) • Forced manual failover (with possible data loss) • During the failover, the failover target takes over the primary role, recovers its databases, and brings them online as the new primary databases • The former primary replica, when available, switches to the secondary role, and its databases become secondary databases • The form(s) of failover that a given availability replica supports is specified by the failover mode property • Synchronous-commit replicas • Asynchronous-commit replicas

  31. AlwaysOn Strengths and Weaknesses • Strengths • Very fast and automatic database and client failover • Very flexible configuration with multiple failover replicas • Read-only access to replicas • Can back up replicas to offload work • No special hardware necessary • Can span data centers • Weaknesses • Complex

  32. Topic: Failover Clustering

  33. Topic: Failover Clustering • The Basics • Implementing Failover Clustering • Failing Over with Failover Clustering • Failover Clustering Strengths and Weaknesses

  34. The Basics • Provides protection in the event of a catastrophic hardware (server) failure • Requires the Windows Server Failover Cluster service • Only supports cluster-aware services or applications such as Microsoft SQL Server or Microsoft Exchange Server • Requires shared disk storage (Fibre Channel or iSCSI) • Clients connect to a virtual name hosted by one of the nodes in the cluster • Provides instance-level availability with automatic and manual failover

  35. Implementing Failover Clustering • Multiple server nodes • Specialized storage requirements • Nodes can now span subnets • Requires the Windows Clustering Feature • Requires SQL Server installed on the cluster

  36. Failing Over with Failover Clustering • Determined by failover policy; usually set to automatic • The resource group owner (server node) is determined by quorum • Node majority • Node and file share majority • Node and disk majority • Disk only • Failover time period may be lengthy • Failover node restarts the instance and recover of all databases occurs • Zero reconfiguration of applications and clients

  37. Failover Clustering Strengths and Weaknesses • Strengths • Stable and mature • Protects an entire instanceand system databases • Easier than ever to setup with the Windows Cluster Validation Tool • Weaknesses • Specialty hardware requirements • More expensive • Requires more expertise • Does NOT duplicate database data; failover clustering should be combined with another HA technology that duplicates database data

More Related