SQL Server Performance Forum – Threads Archive

Replicating the Dataware house databases

I am workign on project which I kept on hold for few week on hold.
I have database 200 GB . We get weekly feeds an we load that data in production server .
at same time clients will be running reports also on same database. They are pretty dynamic report and run for 13 to 14 hours that is expected .. my question is I have setup the transactional replication . It is working fine when smaller reports are runnig.. When long running reports are runing they are having open transactions. my log reader is stuck when it hits OPEN TRAN. so, log is getting full (300GB log files)
So, any one have any idea how can I replicate this database and keep in sync.
I thought of loading on both servers using SSIS . but the problem is we are using Identity columns if something goes wrong identity values may be off.. which will screw up reports..
Out main aim is submit reports on both servers results set (50 K rows write back to primary server using linked tables) . So, Client only know he submitted the reports on main server he does not worry where it gets processed…
Please suggest me how to keep these 2 database servers are in SYNC

What do you want to use the replicated copy for? I’d suggest log shipping or preferably database mirroring as better alternatives to replication in this instance.

run reports on replicated copy also so, that we can finish more batch reports.. each runs 10 to 13 hrs. so, we can spread load on 2 machines

there is no valid reason for a report to take 13-14 hours on a 200GB db and a modern correctly configured server system
even 1 hr indicates serious issues i suggest examining the long running reports
this is the source of your problems

Joe, I understand the concern But, We have gone thru lot of iteration where we can reduce the time & unnecessary code. these reports are very dynmic and at run time they gets data from fact tables and put it into their on PERM temp table space once report is done they will get deleted. Each report creates around 30 to 40 temp tables 4 or 5 tables will have at least 45 M rows.. At this moment I can’t change the whole architecute of the product however I am trying to find best possible soln without touching much functionality. You know funny thing clients are happy with 10 to 13 reports <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> As per DB perspective I have even partitined large FACT tables .( which are holding 500 M records)..

what it comes down to is: there is no method of replication that can happen if you have a lock on the table (for your report with the OPEN TRAN) so you need to work out a scheduling system on when reports run (with OPEN TRAN) and when replication happens so each can accomplish its mission but you still should do a combination of cleaning up inefficiencies in your report
and configuring your hardware to run it faster let me pose this to you:
even supposing there are no inefficencies,
when running the 13hr report,
your cpu is not pegged for the duration? then you hw bottleneck is somewhere else,
find it, fix it, if it is the right thing to do

Can someone tell me in dataware house application which reports are completing in 1hr time.. ? Just intersting to know.. Joe, You mean we do not have soln for these kind of databases? <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

for a 200G dw, i would really like to get my reports in 10-15min
but it really depends on what your reports are
and the degree to which you are persisting computed values also ask: who is waiting for these reports and is their time valuable?
if no, tell them to suck it up
if yes, ask for money to fix this problem i ask this question, to what degree are intermediate values for your reports computed every time someone runs a report, versus persisting the values somewhere anyways, for what you have described, and my guess that your cpu is not pegged (by far)
there are 3 avenues of attack:
1. improve the SQL efficiency of your reports (+hire grossly expensive consultant)
2. schedule reports & replication to avoid contention (hire somelse)
3. improve your hardware (+hire grossly expensive consulant to make its right) I do not what your hardware is
I have not seen a perfmon trace of your system while the report is running
I have not seen the execution plans for your query but i will "see" across time, space and internent to say:
that it is possible to make substantial improvements,
the question is which avenue has lowest hanging fruit
which has the most fruit

Hi, I also interested to know if somebody has done Trans. Replication in DW environment?
Regards, Chetan Best Regards, Chetan
"Calm seas can never make skillful sailors".

]]>