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

  • 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,

     

  • 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.

  • 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,

  • 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.

  • 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

  • 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

  • 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

Viewing 7 posts - 1 through 6 (of 6 total)

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