Offline Reporting DB...

A customer of ours was considering replication as a means of maintaining
data in a second DB on a second server for reporting use only. We think
replication is overkill since they won't mind if the data is a day or two
old.
I like the idea of simply restoring their most recent full backup on the
reporting server, but I'm not sure if they do a full backup every night.
What are other approaches that folks use to accomplish this sort of thing?
Log shipping doesn't seem right; replication seems like overkill. DTS
possibly? Any thoughts, links, or words will be greatly appreciated!
Thanks in advance for your time,
James Hunter RossWithout details about the database size, your connection bandwidth, etc.,
it's hard to say what is the best way for your needs.
If the customer can tolerate data two days old, you can use snapshot
replication and set the snapshot interval as 48 hours. Yuo can also make use
of log shipping secondary server with restored databases being in read-only
mode. Then users can run reports off the databases from time to time. The
inconvenience with log shipping is to regularly kick users out of the
database so that restore of transaction logs can go on. The third option
will be increase full backup frequency so that there is a full backup every
two days, otherwise you will have to restore from a full backup more than 2
days ago and apply all subsequent transaction logs.
Other things you can consider: will copy database wizard apply? It requires
the database being shut down.
Richard
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OYhK%23KdcFHA.3828@.tk2msftngp13.phx.gbl...
>A customer of ours was considering replication as a means of maintaining
>data in a second DB on a second server for reporting use only. We think
>replication is overkill since they won't mind if the data is a day or two
>old.
> I like the idea of simply restoring their most recent full backup on the
> reporting server, but I'm not sure if they do a full backup every night.
> What are other approaches that folks use to accomplish this sort of thing?
> Log shipping doesn't seem right; replication seems like overkill. DTS
> possibly? Any thoughts, links, or words will be greatly appreciated!
> Thanks in advance for your time,
> James Hunter Ross
>|||Keep it simple, if possible. Backup restore is as simple as it gets, and you get your backups tested
each time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OYhK%23KdcFHA.3828@.tk2msftngp13.phx.gbl...
>A customer of ours was considering replication as a means of maintaining data in a second DB on a
>second server for reporting use only. We think replication is overkill since they won't mind if
>the data is a day or two old.
> I like the idea of simply restoring their most recent full backup on the reporting server, but I'm
> not sure if they do a full backup every night.
> What are other approaches that folks use to accomplish this sort of thing? Log shipping doesn't
> seem right; replication seems like overkill. DTS possibly? Any thoughts, links, or words will be
> greatly appreciated!
> Thanks in advance for your time,
> James Hunter Ross
>|||We use log shipping for this as our main DB is too large to restore nightly.
Works well.
Mike Kruchten
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OYhK%23KdcFHA.3828@.tk2msftngp13.phx.gbl...
>A customer of ours was considering replication as a means of maintaining
>data in a second DB on a second server for reporting use only. We think
>replication is overkill since they won't mind if the data is a day or two
>old.
> I like the idea of simply restoring their most recent full backup on the
> reporting server, but I'm not sure if they do a full backup every night.
> What are other approaches that folks use to accomplish this sort of thing?
> Log shipping doesn't seem right; replication seems like overkill. DTS
> possibly? Any thoughts, links, or words will be greatly appreciated!
> Thanks in advance for your time,
> James Hunter Ross
>