SQL Server 2008 High Availability Options

By:   |   Updated: 2011-03-31   |   Comments (6)   |   Related: > Disaster Recovery


Problem

One of my clients asked me about implementing a High Availability option for his organization. They are using SQL Server 2008, so I told him we should evaluate each of the High Availability options that can be implemented for SQL 2008. I also recommended that the preferred option should consider the Recovery Time Objective (RTO) and Recovery Point Objective (RPO) for the business. In this tip, I cover SQL Server 2008 High Availability options that are available out of the box.

Solution

There are 4 High Availability options in SQL Server 2008 that we will cover; Failover Clustering, Database Mirroring, Log Shipping and Replication. Each one has its own features and benefits.

As you may know, the Recovery Time Objective (RTO) which is the tolerable maximum length of time that a system can be unavailable and Recovery Point Objective (RPO) which is how much data can be lost, need to be considered to meet the organizational objectives for each application that is critical to the business. Any desired High Availability option should satisfy these objectives.

High Availability Options

SQL Server 2008 provides options for setting up high availability for a server, database and object level.

The Failover Clustering supports server level high availability whereas Database Mirroring and Log Shipping are per database and Replication is object level. The below image depicts these breakdowns.

high availability options in sql server 2008

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. More information can be read from this MSDN library article.

Use:

  • This is an appropriate option for mission critical applications where automatic failover is needed for the entire instance of SQL Server.

Recovery:

  • 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.

Pros:

  • It provides automatic failover.
  • It protects an entire SQL Server instance.
  • Can apply service packs to one node at a time to minimize downtime.
  • If secondary server is used in a passive mode (only for failover), additional SQL Server licensing is not needed.
Cons:
  • 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. (see chart below)
  • It is a single shared data resource.

Database Mirroring

This provides database redundancy by transferring data from the transaction log to another instance of SQL Server. There are several operating modes that can be used such as: high performance, high safety with automatic failover and high safety without automatic failover. Based on the operating mode, we can have complete or almost complete database redundancy. More information can be read from this MSDN library article.

Use:

  • 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.

Recovery:

  • 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.

Pros:

  • 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.
  • If secondary server is used in a passive mode (only for failover), additional SQL Server licensing is not needed.
Cons:
  • 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.

Log Shipping

It provides database redundancy by sending transactional log backups periodically to a standby server or servers for the entire database. Transaction logs are automatically backed up, copied, and restored on the standby server(s). 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. More info on Log Shipping can be read from this MSDN library article.

Use:

  • It is good option when we want to have multiple secondary failover servers for a database.

Recovery:

  • 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.

Pros:

  • 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.
Cons:
  • 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

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. There are different types of replication and more info on Replication can be read from this MSDN library article.

Use:

  • 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.

Recovery:

  • 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.

Pros:

  • 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.
Cons:
  • 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.

Selecting a High Availability Solution

Each SQL Server 2008 High Availability option addresses different risks. As we know, no "one" solution fits all. We need to review carefully the business requirements. Basically, we need to consider a few questions like:

  • Is Automatic Failover required?
  • What is the data granular level for the High Availability?
  • How much data loss or downtime is accepted?
  • Is the standby server needed to provide read access?
  • What are the budget constraints?

The High Availability requirement may differ from client to client, some clients may need the database to be up and running 24X7 and others may require only during office hours. So based on the client criticality need we have to choose appropriate the High Availability option. Otherwise there will be unnecessary expenses in terms of money, performance and resource utilization.

It is also very important to know what options are available in the different SQL Server editions before planning for the actual implementation. The below table shows differences between Enterprise and Standard High Availability features.

HA Options Enterprise Standard
Clustering 16-node failover clustering 2-node failover clustering
Database Mirroring Full Support Partial Support (Single Threaded, Safety Full Only)
Log Shipping Full Support Full Support
Replication Full Support Oracle Publishing not supported.

To summarize:

  • Failover Clustering is an ideal selection if there is no budget constraint.
  • Database Mirroring is alternative choice with minimal down time, because of automatic failover.
  • Log Shipping is a good selection if we want to have multiple secondary servers.
  • Replication is a good selection when there is the need to duplicate some of the data, send the data to multiple servers or for disconnected architecture support.
  • Multiple options can be used, if there is a need to further protect from failures.
  • This is not a complete list of all pros and cons for each option, but I hope this gives you a starting point on which technology to consider for your environment.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Murali Krishnan Murali Krishnan is a Lead Consultant with vast experience in Database/BI Design, Development and Administration.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-03-31

Comments For This Article




Tuesday, March 5, 2013 - 7:24:02 AM - khalid mehmood awan Back To Top (22569)

Excellent... that helped me.

Wednesday, February 13, 2013 - 9:23:18 AM - AJAY Back To Top (22099)

Hello there,

Excellent article on HA.  My client want's to setup the replication but were not sure about the licensing cost as well as number of licenses required.  Could you please let us know the minimum number of licenses required to support replication?  Does standard editions support replication?

 

Thanks

Ajay

Friday, January 25, 2013 - 12:16:40 AM - Kailas Tare Back To Top (21702)

Excellent article with a good summary of all the important points about high availabilty. My personal favourite is two cluster mirroring approach. One cluster hosts primary database and another cluster hosts mirroring database. You will also need witness server (sql express can be used) or a witness cluster.

Wednesday, January 9, 2013 - 10:52:55 AM - Uditha Back To Top (21346)

Thanks Good article

 

 

 

Thursday, March 31, 2011 - 2:42:59 PM - bass_player Back To Top (13403)

Nowadays, doing high availability via virtualization. Microsoft Hyper-V's Live Migration as well as VMWare's vSphere can be viable HA options for SQL Server 

Thursday, March 31, 2011 - 12:12:05 PM - Melvin Back To Top (13400)

An additional facet when considering availability solutions, is 'scalability'.

Replication sounds great, but does not scale very well when dealing with more then just a few databases in a given instance. To scale out for replicating more databases in a given instance, you have to expand your RPO window to what may become excessively large values.

Log shipping has a simular issue. Since you are moving larger objects (in a high transaction environment), servicing multiple databases for a given instance becomes equally complex. Shipping logs off to multiple servers is nice. But, you have a linear flow issue where you can only move so many parallel objects from one system to another. It is easily possible that you have processed only half of the objects (log files) you need to ship off when the failure occurs. If any of your databases are codependant on each other and must remain in sync, then you can only restore up to the point of the oldest of the most recent available transaction logs to maintain temporal integrity.

Mirroring is a great idea and likely also suffers the same scaling issues, but I have not tried to push that barrier myself. Mirrorings main weakness lies in the external applications capability to deal with the failure event. Legacy applications (assuming you are not creating your own applications) may not be able to deal with the failure by simple DSN string capabilities. If they use configuration files that you can not manipulate outside of the applications own coniguration tools, you may be stuck having to manually repoint the application to the new server. Still, at minimum your data loss window is greatly reduced. Your RTO time just goes up a bit.

I am a fan of the clustering solution. It is far more expensive than the above choices, but creates an application agnostic situation. Since the SQL Server instance is 'virtual', it does not matter to the external application on which physical node the instance is hosted. When a failure event occurs, there will be a brief period where the application is disconnected from the SQL database they were talking to. This is equivalent to a physical server reboot. If the application is written to deal with this kind of event (it does not have to be 'cluster aware'), it should try to automatically reconnect to the database server without manual intervention.

Clustering scales incredibally well, in that you are not replicating anything. All database data resides in a shared storage solution and switches ownership of the data from one physical server to the other during the failure event. Data loss is exactly the same as when a physical single server looses power and is rebooted. SQL's integrity checks, and log rollbacks will bring up the databases (most of the time) without issues. Any 'in-flight' transactions that had not been completed will be reversed out. A well written external application can also deal with this and resubmit the transaction when the server comes back online, or alert the user that their request failed and they should resubmit it.














get free sql tips
agree to terms