Log shippng confguration not impact on routing log backup

Question

Log shippng confguration not impact on routing log backup

Ashwan 536

Hi I have SQL server 2014 STD version and need to configure Log shipping read only access for live reporting . database is on FULL recovery and already confure daily full backup and hourly log backup from 3rd party software. I understand when we do a Log backup through 3rd party , how we configure then log form Log shipping which will fail to recovery. how do we confgure log shipping and we cann't stop log backup for 3rd party software which run for other database as well . what is the best practice to confgure both independely with out impacting eath other .

Log shipping: business is required to behing the production 15 min.

Any advice please.

thanks

Cris Zhan-MSFT 6,661 Reputation points

2020-11-06T02:30:34.51+00:00

Hi @Ashwan ,
Is there any update on this case?
If the answer is helpful, please click "Accept Answer" and upvote it.

1 answer

Your answer

Cris Zhan-MSFT 6,661 Reputation points

2020-11-06T02:30:34.51+00:00

Hi @Ashwan ,
Is there any update on this case?
If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 1

Shashank Singh 6,251

how do we confgure log shipping and we cann't stop log backup for 3rd party software which run for other database as well . what is the best practice to confgure both independely with out impacting eath other .

In this scenario where some TP tool is already taking transaction log backup, you may configure Los Shipping for sake of doing it but the restore jobs will start failing as log backups taken by TP tool cannot be applied on secondary database, since log backups have to be restored such that log chain is not broken. So in end you would not have successful LS configuration. Unfortunately there is no workaround.

You can create manual LS using scripts and use copy_only full backup and copy_only log backup but that would not make much sense.

You can ask backup team to take cop_only log backups, can they do that ?. If so then you have no issues configuring transaction log shipping on the database.,

Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2020-11-01T10:21:40.673+00:00

You can ask backup team to take cop_only log backups, can they do that ?. If so then you have no issues configuring transaction log shipping on the database.,

This does not sound like a good idea to me. Those COPY_ONLY log backups would be of no use, would they? Each such log backup would only include a portion of the log since the most recent log backup taken by the log-shipping job. So these would log backups could not be used for a point-in-time restore. Or am I missing something?

The problem is the same if we set up the log-shipping backup to use COPY_ONLY backups and keep the current backup job unaffected. After at most one hour there will be a gap in the log chain, because the backup job has pulled the carpet for the log-shipping job.

Somehow these jobs need to be synchronised. The easiest seems to be if the backup job runs every 15 minutes, and a second job just picks up and restore those logs without taking any backups of its own.
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2020-11-01T10:22:17.137+00:00

Addendum that did not fit into the previous comment due to the 1000-char limit:

If the frequency of one hour cannot be changed, the log-shipping backup would first have to look if there is log backup taken by the job, and in such case send that log to the other server, and only if there is not take a COPY_ONLY backup. Sounds a bit complicated.
Shashank Singh 6,251 Reputation points

2020-11-02T09:24:51.687+00:00

@Erland Sommarskog The easiest seems to be if the backup job runs every 15 minutes, and a second job just picks up and restore those logs without taking any backups of its own.>> That is again difficult I believe as Third party tools normally may backup at somewhere different location and LS might not get permission there. Also I agree there is hardly any point in copy_only backups in tool if you have full fledged LS already running. The ultimate use of backup is that it can be used for restore, if that is not possible there is hardly any point in taking it.
Ashwan 536 Reputation points

2020-11-02T23:54:07.563+00:00

Hi

Senario proppsed:
TP tool - "COPY only" log backup as global policy
Log shipping: organize log backup some location can continue log apply backup .
during log apply time , all login users cut off.

discussion:
TP tool with "COPY" only log backup I dont think make sence as unable to achive the global purpose of database point in time recovery in case business is needed.

getting seperate log backup for Log shipping.

But this way is not a best practise for us to follow in the company (as readonly replica database realtime). I dont know why Microsoft has this limitation as very poor way of archteturing replication solutions.

any advice please
Shashank Singh 6,251 Reputation points

2020-11-03T06:22:38.447+00:00

What limitation your are talking about ? You care going too optimistic with tools and features. Every feature has limitation and advantages, if you want to use it it has to be done in way it is documented. If you are using TP tool to backup log for the database you would not be able to use LS and vice versa.
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2020-11-03T22:22:18.793+00:00

But this way is not a best practise for us to follow in the company (as readonly replica database realtime). I dont know why Microsoft has this limitation as very poor way of archteturing replication solutions.

I echo Shashank: what limitation? Let's say that we actually would like to do this: have one set of log backups for disaster recovery and one set for log shipping, and both sets should have the full log stream. Furthermore, it should be possible to take the log backups in any order. A possible solution is that we would be able to define log streams for our database, and when we take a log backup, we need to specify which stream we are taken backup for, and the log cannot be truncated past a point that at least stream have not backed up.

I think you are asking for a new feature. (And I would not really hold my breath for it.)

Have you considered Transactional Replication?
Ashwan 536 Reputation points

2020-11-13T00:35:43.573+00:00

Hi Shashank, Erland, Thank you for the commnets . How ever Erland sounds great if we can do both as 1 . TP tool log cannot be truncated past a point .
2. LS backup the log and truncate the log.
Thats is a great feature if we can do that.

regards
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2020-11-13T10:31:21.06+00:00

Well, that depends on your third-party software. Shashank was not optimistic about that, and myself I have no experience of them.

Share via

Log shippng confguration not impact on routing log backup

1 answer

Your answer