There are 5 methods of SQL
high availability are available in SQL Server.
1.
Logshipping
2.
Replication
3.
Database
Mirroring
4.
Always on
5.
Failover
Cluster
Feature
|
DB Mirroring
|
Replication
|
Log Shipping
|
FailoverClustering
|
Always On
|
Server Level
|
No
|
No
|
No
|
Yes
|
No
|
Database Level
|
Yes
|
Yes
|
Yes
|
No
|
Yes
|
Automatic Failover
|
Yes (Only HA Option)
|
No
|
No
|
Yes
|
Yes
|
Automatic Failure Detection
|
Yes (Only in HA option)
|
No
|
No
|
Yes
|
yes
|
Hot, Warm or Cold standby
|
Hot and Warm Options
|
Warm
|
Warm
|
Hot
|
Hot and Warm Options
|
Server Level Objects Failover
|
Manual
|
Manual
|
Manual
|
Yes (Automatic)
|
Yes (Automatic)
|
Is data on Failover available for queries while the Primary
server is running?
|
Yes (use Database Snapshots)
|
Yes
|
Yes (Standby Mode)
|
No
|
no
|
Are changes allowed at Failover location?
|
No
|
Yes (Merge and TRN Replication with updating subscribers)
|
No
|
No
|
no
|
Is Main and Failover server in a 1-1 relationship?
|
Yes
|
Not Required
|
Not Required
|
Yes
|
yes
|
Is 1-Many relationship possible between the Main and Failover
servers?
|
No
|
Yes
|
Yes
|
No
|
yes
|
Is Many-1 relationship possible between Main and Failover
server?
|
No
|
Yes
|
No
|
No
|
yes
|
Is Automatic Application redirection possible after failover?
|
Yes
|
No
|
No
|
Yes
|
yes
|
Recovery model required for the participating databases?
|
FULL
|
ANY
|
FULL & BULK
|
Any
|
full
|
Can you filter what data is sent to the Failover server?
|
No
|
Yes
|
No
|
No
|
no
|
Where does the logic HA reside?
|
Sql
|
Replication Agents (they run outside Sql Server
|
Sql Agents (Run outside Sql Server)
|
OS + Sql
|
OS + Sql
|
If you want a reports server, what is the best solution?
|
Works, but needs constant refresh of the Snapshot database for
latest data.
|
Best Option.
|
Works, but users connections need to be kicked out, when new
log is applied.
|
Not Possible
|
Best Option.
|
Best solution if the Main and Failover server are separated by
long distance?
|
Works, but not recommended.
|
Works, but with high latency
|
Best Option. (Log compression reduces latency significantly.
|
Not good.
|
Works, but with high latency
|
Please let
me know if still need to update anything, I will update the same.
No comments:
Post a Comment