High availability and disaster recovery for SQL Server.
To reduce or eliminate the need to any planned or unplanned downtime in a
database, are to of the most important responsibility of any DBA [database
administrator]. More and more businesses are realizing the importance of an
investment in high availability to improve their operational performance
and to reduce the impact of downtime when a disaster happens.
A report from 2012 Aberdeen group shows mid-sized enterprises spend around
$74,000 per hour in downtime costs and average of $880,600 per year.
In our recent survey, we asked respondents if they have a high availability
or disaster recovery plan for their databases. 70% responded yes, and 30%
responded no.
What are high availability and disaster recovery?
High availability is about providing service availability and 1005 uptime
through redundant and fault-tolerant components at the same location.
Disaster recovery is about providing service continuity and minimizing
downtime through redundant and independent site in a distinct location.
- | high availability | disaster recovery
goal | service availability | service continuity
process | most likely automatic | most likely manual
infrastructure | single location | two or more locations
intention > retaining service | retaining data
Common solutions.
- | high availability | disaster recovery
failover clustering | yes | no
database mirroring | yes (synchronous) | yes (asynchronous)
Always On availability groups | yes | yes
log shipping | no | yes
SAN [storage area network] based virtualization replication | no | yes
Supported platforms for each solution
- | failover cluster | Always On availability group | database mirroring |
log shipping
SQL 2008 | yes | no | yes | yes
SQL 2008 R2 | yes | no | yes | yes
SQL 2012 | yes | yes (4 replicas maximum) | yes | yes
SQL 2014 | yes | yes (8 replicas maximum) | yes | yes
SQL 2016 | yes | yes (8 replicas maximum) | yes | yes
Survey says: What type of high availability solution do you use for your
SQL Server database? Failover clustering 55%, database mirroring 23%,
replication 11%, and log shipping 11%.
