Adding a Far DR secondary server Log shipping on already configured SQL server

Question

We have a setup where there are 3 SQL Server instances in which 1 primary,1 Near Secondary,1 Far Secondary.

So the there is already log shipping configured between Primary and Near DR Secondary.

Now i have to configure/Add log shipping between the Primary and the Far DR Secondary.

Need to add 1 database in the log shipping size is 1.1 TB

The issue is copying a full backup might take more than 12 hours from primary to really far DR and there is 4 schedules of daily full backup on Primary each of 6 hours interval.

Kindly help with a plan so that i can configure the log shipping on the Far DR without any delay and one more thing we cannot stop the existing log shipping config.

Answer 1

The issue is copying a full backup might take more than 12 hours from primary to really far DR and there is 4 schedules of daily full backup on Primary each of 6 hours interval.

... configure the log shipping on the Far DR without any delay ...

These two are competing items. If it takes 12 hours to transfer and you must transfer full backups every time then I don't see how that will fit in a 6 hour window, let alone be able to do anything of use with it.

You'll either need to push back on doing this or ask for better bandwidth.

If, however, you don't have to send the same full backup each time, then a one time full with logs between may be possible depending on the rate of change in the database. Obviously this all depends on what the requirements say.

[comment]...consider using mirroring or AlwaysOn High Availability Groups instead.

Potentially, depending upon the requirements (which we currently don't have or know), although this could lead to other undesirable consequences if the latency is very high. Availability groups do not like latency.

Answer 2

You don't say why you are doing full backups every 6 hours.

1. Assuming that you actually need to do this. Make your backups locally, then copy the backup to your Far Secondary.

2. Re-evaluate why you are taking full backups every 6 hours. Unless you have some VERY special needs you probably don't need this. Full backups once a day or once a week combined with t-log and/or differential backups are going to be less resource intensive.

You should only need to send the full backup to your Far Secondary once, then ship the logs and they should stay as synced as time lag allows. The start up time is only painful when you start or restart log shipping, hopefully this is once per year or less.

Answer 3

I tried doing the backup restore as soon as it got copied (after 19 hours) then i proceeded with the standard procedure of configuring the Log Shipping on primary and i had ran the LS_Copy job which took a while to copy some 300 log files and then LS_Restore job took it with grace and brought the DR in sync.

I didn't stopped the Existing Log Shipping nor the Daily full backup job just made sure the log files didn't get deleted so i increased the LS_Backup log file retention to 3 days and done the Standard procedure.

Thanks everyone.