log shipping what if I need transaction logs backup on my primary database.

Question

log shipping what if I need transaction logs backup on my primary database.

sqlfriend

SSC Guru

Points: 52769
More actions
November 18, 2022 at 7:24 pm
Go to Answer
#4115016

I am trying to understand log shipping. Try to see if it can be used for our disaster recovery purpose on another location. we will only use the second location when main location has a disaster.
I understand we have to stop our regular maintenance plan for transaction log backups otherwise it will be conflict.
Then what if I want to restore some transaction logs for my primary location database for a point time restore, are those transaction logs available somewhere and in the backup chain with full backup and differentials,
I meant the disaster recovery is something rarely happen, but I do need transaction log back up files for my primary regular used database. But apparently maintenance plan cannot do the regular transaction log backups

Thanks,
Ant-Green

SSC Guru

Points: 113485
More actions
December 7, 2022 at 9:08 am
Answer
#4122843

Use the same folder structure you get with your normal backups, it saves re-inventing the wheel for your backup to tape/off site methodology.
For cleanup again, set the log shipping cleanup time to the same time you clean up in Ola's script and you wont have to worry about anything on the cleanup

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1350 More actions · Answer 1

Log Shipping doesn't mean you don't have access to those log backup files. It's just a different agent that manages them.

If you use Ola Hallengren SQL Server Backup, the procedure is smart enough to exclude log-shipped databases from the "normal" log backups.

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

Thanks,

Then where are those transaction log backups from log shipping?

Are they in backup log chain with full backup and differential backup with my regular maintenance backup? Apparently not. Then how can I do a point of time restore on my primary database? What files to use?

Thanks,

ratbak Ten Centuries Points: 1350 More actions · Answer 3

Yes, they are in the log chain -- they use native backup, and are not copy-only. Look in the log-shipping configuration to see where the log backups are stored on/for the primary.

EdVassie SSC Guru Points: 60464 More actions · Answer 4

Having done both log shipping for DR and availability groups for DR, I would always go for availability groups.

Setup, administration and troubleshooting for AGs is much simpler than log shipping, especially on SQL2019. The place I worked at last had a SQL cluster at each of two sites, connected by a distributed availability group (dAG).

To make failover simple and to allow the sites to be used as peers we used a DNS alias to identify the active site and connected all applications via that alias. Failover of the dAG is simple, followed by changing the DNS alias. All done and running again within about 3 minutes.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

sqlfriend SSC Guru Points: 52769 More actions · Answer 5

EdVassie wrote:

Setup, administration and troubleshooting for AGs is much simpler than log shipping, especially on SQL2019. The place I worked at last had a SQL cluster at each of two sites, connected by a distributed availability group (dAG).

Thanks, in the case you mentioned, is there a reason you have to use a dAG with 2 windows clusters instead of using regular AOG having 1 cluster with two nodes, node A in site A, node B in Site B? In our case A and B are in same city, but just different location.

Thanks

EdVassie SSC Guru Points: 60464 More actions · Answer 6

The setup was done with two clusters, to allow patches etc to be applied at each site independantly of the other. One requirement was that failover for maintenance should not cause the primary node to fail over to the second site. One application (SiteCore) in the version initially used would not tolerate its app servers running at a separate site to the DB (latency too long) and failing over SiteCore just because of DB server patching was not wanted.

It would have been possible to set the thing up as a four node cluster, two at each site. Windows clustering now has good facilities for managing multi site clusters and preferring local nodes for failover. For some reason we did not consider this, but in retrospect would have probably rejected it. Using a dAG to link the clusters allows more independence in how they are operated, and lowers the risk that an issue on a remote node could impact the stability of the primary node.

Also when upgrading to a SQL version we found (eventually) the lowest risk approach was to set up a second dAG from the primary to a new cluster hosting the new SQL version. To do cutover we did a dAG failover to the new version, updated our DNS aliases to use it, and then rebuilt resilience to the second site. Leaving the old version frozen meant in theory if we quickly had a show stopper issue we could re-point the DNS aliases back to the old system and keep running (don't mention data repairs to catch up the old with the new). Anyhow, using a dAG proved long term to be low risk and easy to manage.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

sqlfriend SSC Guru Points: 52769 More actions · Answer 7

For transaction log shipping, I still have questions:

we have a primary instance that has 4 databases on it, I only want to do a log shipping for 2 of them.

We also have regular maintenance plan to do full weekend, daily differential and hourly transaction log for the 4 databases in the maintenance plan.

Now I would like to setup transaction log shipping for 2 of the 4 databases. I understand I need to do separate transaction log backups for the 2 databases, So I remove the transaction log backups for the 2 in maintenance plan, but still keep the full and differential in it. In the maintenance plan also have a step to delete files older than…

Now I get to the step of setting transaction log backup, it has a delete files older than ,,,, .

How can I set them without conflicting with the delete steps in Maintenance plan and also works with full backup?

Or should I create a separate maintenance plan for these 2 databases for full and differential and make them into a separate folder from other databases. I want this to work for both transaction log shipping or also want to keep valid backup files for the primary instance in case I want to restore primary databases?

Basically how can I co-ordinate the time in the steps of delete files older than .. with the time for full backup? I hope the transaction log chain is not broken in any way to insure valid backups and restore.

Thanks

Ant-Green SSC Guru Points: 113485 More actions · Answer 8

What is your current log retention period in the maintenance plan? What is your current delete files older than setting? Set the log shipping clean-up older than to be the same as your current maintenance plan value and everything will just behave the same.

Generally you do not change anything in the maintenance plans you had except the databases which are part of the transaction log maintenance piece to remove log shipping databases as the LS process will be the one to handle the backups for you.

Additionally move away from maintenance plans, they are to clunky, they do serve a purpose of a round peg to a square hole and are good as a stop gap solution, but they leave a lot to be desired, I would seriously look other solutions than maintenance plans, like Ola Hallengren's scripts, MinionWare etc

sqlfriend SSC Guru Points: 52769 More actions · Answer 9

Thanks,

Is it a good practice to put the transaction log backups for log shipping to a different folder from other regular non-log shipping database transaction logs backup folder?

2. do we need to do full, differential backups on secondary server as a regular backup?

3. And usually we put monitor instance on primary server or secondary server?

Thanks

Ant-Green SSC Guru Points: 113485 More actions · Answer 10

1 - generally it is best practise to have a folder structure for each database and each type of backup

backups\master\full

backups\master\diff

backups\master\logs

ETC then you know what is what, especially with fulls and diffs as they share the same bak extension.

But it is entirely up to yourself how you structure it as long as you understand what is what and where it is etc.

2 - no you cannot backup a log shipping secondary, it is not in a state that allows backups to happen.

you can only take backups once disaster has struck and you have manually failed yourself over to the secondary and made this primary, then you need to make sure backups happen.

3 - usually some other server if it has been concluded a monitor server is needed.

sqlfriend SSC Guru Points: 52769 More actions · Answer 11

Thank you.

For question 1 above, I have changed all my maintenance plan to OLA maintenance solutions.

I understand the backup folder structure just like what you described above, this is for the regular non log shipping databases, and also for log shipping databases full and differential backup locations.

How about transaction log backup for log shipping databases, should I create a different folder just for log shipping transaction log backups, since they are handled by different process, and also because both jobs have delete files settings, so that they don't delete twice.

Thanks,