SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Data Refreshing to Reporting DB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

abacusdotcom
Posting Yak Master

Nigeria
133 Posts

Posted - 04/24/2013 :  07:23:18  Show Profile  Visit abacusdotcom's Homepage  Send abacusdotcom a Yahoo! Message  Reply with Quote
Dear All,

I have two servers, 1 for data processing (PRDB) and other for reporting database(RSDB).
After every processing on PRDB, I manually refresh the data to RSDB. Manually in this sense:
Using SSIS I Automate this process

A - For small tables

- Delete rows in TableA in RSDB
- select delta rows in TableA in PRDB
- then Insert in TableA in RSDB

B - For large tables

- I used Transfer SQL Server Objects Task Editor - option of DropObjectFirst in destination (RSDB)

I noticed lately the process takes like 3 or more hours to finish, and on those occasions, users are unable to view reports. The system is 96gig RAM, system resources is ok.

Kindly advice and help indicate likely performance flaws one can envisage during daily processes.

Thanks All..

Worried Abacus.

I sign for fame not for shame but all the same, I sign my name.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/24/2013 :  08:39:04  Show Profile  Reply with Quote
you could take a snapshot of database first and make the reports point to it. This will make reports available whilst you do data movement on the background. Once processing is done, remap datasources of report to main copy and drop the snapshot

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 04/24/2013 08:39:25
Go to Top of Page

abacusdotcom
Posting Yak Master

Nigeria
133 Posts

Posted - 04/24/2013 :  09:02:51  Show Profile  Visit abacusdotcom's Homepage  Send abacusdotcom a Yahoo! Message  Reply with Quote
Many thanks visakh16 my good friend.

Will do a research into that. Please kindly make my research exercise lighter.. need understanding on the activity of remapping the datasource. Kindly show example on how to. Thanks alot.

I sign for fame not for shame but all the same, I sign my name.
Go to Top of Page

abacusdotcom
Posting Yak Master

Nigeria
133 Posts

Posted - 04/24/2013 :  09:30:31  Show Profile  Visit abacusdotcom's Homepage  Send abacusdotcom a Yahoo! Message  Reply with Quote
visakh16, you mean I should activate Report snapshot in the report manager?

I sign for fame not for shame but all the same, I sign my name.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/24/2013 :  12:32:35  Show Profile  Reply with Quote
nope...not report snapshot. I was telling on database snapshot.



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000