FAQ Topics

All FAQ's

General DBA

General Developer

DBA Performance Tuning

Developer Performance Tuning

Clustering

Error Messages

Write for Us

Share you SQL Server knowledge with others and raise your profile in the community More...

My manager has asked me to put together a plan for a backup server for our production server running SQL Server. Should I use log shipping or clustering?

Most of the production SQLServersI have run across don't have any easy or quick way to failover to a working server should they fail. Generally, most people hope their production SQL Server never fails. And in the back of their minds, they think they know what to do to recover should the worst happen, but for the most part, they don't have any formal plans, nor have they tested how they actually plan to failover. In these cases, you can assume that it might take a day or longer for them to get back into production should their SQL Server fail.

If your production SQL Server is not critical to the success of your business, and you can afford to be down a day or two, then not having a formal failover plan is not much of a problem

On the other hand, if your production SQL Server is critical to the success of your organization, then you must have a tested failover plan in place. Two popular ways to implement a failover plan is to either implement SQL Server log shipping or clustering. Which of these is your best choice?

The question you have to ask yourself is what you mean by best?

If by best, you mean a failover solutionthat fails over automatically and quickly (a minute or so), the SQL Server clusteringis the answer.

If by best you mean a failover solution is that somewhat easy to implement and less expensive, then log shipping is the answer.

Unfortunately, there is no best answer. You have to wade through the pros and cons and decide for yourself what is the best compromise. Here are the major pros and cons of log shipping vs. clustering.

Pros of Log Shipping

Relatively speaking, less expensive.
Relatively speaking, less work to set up.
Relatively speaking, less on-going maintenance.
Requires less experienced DBAs and networkadministrators.

Cons of Log Shipping

Failover is not quick, may take from 15 - 60 minutes or more of downtime.
Failover is not automatic, it must be manually implemented.
Failed over server must be renamed to the name of the failed server.
Failing back to the original server, once it is fixed, is a manual process that can require 15 - 60 minutes or more of downtime.

Pros of Clustering

Failover is quick, usually resulting in less than minute or two of downtime.
Failover is automatic, no manual intervention is required.
Clustering doesn't require any servers to be renamed.
Failing back is quick, usually resulting in less than minute or two of downtime.

Cons of Clustering

More expensive than log shipping.
Requires more set up than log shipping.
Requires more on-going maintenance.
Requires more experienced DBAs and network administrators.