Course Topics Administering SQL Server 2012 Jump Start 01

Course Topics Administering SQL Server 2012 Jump Start 01 Install and Configure SQL Server 04 Manage Data 02 Maintain Instances and Databases 05 Implement Security 03 Performance Optimization and Troubleshooting 06 High Availability Options

Click to edit Master subtitle style 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 Failover Clustering

Topic: What Is High Availability?

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

Topic: Replication

Topic: Replication The Basics Types of Replication Implementing Replication Replication Strengths and Weaknesses

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

Types of Replication Snapshot Transactional Transactional with updateable subscriptions Merge

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

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

DEMO Implementing Replication

Topic: Log Shipping

Topic: Log Shipping The Basics Implementing Log Shipping Failover Basics Log Shipping Strengths and Weaknesses

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

Implementing Log Shipping SSMS Stored procedures o sp Add Log Shipping Primary Database o sp Add Job Schedule o sp Add Log Shipping Secondary Database o sp Add Log Shipping Alert Job o sp Add Log Shipping Primary Secondary o 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)

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

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

DEMO Implementing Log Shipping

Topic: Mirroring

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

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

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

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

DEMO Implementing Database Mirroring

Topic: AlwaysOn

Topic: AlwaysOn The Basics Implementing AlwaysOn AlwaysOn Failover AlwaysOn Strengths and Weaknesses

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 readonly connections Requires a Windows Server failover cluster Synchronous-commit and asynchronous-commit modes Planned and automatic failover with

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

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

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

Topic: Failover Clustering

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

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

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

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

Failover Clustering Strengths and Weaknesses Strengths – Stable and mature – Protects an entire instance and 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

2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, Office, Azure, System Center, Dynamics and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.