• State Suggested Answer
  • Replies 1 reply
  • Answers 1 answer
  • Subscribers 13 subscribers
  • Views 1889 views
  • Users 0 members are here
Options
Related

Looking for a solution to enable database continuity across/during LiteSpeed restore process.

nilson cain
over 3 years ago

I'm looking for a solution to enable my applications to remain connected to my SQL Server database even during transaction log restores with LiteSpeed.

The scenario:

  • We consume log shipping from our production database via LiteSpeed in an automated process to keep our local readonly copy of that data in sync. This occurs every 30 minutes. We do not have direct access to the production database other than the log shipping we are provided. (This is a SaaS scenario where we maintain a local copy of the application database for reporting and other purposes).
  • The log restore process, as part of the instructions provided by the SaaS provider, include a script that kicks all users off the database server, and then goes into Single-User mode and executes master.dbo.xp_restore_log to perform the restore, then brings the database back online.

Under the assumption (welcome to correction if not the case) that the transaction log restoration process invariably requires the database go offline, I had thought of this sort of concept:

  • Maintain two concurrent copies of our local copy of the database (receiving the log shipping) which do their log restores at offset times, ensuring that one database is always online and accepting connections.
  • Have some sort of transparent front end that will route DB connections/queries to the online database. I don't know if this would come in the form of a load balancer, or some sort of HA failover scheme. I did read that Microsoft's failover clustering is dependent on a shared database file store, and so would seem to be unsuitable for our needs in this situation.

I am not concerned with any concurrency issues, or the data not being 100% consistent between the two database instances due to offset log shipping restore times. This is a read only database and maintaining uninterrupted connections to the data is our goal.

Looking for any input as to how to achieve this goal, and/or if the solution I conceptualized is even on the right path.

Parents
  • 0 over 3 years ago

    Hi Cain I Hope you are doing great.

    Please note Log shipping is a patented MS SQL Server Technology. Litespeed Improves Log Shipping capacity delivering high compression Level, An User-Friendly Interfaces, Log shipping reverse roles and some other Cool Features But in this Particular Scenario There are only two  possible options for configuration when you place the secondary database in standby mode:

    1) You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will be disconnected from the database each time the log shipping restore job attempts to restore a transaction log to the secondary database. Disconnection will happen on the schedule you set for the restore job.

    2) You can choose not to disconnect users. In this case, the restore job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database.

    So yes, it could be hours, days, weeks, or years without a restore if users are still connected to the secondary.


    (This is how works SQL Server by Design)

    I could suggest you Always ON (availability Groups) which is supported by Litespeed and it seems to match with your use case  (see link below).

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15

    I hope this helps.

    Thank  you

    NC

Reply
  • 0 over 3 years ago

    Hi Cain I Hope you are doing great.

    Please note Log shipping is a patented MS SQL Server Technology. Litespeed Improves Log Shipping capacity delivering high compression Level, An User-Friendly Interfaces, Log shipping reverse roles and some other Cool Features But in this Particular Scenario There are only two  possible options for configuration when you place the secondary database in standby mode:

    1) You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will be disconnected from the database each time the log shipping restore job attempts to restore a transaction log to the secondary database. Disconnection will happen on the schedule you set for the restore job.

    2) You can choose not to disconnect users. In this case, the restore job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database.

    So yes, it could be hours, days, weeks, or years without a restore if users are still connected to the secondary.


    (This is how works SQL Server by Design)

    I could suggest you Always ON (availability Groups) which is supported by Litespeed and it seems to match with your use case  (see link below).

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15

    I hope this helps.

    Thank  you

    NC

Children
No Data