Log Shipping: Questions RE setup and access

samjones3

Greetings!

I will be setting up a log shipping environment soon. SQL Backup looks great!

I have a few questions related to SQL Backup and log shipping:

a) Does it work with Express Edition of SQL 2005 and 2008 ?

b) Can I do one-to-many? E.g. log ship to a warm spare DB server, and to our analytics DB server?

c) How many copies of SQL Backup do I need? (One per server? One per "source" server? or ?)

d) Exactly what visibility is needed across the two servers? (Our servers are in different data centers across the country... I really need to know!)

e) Is a network share required?

f) Can I use ftp or Amazon S3 instead of a network share?
(S3 would be smokin!)

Thanks!!!

Eddie D

Thank you for your post into the forum.

I have the following answers to questions.

a) Does it work with Express Edition of SQL 2005 and 2008 ?

The one problem you have with SQL 2005 / 2008 Express Editions is that you do not have a SQL Server Agent.

When you create a scheduled backup job or use the Log Shipping Wizard, SQL Backup relies upon the SQL Server Agent to trigger the call to the SQL Backup Extended Stored Procedure.

So if you wish to log ship using SQL 2005 / 2008 Express Editions, you cannot make use of the Log Shipping Wizard. You will have make use of the SQL Backup Backup Command Line Interface (SQLBackupC.exe), script out the task into a batch file and use the Windows Scheduler to trigger the task. Also you will have to perform a full Backup of the primary server and restore the full backup on the secondary manually before running the batch files to perform the transaction log backup / restore jobs.

Another gotcha, is that you cannot log ship from SQL 2008 server to SQL 2005 server.

b) Can I do one-to-many? E.g. log ship to a warm spare DB server, and to our analytics DB server?

The Answer is yes, but you will need to modify the backup job to add another COPYTO argument to copy the backup file created to a second network share.

c) How many copies of SQL Backup do I need? (One per server? One per "source" server? or ?)

SQL Backup is licensed per physical server, as you wish to log ship from one primary server to two secondary servers you will require 3 licenses of SQL Backup.

e) Is a network share required?

Ideally the answer is yes, as it make your life easier from an administrative point of view.

I recommend that you place the network share on the LAN where the secondary server is located. Even a shared folder on the secondary server itself.

f) Can I use ftp or Amazon S3 instead of a network share?

Yes you can use ftp to copy the backup file from the primary server to the secondary server. We have a ftp utility that we can make available to you if you wish to use it. The utility replaces the COPYTO argument in your backup script.

Unfortunately we have no experience of using Internet Storage like Amazon S3 for Log Shipping. Therefore I cannot comment on this type storage and its suitablility. If you use to use Amazon S3 I would throughly test it out before placing into a production environment.

I hope the above answers your questions.

Many Thanks
Eddie