Transcript Course Topics - Center

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
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 (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
• 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
• 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
• 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
• GUI or code configuration
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
• 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 read-only
connections
• Requires a Windows Server failover
cluster
• Synchronous-commit
and asynchronous-commit modes
• Planned and automatic failover with
no data loss
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
• 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.