Tuesday, October 25, 2005
This entry is part 1 of a 7 part series. To jump to a particular entry, you can click any of the following links:
Logshipping is not too complicated, but it has it's caveats. I found that documentation on log shipping was about as useful as a screen door on a submarine. Microsoft had their typical documentation (brilliant descriptions of some settings, no reference to others, and absolutely zero documentation about what happens in the background), and I found a couple of blogs that had some suggestions, but nothing that was very useful.
The biggest frustration I had was when the setup of a maintenance plan fails, you may get a ‘setup failed’ message. That’s it. No description of what happened. No ‘click here for more info’ button. No cheshire cat to point you down the right path. Frustrating.
What is Log Shipping?
Log shipping is basically the process of feeding transaction logs from a source database to one or more destination databases on a constant basis. This allows us to have a backup server and can also provide a way to offload query processing (such as reports) from the source server to read-only destination servers.
In my opinion the pros of log shipping outweigh the cons. On the upside log shipping is inexpensive, easy to maintain (once it’s set up), and reliable. Data loss can be kept to a minimum as long as you ship the transactions fairly often. On the downside, the failover is a manual process. So when your production server tanks at 3:00 am, you’re going to have to get out of bed and fix the problem. This also means that there will be some downtime.
Why log shipping over replication?
Of course you’re going to ask this question. I did. The biggest reason for using log shipping was that all objects are copied to the destination site. All stored procedure changes, table structure changes, etc. are automagically shipped to the destination server. Replication does not do this.
In the next section, I'll discuss what you need to plan your log shipping maintenance plan.
Chris Antoniak
DBA/Developer