DB SPOFMySQL, MSSQL

Contents• MySQL• MSSQL

Contents• MySQL• MSSQL

3-High Availability Architecture1. Replication2. Clustering & Virtualization3. Shared-Nothing, Failover Clusters

1. Replication• Master-slave

– Database updates are replicated from a master to either one or more slaves

– Master logs the changes to the database– Slaves applies the logs

• Replication modes– Asynchronous (default)– Semi-synchronous– Synchronous

Replication Workflow

Session thread processes queries from the applicationDump thread reads events from binary log and sends them to a slaveI/O thread receives replication events and stores them in slave’s relay logSQL thread reads replication events from slave’s relay log and applies them to slave database

Replication topology• Replication can be deployed in a range of topolo-

gies to support diverse scaling and high avail-ability

2. Clustering & Virtualiza-tion

• Clustering solutions– Oracle VM Template– Solaris Cluster– Windows Cluster– DRBD

• Windows Cluster– Requirements

• Windows Server 2008 R2• Network Connections between

nodes and storage• Shared Storage (SAN)

3. Shared-Nothing, Failover Clusters• Data synchronously replicated to all nodes within the node

group– Reduces fail-over times

• Nodes can be distributed across multiple hosts– Allows to operate even during hardware failure

• Each data node has its own disk and memory– A failure in shared storage does not cause an outage

Comparison of MySQL SolutionsReplication

Clustering & Virtual-ization

(Windows Server)Shared-Nothing,Failover Clusters

Supported Storage En-gine

All (InnoDB for Auto Failover) InnoDB NDB (for auto-sharding,

failover and recovery)Auto IP Failover No Yes Yes

Auto Database Failover Yes Yes YesAuto Data Resynchro-nization Yes N/A –Shared Storage Yes

Failover Time > 5s > 5s + InnoDB Recov-ery Time < 1s

Replication Mode Asynchronous / Semi-Synchronous N/A – Shared Storage Synchronous

Shared Storage No, distributed across nodes Yes No, distributed across

nodes

# of Nodes Master & Multiple Slaves

Active / Passive Master + Multiple Slaves 255 + Multiple Slaves

Availability Design Level 99.9% 99.95% 99.999%

Contents• MySQL• MSSQL• NP?

4-High Availability Technologies1. Log Shipping2. Transactional Replication3. Database Mirroring4. Failover Clustering

1. Log Shipping• Primary / Secondary server(s)

– Based on transaction log restore process– Backup, copy, restore, repeat…– Cannot guarantee zero data loss

• Warm standby solution– Does not have mechanism to automate failover

• The failures are not automatically detected– Extra logic must be added to the client or in a mid tier

Log Shipping Configuration• One primary server runs the transaction log backup job• Three secondary servers run its own copy job and restore job• One monitor server receives history from both primary and secon-

daries

2. Transactional Replication• Publisher• Subscriber• Distributor

• Replication Flow– Committed transactions are read

from the transaction log– Stored in the distribution database on

the distributor– Applied to the subscription databases

Transactional Replication• Limitations

– Warm standby solution• Does not provide automatic detection of failure and auto-

matic failover to a subscriber– Data loss

• There is latency between a transaction occurring on the publisher and being propagated to the subscribers

– Only protects the data in the publication• It cannot be used to protect the entire database or group of

databases

3. Database Mirroring• Provides a redundant copy of a single database

– Main database(principle) / Redundant database(mirror)– Allows only a single mirror

Database Mirroring• Synchronous mirroring with witness server

– High-availability mode• The mirror can initiate failover automatically

– Hot standby solution

• Synchronous mirroring without witness server– High-safety mode– Warm standby solution

• Asynchronous mirroring– High-performance mode– Warm standby solution

4. Failover Clustering• The only technology that allows an entire SQL Server instance to

be made high-available– Leverages Windows Failover Cluster service

• Shared storage– There is only one copy of the data

• Hot standby solution

Comparison of SQL Server Solutions

Log Shipping Replication Mirroring Failover Clus-tering

Automatic Failover No No Yes (high-availability mode) Yes

Failure Detection No No Yes (high-availability mode) Yes

Hot/Warm Standby Warm Warm Hot/Warm HotAutomatic Applica-tion Redirection No No Yes Yes

Relative Cost Low Low Low High

References• MySQL

– High availability• http://www.mysql.com/products/cluster/availability.html

• SQL Server– High availability

• http://technet.microsoft.com/en-us/library/ee523927(v=sql.100).aspx

– Failure cluster• http://www.brentozar.com/archive/2012/02/introduction-sql-server-c

lusters/