SQL Server High Availability Solutions

1. Clustered Instances

Clustered Instances features:

  • Provide HA at the Instance level.
  • Requires Windows Failover Clustering technology.
  • It is usually used when you have multiple SQL instances.
  • You can configure each instance to run under a different owner so that all your SQL servers will be used.

2. Backups

Backup Type:

  • Full Backups – this will backup the entire database.
  • Differential Backups (cumulative backup) – this will contain all the data since the last full backup.
  • Transaction Log Backups (not cumulative backup) – this will contain the data since the last full, differential or transaction log backup.
  • Copy-Only Backup – a copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base.

Recovery Model:

  • Simple – transaction logs will be truncated but you won’t be able to do Point in Time Recovery.
  • Full – you will be able to do Point In Time Recovery and truncate the logs by using Transaction Log Backups.
  • Bulk-logged – can’t do point in time recovery.

Only Transaction Log Backups truncate transaction logs.
Point In Time recovery can be achieved by using Transaction Log Backups with Full Recovery Model.
You can use a Maintenance Plan to automate backups or SQL Server Agent.

3. Always On Availability

Always on availability features:

  • Provides HA at the database level
  • A better version(replacement) for Database Mirroring.
  • Can have multiple (8) Secondary databases that can be readable.
  • Very fast replication in case the Active database is unavailable.
  • Requires Windows Failover Clustering technology.
  • There are 2 availability modes: Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency(allows Automatic and Manual(Planned) Failover); Asynchronous-commit mode is a disaster-recovery solution that works well when the availability replicas are distributed over considerable distances.(only allows Manual(Forced) Failover)
  • You can combine Always On with Failover Cluster Instances.
  • AlwaysOn Listeners give the ability to access the Availability Group through a single IP and route the requests to the Active or Passive database.

 

NOTE: All databases in a single AG must be in the same instance. So if you have and AppDB_A in Instance_1 and AppDB_B in Instance_2, they cannot be part of the same AG. Only one instance of SQL Server per cluster node can participate in Availability Groups due to the coordination between the Availability Groups and their underlying Windows Server Failover Cluster. To clarify that a bit, you cannot install 2 SQL Server instances to the same Windows Server Failover Cluster node and have one instance host a replica for on Availability Group and the other instance host a replica for a different Availability Group. Instead, you would have a single SQL Server Instance on the Windows Server Failover Cluster node that would participate in both of the Availability Groups.

4. Database Mirroring

  • HA at the database level.
  • Can have only one secondary database.
  • The secondary database is not readable.
  • The failover is similar to AlwaysOn.

5. Replication

Mode            Separate Server Provides Secondary Database Data Availability, Latency Secondary Data can be read for reports
AlwaysOn Y Y (multiple) Minimal Y (real-time)
Mirroring Y Y (one database) Seconds No
Clustering Y No Seconds N/A
Log-Shipping Y Yes Minutes/hours Y (data frozen but updated)
Replication Y Sub set Minutes/hours Y (data frozen but updated)
Snapshot N Sub set Minutes/hours Y (data frozen)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s