-1

I am reading the book 'Pro SQL Server 2019 Administration' from Carter, where in the Log shipping section he mentions:

"Unlike other high availability and disaster recovery technologies, an amount of administrative effort is associated with failing over log shipping. To fail over log shipping, you must back up the tail end of the transaction log, and copy it, along with any other uncopied backup files, to the secondary server."

He previously specified that log shipping is mostly used in DR scenarios... My question is, if DR hit the primary data center, how is one supposed to "up the tail end of the transaction log, and copy it, along with any other uncopied backup files"?!

If DR hits, and we have set up log shipping, my guess is that we are only left with whatever the secondary replicas have... That is, if I am able to 'backup the tail' then the server must be up and running...

1

If DR hits, and we have set up log shipping, my guess is that we are only left with whatever the secondary replicas have... That is, if I am able to 'backup the tail' then the server must be up and running...

When logs are restored onto the secondary, the database is being left in "restoring" (or standby as read only) state by restoring logs "with norecovery" option:

RESTORE LOG [YourDB] FROM DISK = 'R:\YourDBLogBackup.TRN' WITH NORECOVERY

If primary is not available

You can always bring it online by running "recovery" command:

RESTORE DATABASE [YourDB] WITH RECOVERY

So if you lose primary, the secondary (DR) will be as up to date as the latest log copied across. However, when you do that manually, you are not going to be able to revert back to primary so only do it when the primary is lost.

EDIT:

Actually, I am going to expand on this. Log shipping can be as good as any other DR method but it will depend on your RTO (Recovery Time Objective) and RPO (Recovery Point Objective) what method you use.

If you care about every single transaction you will have go with Availability Group Synchronous commit which essentially writes the data first into the DR then into primary.

If, however, you can afford to lose few minutes or even 1 hour worth of data Log Shipping will probbaly be enough. Just make sure you are backing up your log often enough.

The beauty of Log Shipping is that you could even do it via FTP. SQL Servers do not need to see each other for this to work.

EDIT 2:

If primary is available

The tail backup is only required to do manual failover if Primary datacenter is not lost. You do a tail backup on primary which puts the database into "restoring" mode:

BACKUP LOG [YourDB] TO 'R:\YourDBLogBackup.TRN' WITH NORECOVERY;

You then copy that very last backup file (hence its called tail) and restore in secondary, Bring secondary online and repeat the operation to fail back to primary. You can do it back and forth (fail over and fail back) as many times as you want.

0

I suspect he's talking about doing the failover manually.

If, as you say, your Data Centre gets totalled, then you're not going to be able to get anything back from your old, Primary server. It's toast.

That's probably why he leads with:

Unlike other high availability and disaster recovery technologies ...

These other technologies ship logs around much faster so that there's no "trailing edge" to worry about.

1
  • "you must back up the tail end of the transaction log" where is this? if its in the primary can't be accesed, if it's in the secondary, then its already there, why backup something that's already there? Oct 20 '20 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.