The Pitfalls of Log Shipping on SQL Server

 Common incorrect assumptions about Log Shipping I’ve seen.

Many people think Log shipping is a type of high availability solution that is a much cheaper solution than clustering however log shipping does not replace clustering. Clustering allows fail-over through the redundancy of the hardware and the OS not the SQL data. If you want High Availability for the SQL data look at Replication or synchronous mirroring. Log shipping is really more Disaster Recovery, which is used to enable you to recover from a disaster whether the disaster is external or internal etc.

Think Log shipping keeps the two servers in Sync -- wrong! Log Shipping occurs at the database level and not at the SQL server level so anything that is changed in other areas of the SQL Server such as configuration changes, OS changes will be unknown to the database that is been log shipped.

What is Log Shipping?

Log shipping is the means of establishing a warm backup solution by using the SQL Server Native or third party utilities to transfer and restore logs between databases.

Log shipping is a Disaster Recovery strategy for databases and nothing more. It does have some side benefits. For instance, the database that is on the standby server can be used for reporting, which is a good thing. If a production database fails, all you have to do is point the users to the standby server, and you are all set. Well, it’s not really that simple, but it comes close if you put enough effort into your log shipping setup.

So why use Log Shipping?

In today’s world, business cannot afford disruptions to applications, web sites or information. Most of the data stored for these areas is found in backend databases. So as good DBAs we look at ways of making our data safer by using standby servers to have a copy of the database either onsite or offsite. If the worst occurs we can swap to the standby server and be back up and running in next to no time.

Log shipping is actually the process of backing up the transaction log for a given database and sending it to another server where we restore. If the time between backups is minimal (the default most DBAs tend to use is 15 mins) than we only risk a limited amount of data loss if something goes wrong.

The Pit Falls of Log Shipping that DBA’s forget.

Imagine you’ve gone to the trouble to implement log shipping and now it is time to bring the standby server online due to a problem with the primary server. You know you are safe. You’ve gone through the documentation from Microsoft and everything looks sweet, right? I’m afraid to tell you there is a lot more to it than just shipping the logs.

Let’s look at what log shipping does not do. It does not ship any SQL Server Objects that is the SQL Jobs, the SQL Maintenance Plans, the SQL Server Security settings and so on, it only ships the DATABASE.

So to bring your standby server online you need to run through everything that has occurred at the server level that will affect that database.

The Problems that DBAs face with Log Shipping.

There are 2 main problems DBAs face with log shipping.

•1. A standby server needs to be managed, updated, and maintained; so you need to allow time for this and you need to plan for the cost of replacing the standby server as the equipment gets old.

•2. The process of failover is not automatic. You may even want to try restoring the tail of the log before you bring it online therefore reducing data lose to nothing.

We can overcome a lot of these issues by restoring master and MSDB on the Standby Server, but wait, what if you are using the standby server for to protect other SQL servers or other databases? It all starts getting very tricky. Consider this, SQL server logins will need to be synchronized from the primary server to the standby server.

I love log shipping for what it can do, but with the advent of Virtual Machines, I have been looking at how to restore the entire server or critical parts to virtual.

If you break your SQL Server into parts, you can see how a virtual image of the server can work very easily for you. The server hardware usually includes the system and application drives since the data and log drives are normally on a SAN. So if my server breaks, I need to identify if it’s just the system and application drives that I need to get back up and running or if it’s the SAN for my data and logs, or if it’s both. (Of course if you are a smaller, non SAN, shop, the data is local so it really is the restoring of both areas at one time.)

In these times where companies are trying to reduce infrastructure costs and postpone the purchase of new equipment, we as DBAs need to start looking into newer technologies to resolve our protection needs. We need to be more eco-friendly by using less electricity and having a smaller foot print on the environment. One way I have found is using AppAssure’s Replay AppImage for Disaster Recovery. It has the ability to restore from its backup images either to a Virtual Machine or to new hardware in minutes. It also has the unique ability to continuously restore to a Virtual Machine that can be powered on when required. So do yourself and the environment a favour and visit there website