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.