High Availability Options in SQL Server 2008

High Availability Options are depend on the following two objectives and any desired High Availability option should satisfy these objectives,

Recovery Time Objective (RTO)
Recovery Point Objective (RPO)

• Recovery Time Objective (RTO) is the maximum acceptable amount of time for restoring a network or application and regaining access to data after an unplanned disruption.
• Recovery point objective (RPO) is the maximum acceptable amount of data loss after an unplanned data-loss incident.

There are 4 High Availability options in SQL Server 2008. Failover Clustering, Database Mirroring, Log Shipping and Replication. Each option has its own features and benefits.

High Availability Options in SQL Server 2008A. Failover Clustering

This provides server-level redundancy on a certified Microsoft Windows Cluster Services Configuration. A failover cluster is setup with a minimum of two servers. In the cluster setup, each server is called a node. All the nodes are connected to shared disk resources. Only one node is active at a time for a SQL Server instance and serves all the requests for that instance of SQL Server. When the active node fails, failover takes place and one of the other available nodes will become the active node.

High Availability Options in SQL Server 2008

Recovery Objectives:

• Recovery Time Objective (RTO) – Almost immediately, because processing is handed over to another node in the cluster.
• Recovery Point Objective (RPO) – If there is no data corruption or data loss (due to data deletion), there is minimal to no data loss during the failover.

Failover Cluster Components: Servers (Nodes), Shared Network Storage.

Advantages:

• It provides automatic failover.
• It protects an entire SQL Server instance.
• Can apply service packs to one node at a time to minimize downtime.

Disadvantages:

• It is more expensive since it requires special hardware for the setup as well as redundant hardware.
• It is not supported with all SQL Server editions.
• It is a single shared data resource.

B. Database Mirroring

Database mirroring is a primarily software solution for increasing database availability. It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. This is a good option when there is the need to have automatic failover for a database. It can be near real time failover of a database depending on the options used. Also a good choice if there is a need to provide database connectivity with minimal downtime.

High Availability Options in SQL Server 2008

Recovery Objectives:

• Recovery Time Objective (RTO) – Depending on the options used it could be almost immediately for a database, because the mirror copy will become the primary copy. Also, if you use the Failover Partner option in the connection string the application should be able to find the new server without any other configuration changes.
• Recovery Point Objective (RPO) – If there is no data loss (due to data deletion), there is minimal to no data loss during the failover. Because this is an exact copy of the primary database all transactions will be replicated to the mirror copy as they were done on the primary server.

Mirroring Components : Principal server, mirror server, and witness server (Optional).

Advantages:

• It provides automatic failover. (if used with a witness)
• Snapshots of the database can be created against the mirrored copy for read only access and off-loading reporting to another server.
• It provides near real time failover of a database, depending on the options used.
• No additional cost, except for the need to have another available server for the mirror and possibly a third for the witness.

Disadvantages:

• High safety without automatic failover setting may be network overhead.
• A third server is required for “automatic failover” (witness – SQL Server Express can be used)
• Other items need to be handled outside of mirroring such as logins, SQL Agent jobs, etc…
• Requires additional storage for mirrored copy.
• If Snapshots are used for read only, the snapshot is only as current as when the snapshot was created.

C. Log Shipping

It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled. If the active server goes down, the standby server can be brought up by restoring any remaining shipped logs and then the database is recovered for use.

High Availability Options in SQL Server 2008

Recovery Objectives:

• Recovery Time Objective (RTO) – Failover is manual for this option, so this will take as long as it takes you to get the secondary server up and running.
• Recovery Point Objective (RPO) – If there is no data loss (due to data deletion), there is minimal to no data loss during the failover. Because transaction backups are applied to the secondary server all transactions will be replicated as they were done on the primary server. If you delay applying the transaction logs you could do a point in time recovery right before an accidental deletion of data occurred.

Log Shipping Components : Primary server, secondary server and monitor server (Optional).

Advantages:

• Log shipping can be configured to multiple standby servers.
• Can use compressed backup feature to limit network bandwidth
• It includes all the database objects along with their schema changes.
• By specifying Log Shipping in short interval, it can be near real-time database.
• Can be setup to use secondary server for read only activity
• No additional cost, except for the need to have another available server for the secondary copy.
• Can delay when the logs get applied to secondary server to help minimize data loss in the event of accidental data deletion (point in time recovery)
• If secondary server is used in a passive mode (only for failover), additional SQL Server licensing is not needed.

Disadvantages:

• There is no automatic failover.
• Other items need to be handled outside of Log Shipping such as logins, SQL Agent jobs, etc…
• Requires additional storage for log backups and shipped copy
• If standby copy is used for read only, SQL requires exclusive access to the database when a log backup is restored

D. Replication

Replication is a set of technologies for copying and distributing data and database objects from one database to another. Replication is a desired option when we want to have the same data on multiple database servers. It is good option when we want to have multiple server updates with disconnected options. It can be near real time. It can also be used to implement load balancing for a database.

High Availability Options in SQL Server 2008

Recovery Objectives:

• Recovery Time Objective (RTO) – This could be immediate and only require re-pointing your application to a different server.
• Recovery Point Objective (RPO) – If there is no data loss (due to data deletion), there is minimal to no data loss during the failover. Because this is an exact copy of the primary database all transactions will be replicated to the mirror copy as they were done on the primary server.

Replication Components : Publisher, Subscribers, Distributor (Optional).

Advantages:

• Can be configured for individual database objects instead of the entire database.
• Enhanced tools available for configuration and troubleshooting.
• Provides near real time disaster recovery.
• It supports disconnected architecture, so it is beneficial for mobile users.
• Secondary copy could be used for near real-time reporting to offload reporting use on production database.

Disadvantages:

• Reconfiguration may require manual intervention.
• Need to rely on distributor to push changes.
• Other items need to be handled outside of Replication such as logins, SQL Agent jobs, etc…
• It is more complicated to setup and maintain then the other options.
• SQL Server will need to be fully licensed for the secondary server.


<<Click here to see all posts>>

2 comments on “High Availability Options in SQL Server 2008”

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.