Hi,
I am testing with Log shipping, I have it setup and it is working just fine. The secondary database was restored with Standby mode and is readonly. I have no problem accessing and querying data from the secondary database but I noticed if changes are made to the primary and I am accessing the database when the restore step to the secondary runs it fails with the following error:
Message
2006-07-31 09:40:54.33 *** Error: Could not apply log backup file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LogShip\CI_REPLICATION_TEST_20060731131501.trn' to secondary database 'CI_REPLICATION_TEST'.(Microsoft.SqlServer.Management.LogShipping) ***
2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
This appears to be because you can not restore to a database that is in use? How do you get around this? Is it possible? How do I query data from the secondary db without worrying about causing log shipping to fail? Do I not worry about it because at somepoint when Exclusive access is obtainable all the logs will be rolled forward, but this could create a wide gap in sychronization between the primary and secondary db's, say if someone or something does not properly log off. I guess I could disconnect all users prior to applying tlogs but that kind of defeats the purpse of having it in read only mode so that data can be accessed? Couldn't really depend on it as a stable source of data for running data extracts? I suppose it could all just come down to timing everything in the appropriate sequence? Anyway.....
To sum up:
Is there anyway to access the standby server without causing the restore process to fail?
Both the Primary and Secondary are SQL 2K5.
Thanks!
Quick answer? No.
To load the t-logs, SQL Server needs exclusive access to the DB. So:
a) you allow read only access to continue, which can potentially block the t-log load
b) allow the t-log load, knowing that users will not be able to access the DB while the t-log is loading
c) kill the connections to the DB when the t-log is about to load, effectively creating b), but making unhappy end users
d) use another reporting solution
Log shipping has always been meant for creating a standby. While you can use it for read only access, it is not the best use of log shipping, and never has been. I have never ever recommended this configuration. YOu are better off setting up another way to do reporting and leave log shipping alone.
This is just the way the SQL engine works.
|||Thanks!
Your basicly confirming, I guess, what I already knew. Just wanted to make sure I understood correctly.
