Log Shipping question for the guru's.

Hi All
I want to create a read-only database for reporting
purposes (takes load off the primary SQL server).
If I select the database to be in "standy mode"
with "terminate users" selected, then each time a tx-log
restores to that server (every 5 minutes), the users
get...well terminated (kicked off). If I don't use
the "terminate users" option, then the transaction log
restore fails (a transaction log restore requires
exclusive access to the database and will not restore if
users are in there).
Therefore, how is it possible to have a log shipped
database (synched to5 min) that can be read only ?
(Is that what the "No Recovery" mode is for?)
Thanks
Jono
Hi Jono
Short answer is that Log Shipping isn't the right solution if you really
want a read-only copy that's always accessible for uninterrupted reporting.
Replication is the way to go if that's your requirement.
Recovery is something SQL Server does to bring a database back to a
consistent state upon start-up. So, when SQL Server starts up, it looks
through the transaction log for each database for transactions that are
either incomplete (no commit / rollback recorded, in which case are rolled
back) or completed in as far as a commit or rollback is recorded, but the
transactions are not "stored" in the database. These transactions (completed
in the transaction log, but not in the database) are "recovered" by being
run again. NoRecovery is a state in which the database is not put through
this process at startup. It's also the required state a database must be in
to have another transaction log restored & hence why it's important to Log
Shipping.
Regards,
Greg Linwood
SQL Server MVP
"Jono" <anonymous@.discussions.microsoft.com> wrote in message
news:7ef601c4d126$9d83cec0$a401280a@.phx.gbl...
> Hi All
> I want to create a read-only database for reporting
> purposes (takes load off the primary SQL server).
> If I select the database to be in "standy mode"
> with "terminate users" selected, then each time a tx-log
> restores to that server (every 5 minutes), the users
> get...well terminated (kicked off). If I don't use
> the "terminate users" option, then the transaction log
> restore fails (a transaction log restore requires
> exclusive access to the database and will not restore if
> users are in there).
> Therefore, how is it possible to have a log shipped
> database (synched to5 min) that can be read only ?
> (Is that what the "No Recovery" mode is for?)
> Thanks
> Jono
sql