Oracle DBAs

Deploying Highly
Available
SQL Server Systems
Joe Yong

Chief Architect
Scalability Experts Inc.
jyong@scalabilityexperts.com

About This Session

Goals
Overview of SQL Server 2005 High Availability
features
Drilldown on HA implementation strategies

Non-goals
Deep dive into SQL Server
Chest thumping
Make you a HA expert

Pre-requisites
Experience as an Oracle DBA, Architect or
Developer DBA
Basic experience in designing, deployment and
managing database systems that require
medium to high levels of availability

Agenda
What is High Availability
SQL Server 2005 HA overview
Solutions to common scenarios
Case study
Summary

What is High Availability

Uninterrupted usability
A running server is not necessarily available

Is a factor of technology, people and processes

Often measured as a percentage in uptime over
1 year
Eg. 99.999% uptime = 5.25 minutes downtime a year
Should includes both planned and unplanned downtime
but many only measure unplanned

You may not own every part of the equation but if

you have to specify your SLA
Example
Online ordering system requires orders to be confirmed in
30 seconds
Availability is impacted by application scalability, network
and database
Dont forget security impact on HA

Agenda
What is High Availability
SQL Server 2005 HA technologies
Hot Standby
Failover Clustering
Database Mirroring

Warm Standby
Database Mirroring High Protection / Performance mode
Replication
Log Shipping

Cold Standby
Backup/restore

Online operations

Solutions to common scenarios

Case study
Summary

Failover Cluster

Microsoft Failover Clustering

Overview

* Inst1

Hot Standby Automatic failover

Protects against local, limited disasters
Built on Microsoft Server Clusters (MSCS)
Multiple nodes provide availability, transparent to client
Supports 2, 4, or 8 nodes depending on OS edition

Automatic detection and failover

Requires cluster certified hardware; see Windows Catalog:
Clustered
Supports many scenarios: Multiple Active Instances, N+1, N+I
Up to 25 SQL Server instances per cluster

NOT a load balancing

solution
N+1: N Active, 1 Inactive

Multiple Active Instances

Instances

* Inst1
Inst3 *
Inst2 *

* Inst1
Inst2 *

N+I: N Active, I Inactive

Instances

Microsoft Failover Clustering

Detail

Geographically Dispersed
Clusters
Same functionality and behavior as standard failover
cluster
Protects against local, total and extended disasters
Requires specially certified cluster hardware from qualified
vendors
Requires guaranteed 500ms maximum round trip latency
between nodes
Site 1
Site 2
Network
SQL Server does not differentiate
between standard and
geo-cluster
N1

N2

N3

Storage Controller S2

Storage Controller S1
D1

D3

N4

Mirror

Mirror

D2

D4

High Availability Toolbox

Disaster Recovery
Features
Local limited
disasters
Local disasters
Extended disasters

Failover
Clusterin
g

With geoclusters

Limited
With geoclusters

Rapid failover
Failure detection

Automatic
switchover

Specialized
hardware

Secondary workload
on standby
(reporting)

Meta data support

Performance impact
Protect against
storage failure

None

Agenda
What is High Availability
SQL Server 2005 HA technologies
Hot Standby
Failover Clustering
Database Mirroring High Availability mode

Warm Standby
Database Mirroring High Protection / Performance mode
Replication
Log Shipping

Cold Standby
Backup/restore

Online operations

Solutions to common scenarios

Case study
Summary

Database Mirroring

Database Mirroring

Overview

Hot Standby
Provides a fault-tolerant database
Building block for complex topologies

Database Failover
Very fast failover
Less than five seconds in most cases

Zero data loss

Automatic or manual failover

Automatic re-sync after failover

Automatic, transparent client redirect

Works with standard certified servers, storage and
networks
No location limitations

No shared components; two separate copies of

data

Database Mirroring
How does it work?
Application

Mirror is always
redoing it
remains current

Witness
(optional)

Commit

Principal

Mirror

SQL Server

2
Log

>2
Data

SQL Server

3
Log

>3
Data

Database Mirroring
Witness

Only required for automatic failover;

Just another instance of SQL Server 2005
Can serve multiple sessions

Prevents split brain scenario

If partners do not see each other, is it due to
network failure or server failure?

To become Principal automatically, a server

must talk to at least one other server
Witness ONLY answers the question Who do
you see?, does not promote a server to be
Principal

Database Mirroring
High Availability mode

Safety Full; synchronous operation

Commit when logged on Mirror
Allows automatic failover
No data loss

Database available whenever quorum

exists
Formed by any two servers from the three;
Principal, Mirror, Witness

Witness is present automatic Failover

Database Mirroring

Transparent Client Redirect

SQLConnection object that targets a mirrored
database
No application code change required
Client automatically redirected if session is
dropped
Client library is aware of Principal and Mirror servers
Upon initial connect to Principal, library caches Mirror
name
When client attempts to reconnect
If Principal is available, connects
If not, client library automatically redirects connection to
Mirror

If Principal is down upon first connect attempt, connection

fails
Workaround via explicit coding or NLB type solution

High Availability Toolbox

Disaster Recovery
Features
Local limited
disasters
Local disasters
Extended disasters

Failover
Clusterin
g

Database
Mirroring
HA
mode

With geoclusters

Limited
With geoclusters

Rapid failover
Failure detection

Automatic
switchover

Specialized
hardware

Secondary workload
on standby
(reporting)

Meta data support

User DB only

None

Minimal ~
low

Performance impact
Protect against

With DB
snapshot

Agenda
What is High Availability
SQL Server 2005 HA technologies
Hot Standby
Failover Clustering
Database Mirroring High Availability mode

Warm Standby
Database Mirroring High Protection / Performance
mode
Replication
Log Shipping

Cold Standby
Backup/restore

Online operations

Solutions to common scenarios

Case study
Summary

Database Mirroring
High Protection mode

Safety Full; synchronous operation

Commit when logged on Mirror
No automatic failover; manual failover only

Database quorum formed by Principal and

Mirror
If Principal loses quorum, it stops servicing
the database
Ensures high protection; database is never in
exposed state

No Witness present no automatic failover

Database Mirroring

High Performance mode

Safety Off; asynchronous operation
Commit when logged on Principal
No automatic failover; manual failover only
Possible data loss

If Mirror becomes unavailable; Principal

continues working
If Principal becomes unavailable; Mirror can
assume workload
Manual failover to Mirror is required

No Witness present; no automatic failover

High Availability Toolbox

Disaster Recovery
Features
Local limited
disasters
Local disasters
Extended disasters

Failover
Clusterin
g

Database
Mirroring
HA
mode

With geoclusters

Limited
With geoclusters

Rapid failover

Database
Mirroring
H P/P
mode

Failure detection

Automatic
switchover

Specialized
hardware

Secondary workload
on standby
(reporting)

Meta data support

User DB only

User DB only

None

Minimal ~
low

Minimal

Performance impact
Protect against

With DB
snapshot

With DB
snapshot

Agenda
What is High Availability
SQL Server 2005 HA technologies
Hot Standby
Failover Clustering
Database Mirroring High Availability mode

Warm Standby
Database Mirroring High Protection / Performance mode
Replication
Log Shipping

Cold Standby
Backup/restore

Online operations

Solutions to common scenarios

Case study
Summary

Transactional Replication
Requires consideration at design time; cannot just
flip the switch
High performance latency measured in seconds
Some (minimal) load on the server
Can be implemented at database or table level
Failover possible; custom designed solution
Two types
Standard transactional replication
Easy to design, setup & manage
Subscriber (standby) can be used for reporting

Peer-to-peer transactional replication

Multi-master model; schema is identical on all sites
Supports distributed applications with data partitioning;
enables load balancing
Does not handle conflicts; design to avoid/prevent conflicts

Peer-To-Peer Transactional
Replication
How does it work?
West

East

Logreader
Agent

Dist
DB
Distribution
Agent

Logreader
Agent

South

Logreader
Agent

Dist
DB
Distribution
Agent

Dist
DB
Distribution
Agent

High Availability Toolbox

Disaster Recovery
Features
Local limited
disasters
Local disasters
Extended disasters

Failover
Clusterin
g

Database
Mirroring
HA
mode

With geoclusters

Limited
With geoclusters

Rapid failover

Database
Mirroring
H P/P
mode

Transaction
al
Replication

Failure detection

Automatic
switchover

Specialized
hardware

Secondary workload
on standby
(reporting)

Meta data support

User DB only

User DB only

Some

None

Minimal ~
low

Minimal

Low

Performance impact

With DB
snapshot

With DB
snapshot

Can automate
in application

Can automate
in application

Agenda
What is High Availability
SQL Server 2005 HA technologies
Hot Standby
Failover Clustering
Database Mirroring High Availability mode

Warm Standby
Database Mirroring High Protection / Performance mode
Replication
Log Shipping

Cold Standby
Backup/restore

Online operations

Solutions to common scenarios

Case study
Summary

Log Shipping
Backup transaction log, copy to secondary
server, restore transaction log backup
Failover is manual
Meta data management may be necessary

Read operations on secondary is permitted

Users are disconnected when log restore occurs

Can maintain multiple secondary servers

Optional Monitor server
Records history and status of backup/restore
jobs
May be setup to raise alerts when jobs fail

High Availability Toolbox

Disaster Recovery
Features
Local limited
disasters
Local disasters
Extended disasters

Failover
Clusterin
g

Database
Mirroring
HA
mode

Database
Mirroring
H P/P
mode

With geoclusters

Limited
With geoclusters

Rapid failover

Transaction
Log
al
Shipping
Replication

Failure detection

Automatic
switchover

Specialized
hardware

Secondary workload
on standby
(reporting)

Meta data support

User DB only

User DB only

Some

User DB
only

None

Minimal ~
low

Minimal

Low

Low

Performance impact

With DB
snapshot

With DB
snapshot

Can automate
in application

Can automate
in application

Can automate
in application

With
limitations

Agenda
What is High Availability
SQL Server 2005 HA technologies
Hot Standby
Failover Clustering
Database Mirroring High Availability mode

Warm Standby
Database Mirroring High Protection / Performance mode
Replication
Log Shipping

Cold Standby
Backup/restore

Online operations

Solutions to common scenarios

Case study
Summary

Backup and Restore

Slowest recovery (but also simplest)
Recommended as secondary or tertiary
protection layer
Manual failure detection and switchover
Data loss possible
Recommend maintaining active backups on
disk; duplicate, archive and offsite backups
on tape
Various levels
Database full, differential, partial, differential
partial, copy-only
File & filegroups full, differential

Backup and Restore

RESTORE VERIFY ONLY
Backup media mirroring
Backup and database page checksums
Fine grained online repair
Online restore
Piecemeal restore
Page-level restore

Database backup does not block Log

backup
Backup/restore includes FullText data
Copy-only via T-SQL only

High Availability Toolbox

Disaster Recovery
Features
Local limited
disasters
Local disasters
Extended disasters

Failover
Clusterin
g

Database
Mirroring
HA
mode

Database
Mirroring
H P/P
mode

With geoclusters

Limited
With geoclusters

Rapid failover
Failure detection

Automatic
switchover

Specialized
hardware

Secondary workload
on standby
(reporting)

Meta data support

User DB only

User DB only

None

Minimal ~
low

Minimal

Performance impact

With DB
snapshot

With DB
snapshot

Transaction
Log
al
Shipping
Replication

Can automate
in application

Can automate
in application

Backup /
Restore

With
limitations

With
limitations

Some

User DB
only

User DB
only

Low

Low

Low

Agenda
What is High Availability
SQL Server 2005 HA technologies
Hot Standby
Failover Clustering
Database Mirroring High Availability mode

Warm Standby
Database Mirroring High Protection / Performance mode
Replication
Log Shipping
Database Snapshot

Cold Standby
Backup/restore

Online operations

Solutions to common scenarios

Case study
Summary

Online Operations
Backup/restore
Full online backup
Online piecemeal restore; undamaged data
remains available

Indexing
Allows create, drop and alter while users
continue to access data
LOB datatype indexes not supported for online

Memory allocations
CPU affinity settings
Database snapshots

Database Snapshot
Not originally designed as a specific HA
solution but works great in some situations
Turning a Database Mirroring mirror into a
reporting server
Isolated historical data for report
generation
Protection in case of administrative,
developer or user error; classic Oops!
scenario
Uses copy-on-write technique to reduce
disk space consumption

Database Snapshot
How does it work?

CREATE DATABASE mydbSnap AS SNAPSHOT OF mydb

USE mydb
UPDATE (pages 4, 9, 10)
mydb Database
Page
1

10

11

12

13

14

15

mydbSnap Read-Only Database Snapshot

USE mydbSnap
SELECT (pages 4, 6, 9, 10, 14)

16

Agenda
What is High Availability
SQL Server 2005 HA technologies
Solutions to common scenarios
Case study
Summary

Rolling Upgrades
In three steps

1. Perform upgrades on the mirror,

secondary,
or subscriber
2. Switch roles

1.

Backup principal log with no-recovery

3.

Re-direct clients to secondary

2. Recover secondary
Database Mirroring
Failover to the mirror

Log Shipping
Replication

Redirect clients to subscriber

3. Perform upgrades on the original database

server

Optional: Switch roles again

Site Disaster Protection

Example Scenarios
Earthquake, fire, or flood causes datacenter
outage

Solutions
Database Mirroring to a secondary site
Optimized solution - Allows very fast failover times to
the secondary site
Optionally add log shipping for additional site
protection

Log Shipping to one or more secondary sites

Basic solution requires additional effort for failover

Third-party geo-clustering solutions for data

center storage level redundancy
Find SQL Server Always On reviewed solutions at the

Database Mirroring
Configuration
In
three steps
Step 1: Restore

database copy to
mirror site with
no-recovery option

Step 2:
Configure
communication
endpoints
Step 3: Set the
data protection
level and Start
Mirroring

Database Query Workload Scale

Out With Redundancy
Scenario
Need for near real time reporting on a second
server that can also be used for disaster
recovery
Need for a tier of identical databases for scaling
out application queries with ability to use any
one of the database copies for disaster recovery

Solutions
Transactional Replication
Peer-to-Peer Replication

Putting It All Together

Failover Clustering

Replication

Local server
redundancy
Full server/instance
protection

Database
Scale Out
For Queries

Database Mirroring
Primary disaster site
for databases
Reporting with
Snapshot
Failover

Log Shipping
Additional disaster
sites for databases
Logical recovery
(with delay)

Replication
Database reporting
and read scale out
with redundancy

Clustering

Production
Database

Database Mirroring

Hot
Standby

Log Shipping
Warm
Standby
Log Shipping
With Restore Delay
Logical
Recovery
Standby

Agenda
What is High Availability
SQL Server 2005 HA technologies
Solutions to common scenarios
Summary

Summary
<<WiP>>

Resources
www.microsoft.com/sql/
msdn.microsoft.com/sqlserver/
www.microsoft.com/technet/
www.scalabilityexperts.com
www.sqldev.net
www.sqlservercentral.com/

High Availability Toolbox

Disaster Recovery
Features
Local limited
disasters
Local disasters
Extended disasters

Failover
Clusterin
g

Database
Mirroring
HA
mode

Database
Mirroring
H P/P
mode

With geoclusters

Limited
With geoclusters

Rapid failover
Failure detection

Automatic
switchover

Specialized
hardware

Secondary workload
on standby
(reporting)

Meta data support

User DB only

User DB only

None

Minimal ~
low

Minimal

Performance impact

With DB
snapshot

With DB
snapshot

Transaction
Log
al
Shipping
Replication

Can automate
in application

Can automate
in application

Backup /
Restore

With
limitations

With
limitations

Some

User DB
only

User DB
only

Low

Low

Low