SQL Server

1
SQL Server

• High Availability

Amit Vaid
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

1. Ensure SQL 2005 is installed on both the
   principle the mirror Server
2. Setup Security (Endpoints)
3. Prepare the mirror database
4. Setup Start the database mirroring session
5. 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]