SQL -HA & DR Solutions

High availability option can be tricky. The decision really depends on these items.

 At a high level, there are five main high availability options

SA

MIRRORING 

Log shipping

Replication 

SQL Server Replication Overview

At a high level, replication involves a publisher and subscriber, where the publisher is the primary server and the subscriber is the target server

There are four types of replication that we will outline:

• • Snapshot replication

  • Transactional replication

  • Merge replication

  • Peer to Peer replication

SQL Server Database Mirroring Overview

Database Mirroring involves a principal server that includes the principal database and a mirror server that includes the mirrored database

SQL Server Log Shipping Overview

Log shipping involves one primary server, one monitor server (optional), and can involve multiple secondary servers.

SQL Server Clustering Overview

Clustering involves at least two servers and is more of a server level high availability option compared to a database level option.Clustering will allow one physical server to take over the responsibilities of another physical server that has failed

Active/Active: When running in Active/Active mode, SQL Server is actually running on both servers.

Active/Passive: When running in Active/Passive mode, SQL Server runs on one server while the other server waits in case of a failure. This is the most popular choice because it doesn't affect performance; however

SQL Server AlwaysON Overview

AlwaysON is a new feature shipping with SQL Server 2012 and is an alternative to database mirroring. AlwaysON uses groups called Availability Groups, which are groups that contain selected databases that will fail over together if a failure should occur.