Summary of SQL Server high Availability Technologies

Original: SQL Server high Availability Technology summary

I. Replication replication (snapshot, transaction, merge)

Application Scenarios:
Load balancing, providing copy read, write operations.
partitions copy historical data into other tables.
Authorization to make the data available to anyone who uses it.
Data merge.
Fail over.
Advantages:
Simple to implement.
The data is synchronized at the same time, almost mirroring.
It is possible to replicate some tables, or table data filtering.
Disadvantages:
Not suitable for high availability because the entire library replication affects performance.
Automatic failure switching is not supported.
Requirements:
You must have a table for the primary key to replicate.

Two. Log shipping logs Shipping (backup-to-copy-restore)

Application Scenarios:
Multiple master-Slave server scheduled backup synchronization.
Load balancing, providing replica read-only.
Advantages:
Simple to implement.
Requirements:
Must be a full backup mode.
The backup folder for the primary server, secondary server, and monitoring server must have read and write permissions.
The SQL Agent agent must be started.

Three. Mirroring

Application Scenarios:

Requires high service availability.

Requires automatic failover.

Ensure the integrity of the data.

Advantages:

Synchronous and asynchronous mirroring can be implemented according to the business.

Fast failover recovery. Low hardware costs.

Requirements:

The principal server.

mirror server.

The witness server.

Four. Failover clustering

Application Scenarios:
Hardware fail-over.
Service failover.
Manual failure or various other reasons.
Advantages:
Flexible cluster topologies based on the business
Fast and automatic failover
Disadvantages:
Cluster inactive node wastes hardware resources
Shared storage shared between cluster nodes, there is still a potential single point of failure vulnerability.
No load capacity, not a technology to improve performance.

Requirements

Windows Fail-Over

A summary of the five (does not contain always on and is the SQL 2005 version of the summary) from the Microsoft Instructor for reference. Ha ha

Summary of SQL Server high Availability Technologies