I am in need of advice on setting up a remote read-only MSSQL 2008 reporting server using transaction logs.
From what I gather, true TLS setup requires both database servers to be aware of each other however in my case the server where the logs will be applied is in no way connected to the primary server.
I am using the following steps to replicate data to the reporting server approximately every 15 minutes.
- Download incremental or full backup file posted to a SFTP server
- Put database into single user mode
- Apply backup (with replace, standby)
- Put database into multi user mode
The problem I am having is the database is unavailable to applications during the period when the database is in single user mode. Is there a better solution for what I am attempting to accomplish where the database wouldn't need to refuse connections, or could queue connections until the backup has been applied?
Thanks!
Best Answer
Is there a better solution for what I am attempting to accomplish where the database wouldn't need to refuse connections, or could queue connections until the backup has been applied?
You need to write some manual code and change backups .Please see below article which we are also applying from Aaron bertrand
http://sqlperformance.com/2014/10/sql-performance/readable-secondaries-on-a-budget