SQL Server

1
SQL Server

• High Availability

Craig Ryan National Manager Database Services
2
Agenda

• How much Availability do you need?
• SQL 2000 High Availability Options
• Log Shipping, Replication, Clustering
• Demo SQL 2000 H.A Options
• New HA options in SQL 2005
• Database Mirroring
• Demo SQL 2005 Database Mirroring

3
How Much Availability do you need?

• Need to ask yourself
• How long can we afford to be down?
• How much data can we afford to lose?
• Availability up/(updown)

4
Barriers To Availability
Many barriersOnly some are addressable by DBMS
technologyBe sure to consider people, planning,
and procedures

• Database Server Failure or Disaster
• User or Application Error
• Data Access Concurrency Limitations
• Database Maintenance and Operations
• Upgrades
• Availability at Scale

5
Warm Standby SolutionsReplication and Log
Shipping

• Database Object Level
• SQL Server Replication
• Database Level
• Log Shipping
• Both provide multiple copies and a MANUAL fail
  over

6
Log Shipping

• Minimal impact on the production server
• No changes to the database are required
• Transactional consistency
• Supports delayed load of transaction logs
• Not all SQL Server objects are automatically
  copied
• Users must exit for next log to be applied

7
Demo

• SQL 2000 Log Shipping

8
Replication

• Failover possible a custom solution
• Not limited to entire database Can define subset
  of source database or tables
• Copy of database is continuously accessible for
  read activity
• Latency between source and copy can be as low as
  seconds
• Significantly increases the on-going Database
  management
• Database schema may need to be changed

9
Demo

• SQL 2000 Transactional Replication

10
Failover Clustering Microsoft Cluster Services

• Hot Standby Automatic failover
• Built on Microsoft Cluster Services (MSCS)
• Multiple nodes provide availability, transparent
  to client
• Automatic detection and failover
• Requires certified hardware
• Supports many scenarios Active/Active, N1, NI
• Instance Failover entire instance works as a
  unit
• Single copy of instance databases
• Available since SQL Server 7.0
• Standby is not available for reporting, queries,
  etc.
• May support other instances

11
Active/Passive SQL Server Cluster
Client PCs
SQL Server
Virtual Server
Server A
Server B
Heartbeat
Cluster management
Shared Disk Array
Hub
Hub
E
F
G
C,D
C,D
SQL Server
12
Active/Passive SQL Server Cluster
Client PCs
SQL Server
Virtual Server
Server A
Server B
Heartbeat
Cluster management
Shared Disk Array
Hub
Hub
E
F
G
C,D
C,D
SQL Server
13
What Clustering doesnt do

• Clustering is not a mechanism to scale
• Doesnt protect your server against site outage
• Doesnt protect your disk subsystem
• Doesnt protect against database corruption
• Doesnt protect against logical corruption
• Doesnt protect against user error
• Doesnt protect application crash
• Clustering is not a method to load-balance

Still a single point of failure The Database!
14
Demo

• SQL 2000 Failover Clustering

15
Log Shipping vs Clustering vs SQL Replication

• So whats the best solution.

it depends
On your business requirements

• You can combine the SQL H.A options.
• E.g. A/P Cluster with Log shipping

16
SQL Server 2005

• High Availability

17
Barriers To AvailabilityAs addressed in SQL
Server 2005

• Database Server Failure or Disaster
• Failover Clustering
• Database Mirroring
• Transparent Client Redirect
• User or Application Error
• Data Access Concurrency Limitations
• Database Maintenance and Operations
• Availability at Scale

18
Failover Clustering SQL Server 2005

• Further refined in SQL Server 2005
• More nodes
• Match operating system limits
• Unattended setup
• Support for mounted volumes (Mount Points)
• All SQL Server services participate
• Database Engine, SQL Server Agent, Analysis
  Services, Full-Text Search, etc.

19
Database MirroringNew for SQL Server 2005

• Instant Standby
• Conceptually a fault-tolerant server
• Database Failover
• Very Fast less than three seconds
• Zero data loss
• Automatic or manual failover
• Automatic re-sync after failover
• Automatic, transparent client redirect

20
Database Mirroring

• Hardware
• Works with standard computers, storage, and
  networks
• No shared storage components, virtually no
  distance limitations
• Impact to transaction throughput
• Zero to minimal, depending on environment /
  workload

21
Database Mirroring How it works
Mirror is always redoing it remains current
Witness
Principal
Mirror
Log
Data
Data
Log
22
Witness and Quorum

• Sole purpose of the Witness is to provide
  automatic failover
• To survive the loss of one server you must have
  at least three
• Prevents split brain
• Does a lost connection mean the partner is down
  or is the network down?
• To become the Principal, a server must talk to at
  least one other server

23
Witness

• Witness is an instance of SQL Server 2005
• Single witness for multiple sessions
• Consumes very little resources
• Not a single point of failure
• Partners can form quorum on their own

24
Safety / Performance

• There is a trade-off between performance and
  safety
• Database Mirroring has two safety levels
• FULL commit when logged on Mirror
• Allows automatic failover
• No data loss
• OFF commit when logged on Principal
• System does its best to keep up
• Prevents failover to make mirror available
• Must force service
• Or terminate Database Mirroring session

25
Transparent Client Redirect

• No changes to application code
• 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

26
Database Mirroring Setup Steps

• Ensure SQL 2005 is installed on both the
  principle the mirror Server
• Setup Security (Endpoints)
• Prepare the mirror database
• Setup Start the database mirroring session
• Optionally add a witness

27
Demo

• SQL 2005 Database Mirroring

28
Database States for Database Mirroring

• SYNCHRONIZING
• SYNCHRONIZED
• SUSPENDED
• PENDING_FAILOVER
• DISCONNECTED

29
Automatic Failover

• Automatic Failover requires the following
  conditions
• Database mirroring running in synchronous mode
• The database must be in a synchronised state
• A witness must exist
• During Failover the following actions occur
• The witness mirror server agree the primary is
  dead, which puts the database into a suspend
  state
• If possible the database on the Primary server
  changes to a disconnected state
• The mirror finishes rolling forward and records
  the LSN
• The mirror database comes online
• When the principle returns, it becomes the mirror

30
Failover Solutions At A Glance

• Clustering Mirroring both provide
• Automatic detection and failover
• Manual failover
• Transparent client connect

• Database Mirroring
• Database scope
• Standard servers
• Fastest failover
• Limited reporting on standby
• Duplicate copy of database

• Failover Clustering
• System scope
• Certified hardware
• Fast failover
• No reporting on standby
• Single copy of database

31
Summary

• How many 9s do you NEED?
• SQL 2000 potential downtime 3 mins
• SQL 2005 potential downtime 3 secs
• Remember to consider process too!

32
Feel free to contact us

• Craig Ryan
• National Manager Database Services
• cryan_at_sdg.com.au
• Andrew Gannon
• Business Development Manager
• agannon_at_sdg.com.au
• [phone]