Data Refresh to Reporting DB

0
votes

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.

1
reporting-servicesssrs-2008load-balancingdatabase-administration

1 Answers

0
votes

Since you are already using SSIS, i suggest that you explorer its Incremental Load capabilities.

An incremental load loads only the differences since the previous load. Differences include:

  • New rows
  • Updated rows
  • Deleted rows

By its very nature, an incremental load is re-executable, which means you can execute the loader over and over again without harm. More than that, re-executable means the loader is designed to be executed multiple times without causing unnecessary or repetitive work on the server.

For a through article to get you started, see

  1. http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/75331/ AND
  2. http://www.sqlservercentral.com/articles/Stairway+Series/76390/