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
JonoHi 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

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

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
JonoHi 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

Log shipping and database snapshots

SQL Server 2005
Can a database snapshot be created on a log ship target standby database so
that read-only reports and/or datamarts be ran' I know this is fully
supported when using Database Mirroring but Microsoft isn't supporting this
in production environments yet Didn't know what the limitations were with a
database in standby and snapshots.
Thanks"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:u1gEwo3YGHA.1348@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005
> Can a database snapshot be created on a log ship target standby database
so
> that read-only reports and/or datamarts be ran'
Sort of.
If you use RESTORE with STANDBY (check BOL for exact syntax) you can turn
the DB into a read-only mode.
Then later logs can be applied to it.
However, note that when those logs apply there can't be any users in the
database or else they will fail to be restored.
In addition, while they are being restored, you won't be able to read from
the database.
These limitations may or may not be a problem. A typical scenario is to do
something like from 5:00 PM -9:00 AM apply log files as normal. At 9:00 AM
stop applying log files and allow reports to be run. At 5:00 PM kick all
users from the database and start applying logs again.

> I know this is fully
> supported when using Database Mirroring but Microsoft isn't supporting
this
> in production environments yet Didn't know what the limitations were with
a
> database in standby and snapshots.
> Thanks
>|||A Database Snapshot can be created against a Mirror or against a source
database. It can NOT be created against a database that is the target for
Log Shipping. The database has to either be online and accessible or in a
mirroring role to have a Database Snapshot created against it.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:u1gEwo3YGHA.1348@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005
> Can a database snapshot be created on a log ship target standby database
> so that read-only reports and/or datamarts be ran' I know this is
> fully supported when using Database Mirroring but Microsoft isn't
> supporting this in production environments yet Didn't know what the
> limitations were with a database in standby and snapshots.
> Thanks
>

Log shipping and database snapshots

SQL Server 2005
Can a database snapshot be created on a log ship target standby database so
that read-only reports and/or datamarts be ran' I know this is fully
supported when using Database Mirroring but Microsoft isn't supporting this
in production environments yet Didn't know what the limitations were with a
database in standby and snapshots.
Thanks"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:u1gEwo3YGHA.1348@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005
> Can a database snapshot be created on a log ship target standby database
so
> that read-only reports and/or datamarts be ran'
Sort of.
If you use RESTORE with STANDBY (check BOL for exact syntax) you can turn
the DB into a read-only mode.
Then later logs can be applied to it.
However, note that when those logs apply there can't be any users in the
database or else they will fail to be restored.
In addition, while they are being restored, you won't be able to read from
the database.
These limitations may or may not be a problem. A typical scenario is to do
something like from 5:00 PM -9:00 AM apply log files as normal. At 9:00 AM
stop applying log files and allow reports to be run. At 5:00 PM kick all
users from the database and start applying logs again.
> I know this is fully
> supported when using Database Mirroring but Microsoft isn't supporting
this
> in production environments yet Didn't know what the limitations were with
a
> database in standby and snapshots.
> Thanks
>|||A Database Snapshot can be created against a Mirror or against a source
database. It can NOT be created against a database that is the target for
Log Shipping. The database has to either be online and accessible or in a
mirroring role to have a Database Snapshot created against it.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:u1gEwo3YGHA.1348@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005
> Can a database snapshot be created on a log ship target standby database
> so that read-only reports and/or datamarts be ran' I know this is
> fully supported when using Database Mirroring but Microsoft isn't
> supporting this in production environments yet Didn't know what the
> limitations were with a database in standby and snapshots.
> Thanks
>