Reporting DB as copy of Transaction DB

Reporting DB as copy of Transaction DB

Post by Rob » Thu, 02 Aug 2001 23:21:25



I want to create a reporting DB from a
transaction DB.  I want to add objects
views, indexes, etc to the reporting DB
for performance enhancements during
queries. This is where all non-real time
reporting requests would be pointed.
Weekly, Monthly, Quarterly, Yearly reports
run only against the reporting DB.

Let's say transaction DB is 2 GB.

I want no more than 1 days latiency(sp?) in
the data from trans DB to reporting DB.

I looked into Warm Standby - applying tlog
as necessary - problem; DB is read only I
could not add objects.

Any ideas, experiences?
Thanks,
Rob

 
 
 

Reporting DB as copy of Transaction DB

Post by Carl Stone » Fri, 03 Aug 2001 00:12:22


Use DTS and after the package is created and copies all the tables to your
reporting DB right click the package and schedule to run as often as
necessary.

Quote:> I want to create a reporting DB from a
> transaction DB.  I want to add objects
> views, indexes, etc to the reporting DB
> for performance enhancements during
> queries. This is where all non-real time
> reporting requests would be pointed.
> Weekly, Monthly, Quarterly, Yearly reports
> run only against the reporting DB.

> Let's say transaction DB is 2 GB.

> I want no more than 1 days latiency(sp?) in
> the data from trans DB to reporting DB.

> I looked into Warm Standby - applying tlog
> as necessary - problem; DB is read only I
> could not add objects.

> Any ideas, experiences?
> Thanks,
> Rob


 
 
 

Reporting DB as copy of Transaction DB

Post by Keith Kratochvi » Fri, 03 Aug 2001 01:13:37


Why not just create the views and indexes to your production machine and
then use log shipping to keep the reporting database current?  By the way,
views might not improve performance.

If the above suggestion does not work for you, and you only need the data to
be current within one day, why not BACKUP the production database x times
per day and RESTORE it to your reporting server?  You could have the
additional steps that add any views, stored procedures, tables, and whatever
else you need to do within the step that restores the database.

--
Keith, SQL Server MVP
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> I want to create a reporting DB from a
> transaction DB.  I want to add objects
> views, indexes, etc to the reporting DB
> for performance enhancements during
> queries. This is where all non-real time
> reporting requests would be pointed.
> Weekly, Monthly, Quarterly, Yearly reports
> run only against the reporting DB.

> Let's say transaction DB is 2 GB.

> I want no more than 1 days latiency(sp?) in
> the data from trans DB to reporting DB.

> I looked into Warm Standby - applying tlog
> as necessary - problem; DB is read only I
> could not add objects.

> Any ideas, experiences?
> Thanks,
> Rob

 
 
 

1. Block copy via DB-Lib in a transaction with delete



I cannot authoritatively explain the behavior you see without looking into
this further.  However, I would point out that, unless you have some
devious way of extracting the DBPROCESS for a given ODBC connection, your
begin tran/delete/commit are running over a separate connection from your
bcp_init/bcp_columns/bcp_etc.  Transactions are scoped to a connection, not
a client process.  I suspect that your connection which does the begin
tran/delete is locking some resource that your bcp connection wishes to
obtain.  It thus appears the client is hanging.  I recommend that when you
see this, run sp_who against the server to see whether your bcp process is
blocked.

--
Scott Nichol
e-mail: Remove nospam. from reply address.

2. ODBC: Refuse by Server.

3. HELP - using DTS to copy data from sql db to sql db

4. "ConnectionCheckForData" error

5. sql2k -copy db wiz corrupt db?

6. Problem Installing Oracle 7.3 on NT 4.0

7. Need FASTER Copy 30GB to 125GB DB to another DB

8. Signitures

9. Error in DTS package copy 7.0 db to 2000 db

10. Copying DB to new DB

11. copy db and db users

12. Copying DB over existing DB connected to DataEnvironment

13. How to copying from one DB table to another DB table