Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
abacusdotcom
Posting Yak Master
133 Posts |
 Posted - 2013-04-24 : 07:23:18
|
| 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 processA - 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
52326 Posts |
Posted - 2013-04-24 : 08:39:04
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
abacusdotcom
Posting Yak Master
133 Posts |
Posted - 2013-04-24 : 09:02:51
|
| 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. |
|
|
|
abacusdotcom
Posting Yak Master
133 Posts |
Posted - 2013-04-24 : 09:30:31
|
| 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-24 : 12:32:35
|
| nope...not report snapshot. I was telling on database snapshot.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Data Refreshing to Reporting DB