SQL 2005: should I roll my own log shipping?

Question

I'm looking into using log shipping for disaster recovery and I'm getting mixed messages about whether to use the built-in stuff or roll my own. Which do you recommend, please, and if you favour rolling your own what's wrong with the built-in stuff? If I'm going to reinvent the wheel I don't want to make the same mistakes! (We have the Workgroup edition.) Thanks in advance.

Answer 1

If you decide to roll your own, here's a nice guide.

I'm assuming you're going this route because Enterprise Edition is so costly?

If you don't need a "live-backup", but really just want a frequently updated backup, I think this approach makes a lot of sense.

---

One more thing:

Make sure you regularly verify that your backup strategy is working.

Answer 2

I'm pretty sure it's available in Standard, since we're doing some shipping, but I'm not sure about the Workgroup edition - it's pretty stripped down.

I'm always in favor of the packages solution, but mostly because I trust a whole team of MSFT developers more than I trust myself, but that comes with a price for sure. I'd second that any solution you roll on your own has to come with a lag notification piece so that you'll know immediately if it isn't working - how many times do we only find out backup solutions aren't working when somebody needs a backup? Also, think honestly about how much time it will take you to design and roll your own solution, including bug fixes and maintenance - can you really do it more cheaply? Maybe you can, but maybe not.

Also, one problem we ran into with Workgroup edition is that it only supports 5 connections at once, and it seems to start dropping connections if you get more users than that, so we had to upgrade to Standard. We were getting ASP.NET errors that our connections were closed if we left them unattended for even a few seconds, which caused us all kinds of problems.

Answer 3

I would expect this to be close to the last place you'd want to save a few bucks, especially given the likely consequences if you screw up. Would you rather have your job on the line? I don't even think I'd admit it, if I felt I had a chance of getting this one right?

What's your personal upside benefit in this?

Answer 4

Have you considered mirroring instead? Here is some documentation to determine if you could do that instead

Answer 5

There's really two parts to your question:

1. Is native log shipping good enough?

2. If not, whose log shipping should I use?

Here's my two cents, but like you're already discovering, a lot of this is based on opinions.

About the first question - native log shipping is fine for small implementations - say, 1-2 servers, a handful of databases, and a full time DBA. In environments like this, the native log shipping's lack of monitoring, alerting, and management isn't a problem. If it breaks, you don't sweat bullets because it's relatively easy to repair. When would it break? For example, if someone accidentally deletes the transaction log backup file before it's restored on the disaster recovery server. (Happens all the time with automated processes.)

When you grow beyond a couple of servers, the lack of management automation starts to become a problem. You want better automated email alerting, alerts when the log shipping gets more than X minutes/hours behind, alerts when the file copying is taking too long, easier handling of multiple secondary servers, etc. That's when people turn to alternate solutions.

About the second question - I'll put it this way. I work for Quest Software, the makers of LiteSpeed, a SQL Server backup & recovery product. I regularly talk to database administrators who use our product and other products like Idera SQLSafe and Red Gate SQL Backup to make their backup management easier. We build GUI tools to automate the log shipping process, give you a nice graphical dashboard showing exactly where your bottlenecks are, and help make sure your butt is covered when your primary datacenter goes down. We sell a lot of licenses. :-)

If you roll your own scripts - and you certainly can - you will be completely alone when your datacenter goes down. You won't have a support line to call, you won't have tools to help you, and you won't be able to tell your coworkers, "Open this GUI and click here to fail over." You'll be trying to walk them through T-SQL scripts in the middle of a disaster. Expert DBAs who have a lot of time on their hands sometimes prefer writing their own scripts, and it does give you a lot of control, but you have to make sure you've got enough time to build them and test them before you bank your job on it.

Answer 6

I tried the built-in log shipping and found some real problems with it so I developed my own. I blogged about it here.

PS: And just for the record, you definitely get log shipping in the Workgoup edition. I don't know where this Enterprise-only thing started.