SQL 2005: should I load my own log shipping?

Question

I study the use of disaster recovery logs, and I get mixed messages about whether to use the built-in stuff or collapse my own. What do you recommend, please, and if you approve of advertising yours, what is wrong with the embedded material? If I am going to reinvent the wheel, I do not want to make the same mistakes! (We have a release of Workgroup.) Thanks in advance.

Answer 1

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

PS: And just for the record, you definitely get magazine delivery in the Workgoup editor. I do not know where this venture has just begun.

Answer 2

There are really two parts to your question:

• Is enough log loading enough?

• If not, whose log transport should be used?

Here are my two cents, but as you already discover, many of them are based on opinions.

About the first question - delivering your own logs is great for small implementations - say, 1-2 servers, several databases and a full-time DBA. In environments such as lack of trap, lack of control, warning and control is not a problem. If it breaks, you do not sweat bullets, because it is relatively easy to repair. When will it break? For example, if someone accidentally deletes a transaction log backup file before it is restored to the disaster recovery server. (It happens all the time with automated processes.)

When you go beyond a couple of servers, the lack of management automation becomes a problem. You want more automated email alerts, notifications when log delivery exceeds X minutes / hours, warns when copying files takes too much time, simplifies the processing of multiple secondary servers, etc. This means that people are turning to alternative solutions.

On the second question - I will say this. I work for Quest Software, the creators of LiteSpeed, a SQL Server backup and recovery product. I regularly communicate with database administrators who use our product and other products such as Idera SQLSafe and Red Gate SQL Backup to simplify backup management. We create GUI tools to automate the log shipping process, provide you with a nice graphical panel showing exactly where your bottlenecks are, and help make sure your applications are covered when your primary data center is down. We sell a lot of licenses. :-)

If you flip your own scripts - and of course you can - you will be completely alone when your data center goes down. You will not have a support line for calling, you will not have tools that will help you, and you will not be able to tell your colleagues: "Open this graphical interface and click here to drop off." You will try to get them through T-SQL scripts in the middle of a disaster. Expert database administrators, who have a lot of time on their own, sometimes prefer to write their own scripts, and this gives you a lot of control, but you need to make sure that you have enough time to create and test them before you take the job On him.

Answer 3

Have you considered mirroring? Here are some documentation to determine if you can do this instead

Answer 4

If you decide to knock over your own, here is a great reference .

I assume you are taking this route because the Enterprise Edition is so expensive?

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

---

One more thing:

Make sure you regularly check to see if your backup strategy works.

Answer 5

I am sure that it is available in the standard, because we do some delivery, but I'm not sure about the release of Workgroup - it is pretty much cut back.

I always support the solution of packages, but mainly because I trust the entire MSFT development team more than I trust myself, but this is connected with the price. I would prefer that any decision you make on your own should appear with a delay notification so that you immediately know if it works . How many times do we find only a backup solution does not work when someone needs a backup? Also, think honestly about how much time you will need to develop and promote your own solution, including bug fixes and maintenance - can you really make it cheaper? Maybe you can, but maybe not.

In addition, one problem that we encountered in the Workgroup edition is that it only supports 5 connections at a time, and it seems that it starts to drop connections if you get more users than this, so we had to switch to the standard . We received ASP.NET errors that our connections were closed if we left them unattended for several seconds, which caused us all kinds of problems.

Answer 6

I would expect this to be close to the last place you want to save a few dollars, especially considering the likely consequences if you ruin. Would you prefer your work on the line? I don’t even think that I would admit it if I felt that I have a chance to get this right?

What is your personal advantage in this?