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
>

ODBC SQL Server database reconnect questions

I have an ODBC 3.0 Application on Windows NT Server maintaining a
connection to a SQL Server 2000 database on the same machine. When an
error occurs, I'd like to be able to determine whether the error is
serious enough that the database connection has been lost, so I can
have the application try to reconnect automatically. My understanding
is that any error of severity 20 or above includes a broken connection,
but I'm having difficulty retrieving the error severity through ODBC.
So my first question is, how do I retrieve the error severity through
ODBC?

Also, as a test, I stopped the database while my application is still
running, and the first error I received had a native error code of 55.
There is no 55 error in the sysmessages table, so I'm confused as to
what the severity of the error is. Can anyone shed some light on why I
would be receiving an error code that is not in the sysmessages table,
and where I might be able to find more information about the error?

Thanks in advance,
AbramI'm not at all familiar with ODBC, however if there's a fatal error
which breaks the connection, it seems unlikely that the server will be
able to inform the client, so I don't see how you could guarantee that
you'll get the MSSQL severity level in a client application (although
it should be logged on the server).

I think you should probably have a look at whatever ODBC errors might
be raised in this situation, although as I said I don't know much about
ODBC, so I may be on the wrong track. In any case, it might be worth
asking the question in an ODBC group as well - handling unexpected
disconnections from a remote data source is probably not specific to
MSSQL.

As for error 55, it's probably Windows error 55 - try typing this at a
command prompt:

NET HELPMSG 55

Simon|||Thanks Simon. I've cross-posted on microsoft.public.data.odbc as well.
I think you're right about error 55. Thanks again!

Abram