Course Topics

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